python_fullstack数据库(七)-Python连接MySQL数据库之pymysql模块使用

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")