Python3 MySQL(mysql-connector)
用 mysql-connector-python 连接 MySQL：建连、建表、插入、查询与参数化。
安装 mysql-connector
Oracle's mysql-connector-python is pure Python with no build step. PyMySQL is an alternative with nearly identical API — swap the import name.
cmds = [
"pip install mysql-connector-python",
"pip install PyMySQL",
]
for c in cmds:
print(c)
print(len(cmds))pip install mysql-connector-python
pip install PyMySQL
2创建数据库连接
mysql.connector.connect(host, user, password, database) opens a connection and cursor() gives you a handle to run SQL. In production use a pool and set timeouts.
# import mysql.connector # 需要先 pip install mysql-connector-python
config = {
'host': 'localhost',
'user': 'root',
'password': 'your_password',
'database': 'testdb',
'connection_timeout': 5,
}
# 真实运行时：
# conn = mysql.connector.connect(**config)
# print(conn.is_connected())
print(sorted(config))
print(config['host'])['connection_timeout', 'database', 'host', 'password', 'user']
localhost创建数据库表
Run DDL through cursor.execute(). Table and column names cannot be parameters — they must come from a whitelist in code — while values always use %s placeholders.
DDL = """
CREATE TABLE IF NOT EXISTS sites (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64) NOT NULL,
url VARCHAR(255) NOT NULL,
UNIQUE KEY uk_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
print(DDL.count('CREATE'))
print('utf8mb4' in DDL)
print('PRIMARY KEY' in DDL)1
True
True插入数据
Writes need an explicit commit() — otherwise the change rolls back when the connection closes. Use executemany(sql, params) for bulk inserts; it is far faster than looping.
INSERT = "INSERT INTO sites (name, url) VALUES (%s, %s)"
rows = [('runoob', 'https://www.runoob.com'), ('python', 'https://python.org')]
# 真实运行时：
# cursor.executemany(INSERT, rows)
# conn.commit()
# print(cursor.rowcount)
print(INSERT.count('%s'))
print(len(rows))
print(rows[0][0])2
2
runoob%s here is the driver's placeholder, not Python string formatting. Writing "... %s" % value concatenates the value in and opens the door to SQL injection.查询数据
fetchone() returns one row, fetchall() all rows, fetchmany(n) n rows — as tuples. Pass cursor(dictionary=True) to get dicts instead.
SELECT = "SELECT id, name FROM sites WHERE name = %s"
# 真实运行时：
# cursor.execute(SELECT, ('runoob',))
# print(cursor.fetchone())
# cursor.execute("SELECT id, name FROM sites")
# print(cursor.fetchall())
sample = [(1, 'runoob'), (2, 'python')]
print(sample[0])
print([r[1] for r in sample])(1, 'runoob')
['runoob', 'python']安装 mysql-connector
Oracle 官方驱动 mysql-connector-python 是纯 Python 实现，无需编译。也可以选 PyMySQL，两者 API 几乎一致，import 时换个名字即可。
cmds = [
"pip install mysql-connector-python",
"pip install PyMySQL",
]
for c in cmds:
print(c)
print(len(cmds))pip install mysql-connector-python
pip install PyMySQL
2创建数据库连接
mysql.connector.connect(host, user, password, database) 建立连接，cursor() 拿到游标执行 SQL。生产环境要用连接池并设置超时。
# import mysql.connector # 需要先 pip install mysql-connector-python
config = {
'host': 'localhost',
'user': 'root',
'password': 'your_password',
'database': 'testdb',
'connection_timeout': 5,
}
# 真实运行时：
# conn = mysql.connector.connect(**config)
# print(conn.is_connected())
print(sorted(config))
print(config['host'])['connection_timeout', 'database', 'host', 'password', 'user']
localhost创建数据库表
用 cursor.execute() 执行 DDL。表名和字段名不能用占位符，只能由代码拼（且必须来自白名单）；值一律用 %s 占位。
DDL = """
CREATE TABLE IF NOT EXISTS sites (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64) NOT NULL,
url VARCHAR(255) NOT NULL,
UNIQUE KEY uk_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
print(DDL.count('CREATE'))
print('utf8mb4' in DDL)
print('PRIMARY KEY' in DDL)1
True
True插入数据
写入后必须 commit()，否则连接关闭时改动会回滚。批量插入用 executemany(sql, seq_of_params)，比循环单条插入快得多。
INSERT = "INSERT INTO sites (name, url) VALUES (%s, %s)"
rows = [('runoob', 'https://www.runoob.com'), ('python', 'https://python.org')]
# 真实运行时：
# cursor.executemany(INSERT, rows)
# conn.commit()
# print(cursor.rowcount)
print(INSERT.count('%s'))
print(len(rows))
print(rows[0][0])2
2
runoob%s 是 mysql-connector 的占位符，不是 Python 的字符串格式化。写成 "... %s" % value 会直接拼接字符串，构成 SQL 注入。查询数据
fetchone() 取一行、fetchall() 取全部、fetchmany(n) 取 n 行。返回的是元组；要字典形式就传 cursor(dictionary=True)。
SELECT = "SELECT id, name FROM sites WHERE name = %s"
# 真实运行时：
# cursor.execute(SELECT, ('runoob',))
# print(cursor.fetchone())
# cursor.execute("SELECT id, name FROM sites")
# print(cursor.fetchall())
sample = [(1, 'runoob'), (2, 'python')]
print(sample[0])
print([r[1] for r in sample])(1, 'runoob')
['runoob', 'python']