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里，调用函数即可，自己备份一下用法。