pymysql
介绍
pymysql是python操作MySQL的第三方数据库
因为是第三方,所以在使用之前需要进行安装
pip install pymysql
使用
执行sql语句
import pymysql # 进行连接 conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='py_database') # 创建游标 cursor = conn.cursor() # 执行sql语句 cursor.execute('create table tb2(id int auto_increment key,name varchar(20) not null,age int default 18)') # 确认执行 conn.commit() # 关闭 cursor.close() conn.close()
插入数据
import pymysql # 进行连接 conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='py_database') # 创建游标 cursor = conn.cursor() sql = 'insert into tb2(name,age) VALUES (%s,%s)' # 单条插入 cursor.execute(sql,('kidd',15)) # 多条插入 more = [('one',1),('two',2),('three',3)] cursor.executemany(sql,more) # 确认执行 conn.commit() # 关闭 cursor.close() conn.close()
删除数据
import pymysql # 进行连接 conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='py_database') # 创建游标 cursor = conn.cursor() # 执行语句 sql = 'delete from tb2 where name = %s' cursor.execute(sql,'kidd') # 确认执行 conn.commit() # 关闭 cursor.close() conn.close()
更新数据
import pymysql # 进行连接 conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='py_database') # 创建游标 cursor = conn.cursor() # 执行语句 sql = 'update tb2 set age=%s where name=%s' cursor.execute(sql,(111,'one')) # 确认执行 conn.commit() # 关闭 cursor.close() conn.close()
查看数据
import pymysql # 进行连接 conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='py_database') # 创建游标 cursor = conn.cursor() # 执行语句 sql = 'select * from tb2 limit 3' cursor.execute(sql) # 查看内容 result = cursor.fetchall() # fetchone,fetchmany print(result) # 关闭 cursor.close() conn.close()
import pymysql # 进行连接 conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='py_database') # 设置游标 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 执行语句 sql = 'select * from tb2 limit 3' cursor.execute(sql) # 查看内容 result = cursor.fetchone() print(result) # 关闭 cursor.close() conn.close()
创建视图,view
import pymysql # 连接Mysql conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='school') # 创建游标 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 创建视图,并查看 sql = 'create view v1 as select sname,caption from student left join class on class_id = cid' cursor.execute(sql) sql = 'select * from v1' cursor.execute(sql) #结果 result = cursor.fetchall() # 打印 print(result) # 关闭 cursor.close() conn.close()
获取存储过程
import pymysql # 连接Mysql conn = pymysql.connect(user='root',passwd='1314',host='localhost',db='school') # 创建游标 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 调用存储过程 cursor.callproc('f1',args=(1,2,'kidd')) # 取值 cursor.execute('select @f_1_0,@f_1_1,@f_1_2') # 结果 result = cursor.fetchall() # 打印 print(result) # 关闭 cursor.close() conn.close()
注:在fetch数据时按照顺序进行,可以使用cursor.scroll(num,mode)来移动游标位置,如:
- cursor.scroll(1,mode='relative') # 相对当前位置移动
- cursor.scroll(2,mode='absolute') # 相对绝对位置移动
获取最新自增ID
new_id = cursor.lastrowid