利用PyMySQL连接MySQL
连接数据库
import pymysql
# 连接MySQL MySQL在本地运行 用户名为root 密码为123456 默认端口3306
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306)
# cursor()方法获得MySQL的操作游标,利用游标来执行SQL语句,其中执行方法为execute()
cursor = db.cursor()
# 获取MySQL的当前版本
cursor.execute('SELECT VERSION()')
# fetchone()方法获得第一条数据,即版本号
data = cursor.fetchone()
print('Database version:', data)
# 创建一个名为reptile的数据库,默认编码为utf8mb4
cursor.execute("CREATE DATABASE reptile DEFAULT CHARACTER SET utf8mb4")
db.close()
# 运行输出:
Database version: ('8.0.16',)
# 运行生成一个名为reptile的数据库
创建表
import pymysql
db = pymysql.connect(host='localhost', user='用户名', password='密码', port=3306, db='reptile')
cursor = db.cursor()
sql = '''CREATE TABLE
IF NOT EXISTS students
(
id VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
age INT NOT NULL,
PRIMARY KEY (id)
)'''
# 创建一个名为students的数据表,主键为id
cursor.execute(sql)
db.close()
# 运行创建一个数据表
插入数据
方式1
import pymysql
id = '20180001'
user = 'Lee Hua'
age = 20
# 连接数据库
db = pymysql.connect(host='localhost', user='用户名', password='密码', port=3306, db='reptile')
# 获得MySQL的操作游标
cursor = db.cursor()
sql = '''INSERT INTO students(id, name, age) VALUES(%s %s %s)'''
try:
# 执行
cursor.execute(sql, (id, user, age))
# 数据插入、更新、删除操作,都需要用到commit()方法才能生效
db.commit()
except:
# 调用rollback()执行数据回滚,相当于什么都没有发生过
db.rollback()
db.close()
# 运行,数据被插入到数据表中
方式2
import pymysql
db = pymysql.connect(host='localhost', user='用户名', password='密码', port=3306, db='reptile')
cursor = db.cursor()
data = {
'id': '20180002',
'user': 'Lao wang',
'age': 19
}
table_name = 'students'
keys = ', '.join(data.keys()) # id, user, age
values = ', '.join(['%s'] * len(data)) # ['%s', '%s', ......] len(data)个'%s'
sql = '''INSERT INTO {table_name}({keys}) VALUES({values})'''.format(table_name=table_name, keys=keys, values=values)
# sql = INSERT INTO students(id, name, age) VALUES(%s %s %s)
try:
tuple_ = tuple(data.values())
if cursor.execute(sql, tuple_):
print('成功插入数据')
db.commit()
except:
print('插入数据失败')
db.rollback()
db.close()
更新数据
import pymysql
db = pymysql.connect(host='localhost', user='用户名', password='密码', port=3306, db='reptile')
cursor = db.cursor()
sql = 'UPDATA students SET age = %s WHERE name = %s'
try:
cursor.execute(sql, (25, 'Bob'))
db.commit()
except:
db.rollback()
db.close()
实现去重(如果数据存在,则更新数据;如果数据不存在,则插入数据。)
import pymysql
db = pymysql.connect(host='localhost', user='用户名', password='密码', port=3306, db='reptile')
cursor = db.cursor()
data = {
'id': '20180002',
'user': 'Lao wang',
'age': 19
}
table_name = 'students'
keys = ', '.join(data.keys()) # keys = id, name, age
values = ', '.join(['%s'] * len(data)) # values = %s, %s, %s
update = ', '.join(
[" {key} = %s".format(key=key) for key in data]
) # id = %s, name = %s, age = %s
sql = '''INSERT INTO {table_name}({keys}) VALUES({values}) ON DUPLICATE KEY update'''.format(table_name=table_name, keys=keys, values=values)
# ON DUPLICATE KEY UPDATE 表示:如果主键已经存在,那么就执行更新操作
try:
tuple_ = tuple(data.values())
if cursor.execute(sql, tuple_):
print('成功插入数据')
db.commit()
except:
print('插入数据失败')
db.rollback()
db.close()
删除数据
import pymysql
db = pymysql.connect(host='localhost', user='用户名', password='密码', port=3306, db='reptile')
cursor = db.cursor()
table = 'students'
condition = 'age > 20'
sql = 'DELETE FROM {table} WHERE {conditon}'.format(table=table, conditon=condition)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()
# 删除age > 20的数据
查询数据
import pymysql
db = pymysql.connect(host='localhost', user='用户名', password='密码', port=3306, db='reptile')
cursor = db.cursor()
sql = 'SELECT * FROM students WHERE age >= 20'
try:
cursor.execute(sql)
print('Count:', cursor.rowcount) # 调用cursor的rowcount属性,获取查询结果的条数
one = cursor.fetchone() # 获取结果的第一条信息
print('One:', one)
result = cursor.fetchall() # 获取结果的所有数据
print('Result:', result)
print('Result Type:', type(result))
for row in result:
print(row)
except:
print('Error')
# 输出:
Count: 0
One: None
Result: ()
Result Type: <class 'tuple'>
一次性查询所有数据
import pymysql
db = pymysql.connect(host='localhost', user='用户名', password='密码', port=3306, db='reptile')
cursor = db.cursor()
sql = 'SELECT * FROM students WHERE age >= 20'
try:
cursor.execute(sql)
print('Count:', cursor.rowcount) # 调用cursor的rowcount属性,获取查询结果的条数
row = cursor.fetchone() # 获取结果的第一条数据
while row:
print('Row:', row)
row = cursor.fetchone()
except:
print('Error')
作者:Lee Hua