python操作mysql数据库
一、数据库基本操作
1.想允许在数据库写中文，可在创建数据库时用下面命令
createdatabasezclcharsetutf8;
2.查看students表结构
descstudents;
3.查看创建students表结构的语句
showcreatetablestudents;
4.删除数据库
dropdatabasezcl;
5.创建一个新的字段
altertablestudentsaddcolumnnalchar(64);
PS:本人是很讨厌上面这种“简单解释+代码”的博客。其实我当时在mysql终端写了很多的实例，不过因为当时电脑运行一个看视频的软件，导致我无法Ctrl+C/V。现在懒了哈哈~~
二、python连接数据库
python3不再支持mysqldb。其替代模块是PyMySQL。本文的例子是在python3.4环境。
1.安装pymysql模块
pip3installpymysql
2.连接数据库，插入数据实例
importpymysql
#生成实例，连接数据库zcl
conn=pymysql.connect(host='127.0.0.1',user='root',passwd='root',db='zcl')
#生成游标，当前实例所处状态
cur=conn.cursor()
#插入数据
reCount=cur.execute('insertintostudents(name,sex,age,tel,nal)values(%s,%s,%s,%s,%s)',('Jack','man',25,1351234,"CN"))
reCount=cur.execute('insertintostudents(name,sex,age,tel,nal)values(%s,%s,%s,%s,%s)',('Mary','female',18,1341234,"USA"))
conn.commit()#实例提交命令
cur.close()
conn.close()
print(reCount)
查看结果:
mysql>select*fromstudents; +----+------+-----+-----+-------------+------+ |id|name|sex|age|tel|nal| +----+------+-----+-----+-------------+------+ |1|zcl|man|22|15622341234|NULL| |2|alex|man|30|15622341235|NULL| +----+------+-----+-----+-------------+------+ rowsinset
3.获取数据
importpymysql
conn=pymysql.connect(host='127.0.0.1',user='root',passwd='root',db='zcl')
cur=conn.cursor()
reCount=cur.execute('select*fromstudents')
res=cur.fetchone()#获取一条数据
res2=cur.fetchmany(3)#获取3条数据
res3=cur.fetchall()#获取所有(元组格式)
print(res)
print(res2)
print(res3)
conn.commit()
cur.close()
conn.close()
输出:
(1,'zcl','man',22,'15622341234',None) ((2,'alex','man',30,'15622341235',None),(5,'Jack','man',25,'1351234','CN'),(6,'Mary','female',18,'1341234','USA')) ()
三、事务回滚
事务回滚是在数据写到数据库前执行的，因此事务回滚conn.rollback()要在实例提交命令conn.commit()之前。只要数据未提交就可以回滚，但回滚后ID却是自增的。请看下面的例子:
插入3条数据(注意事务回滚):
importpymysql
#连接数据库zcl
conn=pymysql.connect(host='127.0.0.1',user='root',passwd='root',db='zcl')
#生成游标，当前实例所处状态
cur=conn.cursor()
#插入数据
reCount=cur.execute('insertintostudents(name,sex,age,tel,nal)values(%s,%s,%s,%s,%s)',('Jack','man',25,1351234,"CN"))
reCount=cur.execute('insertintostudents(name,sex,age,tel,nal)values(%s,%s,%s,%s,%s)',('Jack2','man',25,1351234,"CN"))
reCount=cur.execute('insertintostudents(name,sex,age,tel,nal)values(%s,%s,%s,%s,%s)',('Mary','female',18,1341234,"USA"))
conn.rollback()#事务回滚
conn.commit()#实例提交命令
cur.close()
conn.close()
print(reCount)
未执行命令前与执行命令后(包含回滚操作)(注意ID号):未执行上面代码与执行上面代码的结果是一样的!!因为事务已经回滚，故students表不会增加数据!
mysql>select*fromstudents; +----+------+--------+-----+-------------+------+ |id|name|sex|age|tel|nal| +----+------+--------+-----+-------------+------+ |1|zcl|man|22|15622341234|NULL| |2|alex|man|30|15622341235|NULL| |5|Jack|man|25|1351234|CN| |6|Mary|female|18|1341234|USA| +----+------+--------+-----+-------------+------+ rowsinset
执行命令后(不包含回滚操作):只需将上面第11行代码注释。
mysql>select*fromstudents; +----+-------+--------+-----+-------------+------+ |id|name|sex|age|tel|nal| +----+-------+--------+-----+-------------+------+ |1|zcl|man|22|15622341234|NULL| |2|alex|man|30|15622341235|NULL| |5|Jack|man|25|1351234|CN| |6|Mary|female|18|1341234|USA| |10|Jack|man|25|1351234|CN| |11|Jack2|man|25|1351234|CN| |12|Mary|female|18|1341234|USA| +----+-------+--------+-----+-------------+------+ rowsinset
总结：虽然事务回滚了，但ID还是自增了，不会因回滚而取消，但这不影响数据的一致性（底层的原理我不清楚~）
四、批量插入数据
importpymysql
#连接数据库zcl
conn=pymysql.connect(host='127.0.0.1',user='root',passwd='root',db='zcl')
#生成游标，当前实例所处状态
cur=conn.cursor()
li=[
("cjy","man",18,1562234,"USA"),
("cjy2","man",18,1562235,"USA"),
("cjy3","man",18,1562235,"USA"),
("cjy4","man",18,1562235,"USA"),
("cjy5","man",18,1562235,"USA"),
]
#插入数据
reCount=cur.executemany('insertintostudents(name,sex,age,tel,nal)values(%s,%s,%s,%s,%s)',li)
#conn.rollback()#事务回滚
conn.commit()#实例提交命令
cur.close()
conn.close()
print(reCount)
pycharm下输出:5
mysql终端显示:
mysql>select*fromstudents;#插入数据前 +----+-------+--------+-----+-------------+------+ |id|name|sex|age|tel|nal| +----+-------+--------+-----+-------------+------+ |1|zcl|man|22|15622341234|NULL| |2|alex|man|30|15622341235|NULL| |5|Jack|man|25|1351234|CN| |6|Mary|female|18|1341234|USA| |10|Jack|man|25|1351234|CN| |11|Jack2|man|25|1351234|CN| |12|Mary|female|18|1341234|USA| +----+-------+--------+-----+-------------+------+ rowsinset mysql> mysql>select*fromstudents;#插入数据后 +----+-------+--------+-----+-------------+------+ |id|name|sex|age|tel|nal| +----+-------+--------+-----+-------------+------+ |1|zcl|man|22|15622341234|NULL| |2|alex|man|30|15622341235|NULL| |5|Jack|man|25|1351234|CN| |6|Mary|female|18|1341234|USA| |10|Jack|man|25|1351234|CN| |11|Jack2|man|25|1351234|CN| |12|Mary|female|18|1341234|USA| |13|cjy|man|18|1562234|USA| |14|cjy2|man|18|1562235|USA| |15|cjy3|man|18|1562235|USA| |16|cjy4|man|18|1562235|USA| |17|cjy5|man|18|1562235|USA| +----+-------+--------+-----+-------------+------+ rowsinset
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持毛票票！