pymysql

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