python连接MySQL之pysql
Python在2.X版本下连接MySQL使用MySQLdb,但是它不支持Python3.X版本。PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。
安装pymysql
pip3 install pymysql
连接MySQL数据库
db = pymysql.connect(host='localhost', user='root', password='密码', port=3306)
cursor = db.cursor()
PS：password='密码’中的密码是你的MySQL数据库的真实密码
测试连接是否成功：
# sql语句
sql = 'select version()'
# 运行
try:
cursor.execute(sql)
data = cursor.fetchone()
print('VERSION：', data)
except Exception as e:
db.collback()
print(e)
finally:
db.close()
测试结果：
新建数据库
# 连接
db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306)
cursor = db.cursor()
try:
sql = 'create DATABASE ziMing DEFAULT CHARACTER SET utf8'
cursor.execute(sql)
except Exception as e:
print(e)
在数据库中新建数据表
此时，在链接数据库时要绑定数据库，db=‘ziMing’
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
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))'
try:
cursor.execute(sql)
except Exception as e:
print(e)
增加数据1.0
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
id = '100'
name = 'ziMing'
age = '18'
sql = 'insert into students(id, name, age) VALUES(%s, %s, %s)'
try:
cursor.execute(sql, (id, name, age))
print('success')
except Exception as e:
print(e)
db.rollback()
增加数据2.0
利用字典实现动态增加数据
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
data = {
'id': '101',
'name': 'Michael',
'age': 16
}
table = 'students'
keys = ', '.join(data.keys())
print(keys)
values = ', '.join(['%s'] * len(data))
print(values)
sql = 'insert into {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
try:
cursor.execute(sql, tuple(data.values()))
db.commit()
print('Success')
except Exception as e:
print(e)
db.rollback()
finally:
db.close()
查看数据1.0
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
sql = 'select * from students WHERE age > 1'
try:
cursor.execute(sql)
print('count:', cursor.rowcount)
data = cursor.fetchone()
print('data:', data)
datas = cursor.fetchall()
print('datas:', datas)
for row in datas:
print(row)
print('success')
except Exception as e:
print(e)
db.rollback()
查看数据2.0
用while循环查询每一行的数据，fetchall()的内存开销
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
# cursor = db.cursor()
# sql = 'select * from students WHERE age > 1'
# try:
# cursor.execute(sql)
# print('count:', cursor.rowcount)
# data = cursor.fetchone()
# while data:
# print(data)
# data = cursor.fetchone()
# except Exception as e:
# print(e)
# db.rollback()
修改数据
用where定位，再用update修改
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
sql = 'update students set age = %s WHERE name = %s'
try:
cursor.execute(sql, (36, 'Janel'))
db.commit()
print('Success')
except Exception as e:
print(e)
db.rollback()
finally:
db.close()
插入数据
data = {
'id': '101',
'name': 'Michael',
'age': 16
}
table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data))
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
# ON DUPLICATE KEY UPDATE:如果主键已经存在，就执行更新操作
sql = 'insert into {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE '.format(table=table, keys=keys, values=values)
update = ', '.join([" {key} = %s".format(key=key) for key in data])
sql += update
try:
cursor.execute(sql, tuple(data.values()) * 2)
db.commit()
print('Success')
except Exception as e:
print(e)
db.rollback()
finally:
db.close()
删除数据
同样，也是先用where定位数据，再用delete删除数据
db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
table = 'students'
condition = 'age > 18'
sql = 'delete from {table} WHERE {condition}'.format(table=table, condition=condition)
try:
cursor.execute(sql)
db.commit()
print('Success')
except Exception as e:
print(e)
db.rollback()
finally:
db.close()
总结
上面是Python使用pymysql连接MySQL数据库并进行简单操作的示例。显然，如此频繁的编写sql语句无法做到高效所以，我将在另一篇博客中介绍如何使用Python + sqlalchemy连接MySQL数据库，并做简单的操作。
---------------------
|
|
收藏
淘帖
踩
