Python基础之操作mysql数据库

一：安装驱动程序

Pythons要想操作Mysql，必须要有一个中间件，或者叫做驱动程序。驱动程序有很多，如：mysqldb, mysqlclient, pymysql等。这里选择使用pymysql

pip install pymysql

二：数据库操作

2.1 连接数据库

import pymysql
conn = pymysql.connect(host = '172.17.2.36', user = 'root', password = 'root', database = 'spider', port = 3306)
cursor = conn.cursor()

cursor.execute('select 1')
result = cursor.fetchone()
print(result)

conn.close()

2.2 建库

conn = pymysql.connect(
    host = '172.17.2.36',
    user = 'root',
    password = 'root',
    charset = 'utf8'
)
cursor = conn.cursor()
# 建库
cursor.execute('create database pytest charset utf8;')
# 使用库
cursor.execute('use pytest;')
conn.close()

2.2 建表

conn = pymysql.connect(host = '172.17.2.36',user = 'root',password = 'root', database = 'pytest', charset = 'utf8')
cursor = conn.cursor()
cursor.execute('drop table if exists user;')
cursor.execute("create table `user`(id int primary key auto_increment, username varchar(20), age int, password varchar(64));")
conn.close()

2.3 插入表数据

conn = pymysql.connect(host = '172.17.2.36',user = 'root',password = 'root', database = 'pytest', charset = 'utf8')
cursor = conn.cursor()

sql = '''insert into user values(null,'ginvip02', 20, '123456');'''
cursor.execute(sql)
conn.commit()

conn.close()

2.4 查询

查询单条数据：

cursor.execute('select * from user where id=1;')
result = cursor.fetchone()
print(result)
# (1, 'ginvip', 20, '123456')

查询多条数据：

cursor.execute('select * from user;')
while True:
    result = cursor.fetchone()
    if not result: break
    print(result)

也可以使用fetchall一次性把数据全部取出：

cursor.execute('select * from user;')
result = cursor.fetchall()
print(result)
# ((1, 'ginvip', 20, '123456'), (2, 'ginvip02', 20, '123456'))

或者使用fetchmany(num)指定取次数据的条数：

cursor.execute('select * from user;')
result = cursor.fetchmany(2)		# 取出2条数据
print(result)

2.5 删除

cursor.execute('delete from user where id=2;')
conn.commit()

delete和truncate区别如下：

1. 灵活性：delete可以条件删除数据，而truncate只能删除表的所有数据；

delete from table_test where ... 
truncate table table_test 

2. 效率：delete效率低于truncate，delete是一行一行地删除，truncate会重建表结构，
3. 事务：truncate是DDL语句，需要drop权限，因此会隐式提交，不能够rollback；delete是DML语句，可以使用rollback回滚。
4. 触发器：truncate 不能触发任何Delete触发器；而delete可以触发delete触发器。

2.6 更新

cursor.execute('update user set username="Bruce" where id=1;')
conn.commit()