Python中的第三方库pymysql提供了与数据库连接并且进行交互的功能

pymysql库的安装

1
pip install pymysql

pymysql库的使用

(1)导入pymysql库

1
import pymysql

(2)使用pymysql.connect()方法创建python客户端与数据库连接的connection对象

1
conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root')

connect方法中的参数：

1)host：字符串类型，表示mysql服务器地址

2)port：数字类型，表示mysql服务器端口号，默认为3306

3)user：字符串类型，表示mysql数据库用户名

4)passwd：字符串类型，表示mysql数据库密码

5)db：字符串类型，表示连接到的数据库名

6)charset：字符串类型，表示连接编码

(3)使用connection对象的cursor()方法创建于数据库交互的对象

1
cur = conn.cursor()

(4)使用cursor对象的execute()方法执行sql语句

1
2
sql = ''
row = cur.execute() #返回查询结果的行数

(5)使用cursor对象的fetchone,fetchmany,fetchall方法获得查询结果

1
result = cur.fetchone() #取得结果集的下一行

例子：

1
2
3
4
5
6
7
8
9
10
11
>>> import pymysql
>>> conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root')
>>> cur = conn.cursor()
>>> sql = 'show databases'
>>> row = cur.execute(sql)
>>> result = cur.fetchone()
>>> print(result)
('information_schema',)
>>> result = cur.fetchone()
>>> print(result)
('mysql',)
1
result = cur.fetchmamy(size) #取得结果集的下几行

例子：

1
2
3
4
5
6
7
8
9
10
11
>>> import pymysql
>>> conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root')
>>> cur = conn.cursor()
>>> sql = 'show databases'
>>> row = cur.execute(sql)
>>> result = cur.fetchmany(2)
>>> print(result)
(('information_schema',), ('mysql',))
>>> result = cur.fetchmany(2)
>>> print(result)
(('performance_schema',), ('security',))
1
result = cur.fetchall() #取得结果集的所有行

例子：

1
2
3
4
5
6
7
8
>>> import pymysql
>>> conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root')
>>> cur = conn.cursor()
>>> sql = 'show databases'
>>> row = cur.execute(sql)
>>> result = cur.fetchall()
>>> print(result)
(('information_schema',), ('mysql',), ('performance_schema',), ('security',), ('test',))

(6)使用cursor对象的commit()方法将修改提交到数据库

1
cur.commit()

(7)关闭cursor对象和connection对象

1
2
cur.close()
conn.close()

使用pymysql库实现数据库的增删改查

(1)创建新的数据库pymysql，并且创建新的数据表demo，列名分别为id,username,password

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import pymysql

conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root')
cur = conn.cursor()

createdb_sql = 'create database pymysql'
cur.execute(createdb_sql)

querydb_sql = 'show databases'
cur.execute(querydb_sql)
result = cur.fetchall()
if ('pymysql',) in result:
    print('Database has created successfully!')

selectdb_sql = 'use pymysql'
cur.execute(selectdb_sql)
createtb_sql = 'create table demo(id int(3),username varchar(20),password varchar(20))'
cur.execute(createtb_sql)

querytb_sql = 'show tables'
cur.execute(querytb_sql)
result = cur.fetchall()
if ('demo',) in result:
    print('Table has created successfully!')

运行结果：

mysql命令行中查询：

(2)向demo表中添加修改数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
import pymysql

conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root')
cur = conn.cursor()

selectdb_sql = 'use pymysql'
cur.execute(selectdb_sql)

data = [[1,'admin1','admin1'],[2,'admin2','admin2'],[3,'admin3','admin3']]
for i in data:
    ID = i[0]
    username = i[1]
    password = i[2]
    insert_sql = "insert into demo values(%d,'%s','%s')"%(ID,username,password)
    cur.execute(insert_sql)

query_sql = 'select * from demo'
cur.execute(query_sql)
result = cur.fetchall()
print(result)

update_sql = "update demo set username='somnus',password='somnus' where id=3"
cur.execute(update_sql)
cur.execute(query_sql)
result = cur.fetchall()
print(result)

cur.close()
conn.close()

运行结果：

(3)删除列，表，库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import pymysql

conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='pymysql')
cur = conn.cursor()

deletecu_sql = "delete from demo where id=1"
cur.execute(deletecu_sql)
query_sql = 'select * from demo where id=1'
if cur.execute(query_sql) == 0:
    print('column 1 has deleted')

deletetb_sql = "drop table demo"
cur.execute(deletetb_sql)
query_sql = 'show tables'
if cur.execute(query_sql) == 0:
    print('table has deleted')

deletedb_sql = "drop database pymysql"
cur.execute(deletedb_sql)
query_sql = 'show databases'
cur.execute(query_sql)
result = cur.fetchall()
if ('pymysql',) not in result:
    print('database has deleted')