python用pymysql模块操作数据库MySQL,实现查增删改
这里主要写四个函数，查增删改
下面是目标数据库的样式，数据库：testpy，数据表：movie1，（拼错了不改了,猪啊），有四个字段，id是主键。
查，下面的例子加个函数名就可以运行了
1 #查select 2 def select(): 3 #创建connection连接 4 conn = pymysql.connect( 5 host = 'localhost', 6 port = 3306, 7 user = 'root', 8 password = 'mysql_123456', 9 database = 'testpy', 10 charset = 'utf8' 11 ) 12 #获得cursor对象 13 cursor = conn.cursor() 14 #SQL语句 15 sql = "select daoyan,dizhi,mingzi from movie1 where daoyan='NA';" 16 #执行语句 17 cursor.execute(sql) 18 #获取查询数据 19 #ret = cursor.fetchone() 20 #ret = cursor.fetchmany(3) 21 ret = cursor.fetchall() 22 #关闭对象，连接 23 cursor.close() 24 conn.close() 25 #打印结果 26 for i in ret: 27 print(i)
增
1 #增：insert 2 def insert(): 3 #创建connection连接 4 conn = pymysql.connect( 5 host = 'localhost', 6 port = 3306, 7 user = 'root', 8 password = 'mysql_123456', 9 database = 'testpy', 10 charset = 'utf8' 11 ) 12 #获得cursor对象 13 cursor = conn.cursor() 14 #SQL语句 15 sql = "insert into movie1 values ('0','luke','http://www.luke.com','你妹');" 16 #执行语句 17 cursor.execute(sql) 18 #提交事务 19 conn.commit() 20 last_id = cursor.lastrowid 21 #返回插入的id号码 22 print(last_id) 23 #关闭对象，连接 24 cursor.close() 25 conn.close()
删
1 #删：delete 2 def delete(): 3 #创建connection连接 4 conn = pymysql.connect( 5 host = 'localhost', 6 port = 3306, 7 user = 'root', 8 password = 'mysql_123456', 9 database = 'testpy', 10 charset = 'utf8' 11 ) 12 #获得cursor对象 13 cursor = conn.cursor() 14 #SQL语句 15 sql = "delete from movie1 where id =149 ;" 16 #执行语句 17 cursor.execute(sql) 18 #提交事务 19 conn.commit() 20 #关闭对象，连接 21 cursor.close() 22 conn.close()
改
1 #改：update 2 def update(): 3 #创建connection连接 4 conn = pymysql.connect( 5 host = 'localhost', 6 port = 3306, 7 user = 'root', 8 password = 'mysql_123456', 9 database = 'testpy', 10 charset = 'utf8' 11 ) 12 #获得cursor对象 13 cursor = conn.cursor() 14 #SQL语句 15 sql = "update movie1 set daoyan='luke' where id=2;" 16 #执行语句 17 cursor.execute(sql) 18 #提交事务 19 conn.commit() 20 #关闭对象，连接 21 cursor.close() 22 conn.close()
上面的四个函数写在一个py里，调用函数即可，自己备份一下用法。
