python 连接数据库

一.连接MYSQL

1. 下载PyMySql模块

2.在MYSQL中创建数据库并连接

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123456',database='ikun',charset='utf8',port=3306)

3.创建表

 

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123456',database='ikun',charset='utf8',port=3306)
c=conn.cursor()
sql='''
create table kun
( id int PRIMARY  KEY  NOT NULL ,
  name text NOT NULL ,
  age int NOT NULL ,
  address char(50)
 );

c.execute(sql)
conn.commit()
conn.close()
print("成功建表")

4.插入、更新和删除数据

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123456',database='ikun',charset='utf8',port=3306)
c=conn.cursor()#获取游标
c=conn.cursor()
sql="insert into kun VALUES (1,'李四坤',18,'A1')"
c.execute(sql)
conn.commit()
conn.close()
print("插入数据成功")

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123456',database='ikun',charset='utf8',port=3306)
c=conn.cursor()#获取游标
sql="update kun set address='贵州大学' WHERE id=1"
c.execute(sql)
conn.commit()
conn.close()
print("更新数据成功")

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123456',database='ikun',charset='utf8',port=3306)
c=conn.cursor()#获取游标
sql="delete from kun WHERE id=2"
c.execute(sql)
conn.commit()
conn.close()
print("删除数据成功")

5.查询数据

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123456',database='ikun',charset='utf8',port=3306)
c=conn.cursor()#获取游标
sql="select * from kun"
c.execute(sql)
data=c.fetchall();
print(data)
conn.commit()
conn.close()

二.使用内置sqlite

1.打开或创建数据库文件

conn=sqlite3.connect("test.db")

print("打开数据库成功")

2.创建表格

c=conn.cursor()#获取游标
sql='''
create table company
( id int PRIMARY  KEY  NOT NULL ,
name text NOT NULL ,
age int NOT NULL ,
address char(50),
salary REAL
);
'''
c.execute(sql)#执行sql语句
conn.commit()#提交数据库操作
conn.close()#关闭数据库连接
print("成功建表")

3.插入数据库

c=conn.cursor()#获取游标
sql="insert into company(id,name,age,address,salary) VALUES (2,'李四',20,'贵州',8000)"
c.execute(sql)#执行sql语句
conn.commit()#提交数据库操作
conn.close()#关闭数据库连接
print("插入数据成功")

4.查询数据

c=conn.cursor()#获取游标
sql="select * from company"
reader=c.execute(sql)
for row in reader:
    print(row[0],row[1],row[2])
conn.close()#关闭数据库连接