Python连接MySQL数据库之pymysql模块使用
一、Python3连接MySQL数据库
1、Python连接MySQL数据库
Python2连接MySQL数据库使用mysqldb模块
Python3连接MySQL数据库使用pymysql模块
2、pymysql模块的安装
使用pip安装:pip install pymysql
使用pycharm安装:File -> Settings -> Project Interpreter -> 点击右上绿色+号 -> 查找模块,点击Install Package安装
3、连接数据库
①前提准备
- 有一个可供连接的MySQL数据库并且已经启动服务
- 拥有连接数据库的用户名、密码和相应的database操作权限
②基本使用
# 导入模块
import pymysql
#创建连接对象
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
password = "",
database = "testdb",
charset = "utf8"
)
# 定义光标对象
cursor = conn.cursor()
# 得到一个可以执行SQL语句并且将结果作为字典返回的游标
# cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 定义要执行的SQL语句
sql = '''
CREATE TABLE userinfo (
id INT auto_increment PRIMARY KEY ,
name CHAR(10) NOT NULL UNIQUE,
age TINYINT NOT NULL
)ENGINE=innodb DEFAULT CHARSET=utf8;
'''
# 执行sql语句
res = cursor.execute(sql)
# 打印查询结果
print(res)
# 关闭光标和连接
cursor.close()
conn.close()
4、数据库基本CURD操作
①增
- 正常增加
# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
password = "",
database = "testdb",
charset = "utf8"
)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
sql = "INSERT INTO user(name, age) VALUES (%s, %s);"
username = "Yang"
age = 18
# 执行SQL语句
cursor.execute(sql, [username, age])
# 提交事务
conn.commit()
cursor.close()
conn.close()
- 插入数据失败回滚操作
# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
password = "",
database = "testdb",
charset = "utf8"
)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);"
username = "Yang"
age = 18
try:
# 执行SQL语句
cursor.execute(sql, [username, age])
# 提交事务
conn.commit()
except Exception as e:
# 有异常,回滚事务
conn.rollback()
cursor.close()
conn.close()
- 获取插入数据的ID(关联操作时会用到)
# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
password = "",
database = "testdb",
charset = "utf8"
)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);"
username = "Yang"
age = 18
try:
# 执行SQL语句
cursor.execute(sql, [username, age])
# 提交事务
conn.commit()
# 提交之后,获取刚插入的数据的ID
last_id = cursor.lastrowid
except Exception as e:
# 有异常,回滚事务
conn.rollback()
cursor.close()
conn.close()
- 批量执行
# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
password = "",
database = "testdb",
charset = "utf8"
)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);"
data = [("Person_1", 18), ("Person_2", 20), ("Person_3", 21)]
try:
# 批量执行多条插入SQL语句
cursor.executemany(sql, data)
# 提交事务
conn.commit()
except Exception as e:
# 有异常,回滚事务
conn.rollback()
cursor.close()
conn.close()
②删
# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
password = "",
database = "testdb",
charset = "utf8"
)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
sql = "DELETE FROM USER1 WHERE id=%s;"
try:
cursor.execute(sql, [4])
# 提交事务
conn.commit()
except Exception as e:
# 有异常,回滚事务
conn.rollback()
cursor.close()
conn.close()
③改
# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
password = "",
database = "testdb",
charset = "utf8"
)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
# 修改数据的SQL语句
sql = "UPDATE USER1 SET age=%s WHERE name=%s;"
username = "Yang"
age = 18
try:
# 执行SQL语句
cursor.execute(sql, [age, username])
# 提交事务
conn.commit()
except Exception as e:
# 有异常,回滚事务
conn.rollback()
cursor.close()
conn.close()
④查
- 查询单条数据
# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
password = "",
database = "testdb",
charset = "utf8"
)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
# 查询数据的SQL语句
sql = "SELECT id,name,age from USER1 WHERE id=1;"
# 执行SQL语句
cursor.execute(sql)
# 获取单条查询数据
res = cursor.fetchone()
cursor.close()
conn.close()
# 打印下查询结果
print(res)
- 查询多条数据
# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
password = "",
database = "testdb",
charset = "utf8"
)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
# 查询数据的SQL语句
sql = "SELECT id,name,age from USER1;"
# 执行SQL语句
cursor.execute(sql)
# 获取多条查询数据
res = cursor.fetchall()
cursor.close()
conn.close()
# 打印下查询结果
print(res)
⑤进阶
# 可以获取指定数量的数据
cursor.fetchmany(3)
# 光标按绝对位置移动1
cursor.scroll(1, mode="absolute")
# 光标按照相对位置(当前位置)移动1
cursor.scroll(1, mode="relative")