Python 连接到 MySQL 数据库有几种不同的连接方法,而且不是所有的方法都能与不同的操作系统很好地配合.
MySQL connector/Python模块是Oracle支持的官方驱动,用于通过Python连接MySQL。该连接器完全是Python语言,而mysqlclient是用C语言编写的。它也是独立的,意味着它不需要MySQL客户端库或标准库以外的任何Python模块。
注意,MySQL Connector/Python不支持旧的MySQL服务器认证方法,这意味着4.1之前的MySQL版本不能工作。
本文实例的运行环境:
Windows 10
MySQL 8.0
一、创建数据库连接
1、首先安装mysql-connector-python模块。
python -m pip install mysql-connector
![](https://img-blog.csdnimg.cn/img_convert/6c6ab074b3db091fe2e83748db9c008f.png)
2、连接数据库:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root" # 数据库密码
)
print(mydb)
运行结果
<mysql.connector.connection.MySQLConnection object at 0x000002B399503AC0>
二、创建数据库
1、创建数据库使用 "CREATE DATABASE" 语句,以下创建一个名为 python_demo 的数据库:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root" # 数据库密码
)
print(mydb)
myCursor = mydb.cursor()
myCursor.execute("CREATE DATABASE python_demo")
![](https://img-blog.csdnimg.cn/img_convert/c1d6b4d6d44d88894a1de142ad2aa0d9.png)
2、使用 "SHOW DATABASES" 语句来查看数据库是否存在:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root" # 数据库密码
)
print(mydb)
myCursor = mydb.cursor()
# myCursor.execute("CREATE DATABASE python_demo")
myCursor.execute("SHOW DATABASES")
for x in myCursor:
print(x)
![](https://img-blog.csdnimg.cn/img_convert/b4f71cab1b34816ea632010ec400a57e.png)
我们可以直接连接数据库,如果数据库不存在,会输出错误信息:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
print(mydb)
三、创建数据表
创建数据表使用 "CREATE TABLE" 语句,创建数据表前,需要确保数据库已存在,以下创建一个名为 user 自增主键的数据表:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
# print(mydb)
myCursor = mydb.cursor()
myCursor.execute("CREATE TABLE user (id int(11) primary key not null auto_increment,name VARCHAR(64), mobile VARCHAR(32), address VARCHAR(255))")
![](https://img-blog.csdnimg.cn/img_convert/d8570895da3de11a51b09f704bf64e6b.png)
如果我们的表已经创建,我们需要使用 ALTER TABLE 来给表添加主键:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
# print(mydb)
myCursor = mydb.cursor()
myCursor.execute("ALTER TABLE user ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
四、插入数据
插入数据使用 "INSERT INTO" 语句:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
# print(mydb)
myCursor = mydb.cursor()
sql = "INSERT INTO user (name, mobile,address) VALUES (%s, %s, %s)"
val = ("Lily", "13866669999", "America")
myCursor.execute(sql, val)
mydb.commit() # 提交
print(myCursor.rowcount, "插入成功。")
![](https://img-blog.csdnimg.cn/img_convert/4f6dcc11cc898bbd5da6ec4bffc590cd.png)
批量插入
批量插入使用 executemany() 方法,该方法的第二个参数是一个元组列表,包含了我们要插入的数据:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
# print(mydb)
myCursor = mydb.cursor()
sql = "INSERT INTO user (name, mobile,address) VALUES (%s, %s, %s)"
# val = ("Lily", "13866669999", "America")
val = [
("LiLei", "13866668888", "China"),
("Jim", "13855558888", "America"),
("HanMeimei", "13811113333", "China")
]
# myCursor.execute(sql, val)
myCursor.executemany(sql, val)
mydb.commit() # 提交
print(myCursor.rowcount, "插入成功。")
3 插入成功。
![](https://img-blog.csdnimg.cn/img_convert/842b13c7858d19404f326caf1b7c65c2.png)
五、查询数据
1、查询数据使用 SELECT 语句:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
myCursor = mydb.cursor()
myCursor.execute("SELECT * FROM user")
result = myCursor.fetchall() # fetchall() 获取所有记录
for x in result:
print(x)
![](https://img-blog.csdnimg.cn/img_convert/ce78cd5750fd1ba6bcf0eb2b266899ef.png)
读取指定的字段数据:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
myCursor = mydb.cursor()
# myCursor.execute("SELECT * FROM user")
myCursor.execute("SELECT name, mobile, address FROM user")
result = myCursor.fetchall() # fetchall() 获取所有记录
for x in result:
print(x)
![](https://img-blog.csdnimg.cn/img_convert/9cae05fb7bf1dc6d75461f00f49065a4.png)
读取一条数据,可以使用 fetchone() 方法:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
myCursor = mydb.cursor()
# myCursor.execute("SELECT * FROM user")
myCursor.execute("SELECT name, mobile, address FROM user")
# result = myCursor.fetchall() # fetchall() 获取所有记录
result = myCursor.fetchone() # fetchall() 获取一条记录
print(result)
('Lily', '13866669999', 'America')
2、where 条件语句
如果我们要读取指定条件的数据,可以使用 where 语句:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
myCursor = mydb.cursor()
# myCursor.execute("SELECT * FROM user")
myCursor.execute("SELECT * FROM user where address ='America'")
result = myCursor.fetchall() # fetchall() 获取所有记录
# result = myCursor.fetchone() # fetchall() 获取一条记录
# print(result)
for x in result:
print(x)
(1, 'Lily', '13866669999', 'America')
(3, 'Jim', '13855558888', 'America')
为了防止数据库查询发生 SQL 注入的攻击,我们可以使用 %s 占位符来转义查询的条件:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
myCursor = mydb.cursor()
# myCursor.execute("SELECT * FROM user where address ='America'")
sql = "SELECT * FROM user where address = %s"
param = ("China",)
myCursor.execute(sql, param)
result = myCursor.fetchall() # fetchall() 获取所有记录
for x in result:
print(x)
(2, 'LiLei', '13866668888', 'China')
(4, 'HanMeimei', '13811113333', 'China')
3、排序
查询结果排序可以使用 ORDER BY 语句,默认的排序方式为升序,关键字为 ASC,如果要设置降序排序,可以设置关键字 DESC。
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
myCursor = mydb.cursor()
# myCursor.execute("SELECT * FROM user ORDER BY name")
sql = "SELECT * FROM user ORDER BY name"
myCursor.execute(sql)
result = myCursor.fetchall() # fetchall() 获取所有记录
for x in result:
print(x)
(4, 'HanMeimei', '13811113333', 'China')
(3, 'Jim', '13855558888', 'America')
(2, 'LiLei', '13866668888', 'China')
(1, 'Lily', '13866669999', 'America')
降序排序实例:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
myCursor = mydb.cursor()
# myCursor.execute("SELECT * FROM user ORDER BY name")
sql = "SELECT * FROM user ORDER BY address DESC "
myCursor.execute(sql)
result = myCursor.fetchall() # fetchall() 获取所有记录
for x in result:
print(x)
(2, 'LiLei', '13866668888', 'China')
(4, 'HanMeimei', '13811113333', 'China')
(1, 'Lily', '13866669999', 'America')
(3, 'Jim', '13855558888', 'America')
4、Limit
如果我们要设置查询的数据量,可以通过 "LIMIT" 语句来指定
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
myCursor = mydb.cursor()
# myCursor.execute("SELECT * FROM user ORDER BY name")
sql = "SELECT * FROM user LIMIT 2 "
myCursor.execute(sql)
result = myCursor.fetchall() # fetchall() 获取所有记录
for x in result:
print(x)
(1, 'Lily', '13866669999', 'America')
(2, 'LiLei', '13866668888', 'China')
指定起始位置,使用的关键字是 OFFSET:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
myCursor = mydb.cursor()
# sql = "SELECT * FROM user LIMIT 2 "
sql = "SELECT * FROM user LIMIT 2 OFFSET 1"
myCursor.execute(sql)
result = myCursor.fetchall() # fetchall() 获取所有记录
for x in result:
print(x)
(2, 'LiLei', '13866668888', 'China')
(3, 'Jim', '13855558888', 'America')
六、删除记录
删除记录使用 "DELETE FROM" 语句:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
myCursor = mydb.cursor()
sql = "DELETE FROM user WHERE name = 'Jim'"
myCursor.execute(sql)
mydb.commit()
print(myCursor.rowcount, " 条记录删除")
1 条记录删除
![](https://img-blog.csdnimg.cn/img_convert/be11aeff0d1e27e930175ec44c225af4.png)
为了防止数据库查询发生 SQL 注入的攻击,我们可以使用 %s 占位符来转义删除语句的条件:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
myCursor = mydb.cursor()
sql = "DELETE FROM user WHERE name = %s"
param = ("HanMeimei",)
myCursor.execute(sql, param)
mydb.commit()
print(myCursor.rowcount, " 条记录删除")
1 条记录删除
七、更新表数据
数据表更新使用 "UPDATE" 语句:
![](https://img-blog.csdnimg.cn/img_convert/9399484a60a974e750f06d007fd874e1.png)
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
myCursor = mydb.cursor()
sql = "UPDATE user SET name = 'Lucy' WHERE name = 'Lily'"
myCursor.execute(sql)
mydb.commit()
print(myCursor.rowcount, " 条记录被修改")
1 条记录被修改
![](https://img-blog.csdnimg.cn/img_convert/77daa9312cf8329fffa4d814928751e1.png)
为了防止数据库查询发生 SQL 注入的攻击,我们可以使用 %s 占位符来转义更新语句的条件:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
myCursor = mydb.cursor()
sql = "UPDATE user SET name = %s WHERE name = %s"
param = ("HanMeimei", "LiLei")
myCursor.execute(sql, param)
mydb.commit()
print(myCursor.rowcount, " 条记录被修改")
1 条记录被修改
八、删除表
删除表使用 "DROP TABLE" 语句, IF EXISTS 关键字是用于判断表是否存在,只有在存在的情况才删除:
# coding=utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="python_demo" # 指定数据库
)
myCursor = mydb.cursor()
myCursor.execute("SHOW TABLES")
for x in myCursor:
print(x)
sql = "DROP TABLE IF EXISTS user_info" # 删除数据表 user_info
myCursor.execute(sql)
print(" 删除数据表 user_info")
myCursor.execute("SHOW TABLES")
for x in myCursor:
print(x)
('user',)
('user_info',)
删除数据表 user_info
('user',)