关系数据库-2-[mysql8]python3操作mysql
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
参考文档Python-PyMysql详解
参考文档使用pandas操作MySQL数据库
1 pymysql操作mysql
PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库。
PyMySQL 遵循 Python 数据库 API v2.0 规范并包含了 pure-Python MySQL 客户端库。
pip install PyMySQL
1.1 连接数据库
import pymysql
# (1)数据库信息
class config(object):
HOST = "localhost"
PORT = 3306
USERNAME = "root"
PASSWORD = "xxxx"
DBNAME = "test"
# 创建连接
con = pymysql.Connect(host=config.HOST,
port=config.PORT,
user=config.USERNAME,
passwd=config.PASSWORD,
db=config.DBNAME)
# 使用cursor()方法创建一个游标对象cursor
cur = con.cursor()
# 使用execute()方法执行SQL查询获取数据库版本
cur.execute("SELECT VERSION()")
# 使用fetchone()方法获取单条数据.
data = cur.fetchone()
print(data)
# 关闭不使用的游标对象
cur.close()
# 关闭数据库连接
con.close()
输出如下(‘8.0.19’,)。
1.2 创建数据表
如果数据库连接存在我们可以使用execute()方法来为数据库创建表如下所示创建表EMPLOYEE
import pymysql
# (1)数据库信息
class config(object):
HOST = "localhost"
PORT = 3306
USERNAME = "root"
PASSWORD = "xxxx"
DBNAME = "test"
# 创建连接
con = pymysql.Connect(host=config.HOST,
port=config.PORT,
user=config.USERNAME,
passwd=config.PASSWORD,
db=config.DBNAME)
# 使用cursor()方法创建一个游标对象cursor
cur = con.cursor()
# 使用预处理语句创建表
sql_create = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
#执行sql语句
cur.execute(sql_create)
# 关闭不使用的游标对象
cur.close()
# 关闭数据库连接
con.close()
1.3 插入操作
1.3.1 普通的插入
以下实例使用执行SQL INSERT语句向表EMPLOYEE插入记录
import pymysql
# (1)数据库信息
class config(object):
HOST = "localhost"
PORT = 3306
USERNAME = "root"
PASSWORD = "xxxx"
DBNAME = "test"
# 创建连接
con = pymysql.Connect(host=config.HOST,
port=config.PORT,
user=config.USERNAME,
passwd=config.PASSWORD,
db=config.DBNAME)
# 使用cursor()方法创建一个游标对象cursor
cur = con.cursor()
# SQL 插入语句
sql_insert = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
# 执行sql语句
cur.execute(sql_insert)
# 提交到数据库执行
con.commit()
except:
# 如果发生错误则回滚
con.rollback()
# 关闭不使用的游标对象
cur.close()
# 关闭数据库连接
con.close()
1.3.2 变量传值
import pymysql
# (1)数据库信息
class config(object):
HOST = "localhost"
PORT = 3306
USERNAME = "root"
PASSWORD = "xxxx"
DBNAME = "test"
# 创建连接
con = pymysql.Connect(host=config.HOST,
port=config.PORT,
user=config.USERNAME,
passwd=config.PASSWORD,
db=config.DBNAME)
# 使用cursor()方法创建一个游标对象cursor
cur = con.cursor()
first_name='lucy'
last_name='kalt'
age=22
sex='M'
income=1800
# SQL 插入语句
sql_insert = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('{}','{}',{},'{}',{})""".format(first_name, last_name, age, sex, income)
print(sql_insert)
try:
# 执行sql语句
cur.execute(sql_insert)
# 执行sql语句
con.commit()
except:
# 发生错误时回滚
con.rollback()
# 关闭不使用的游标对象
cur.close()
# 关闭数据库连接
con.close()
1.3.3 批量写入
通过python向mysql插入大量数据时可以有两种方法
1、for + cursor.execute(sql)最后集中提交commit()。
2、cursor.executemany(sql,list)。
两种方法效率上和功能上有一定差异。26万条数据使用第一种方法需要约1.5小时使用第二种方法只需要10几秒。
import pymysql
# (1)数据库信息
class config(object):
HOST = "localhost"
PORT = 3306
USERNAME = "root"
PASSWORD = "xxxx"
DBNAME = "test"
# 创建连接
con = pymysql.Connect(host=config.HOST,
port=config.PORT,
user=config.USERNAME,
passwd=config.PASSWORD,
db=config.DBNAME)
# 使用cursor()方法创建一个游标对象cursor
cur = con.cursor()
# 必须是元组列表的格式
list_1 = [('lily','kalt',22,'M',1900),('jim','kalt',22,'M',2000)]
# SQL 插入语句
# 注意这里的%s不需要加引号使用execute需要加引号。
# 另外executemany 在执行过程中能够将python的None转为sql的null这一点很方便。
sql_1 = """insert into EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME) values(%s,%s,%s,%s,%s)"""
try:
# 执行sql语句
cur.executemany(sql_1,list_1)
# 执行sql语句
con.commit()
except:
# 发生错误时回滚
con.rollback()
# 关闭不使用的游标对象
cur.close()
# 关闭数据库连接
con.close()
1.4 查询操作
Python查询Mysql使用fetchone()方法获取单条数据, 使用fetchall()方法获取多条数据。
fetchone(): 该方法获取下一个查询结果集。结果集是一个对象。
fetchall(): 接收全部的返回结果行.
rowcount: 这是一个只读属性并返回执行execute()方法后影响的行数。
1.4.1 返回元组
import pymysql
# (1)数据库信息
class config(object):
HOST = "localhost"
PORT = 3306
USERNAME = "root"
PASSWORD = "xxxx"
DBNAME = "test"
# 创建连接
con = pymysql.Connect(host=config.HOST,
port=config.PORT,
user=config.USERNAME,
passwd=config.PASSWORD,
db=config.DBNAME)
# 使用cursor()方法创建一个游标对象cursor
cur = con.cursor()
# SQL 查询语句
sql_select = "SELECT * FROM EMPLOYEE \
WHERE INCOME > %s" % (1000)
try:
# 执行SQL语句
cur.execute(sql_select)
# 获取所有记录列表,以元组来存储
results = cur.fetchall()
print(results)
# 遍历元组存值
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
# 打印结果
print("fname={},lname={},age={},sex={},income={}".format(fname, lname, age, sex, income))
except:
print("错误没有查找到数据")
# 关闭不使用的游标对象
cur.close()
# 关闭数据库连接
con.close()
1.4.2 转为DataFrame
返回的results可以直接转换为DataFrame。
import pandas as pd
# 指定好列名
df = pd.DataFrame(results,columns=["fname","lname","age","sex","income"])
print(df)
1.5 更新操作
更新操作用于更新数据表的数据以下实例将表中SEX为 ‘M’ 的AGE字段递增 1
import pymysql
# (1)数据库信息
class config(object):
HOST = "localhost"
PORT = 3306
USERNAME = "root"
PASSWORD = "xxxx"
DBNAME = "test"
# 创建连接
con = pymysql.Connect(host=config.HOST,
port=config.PORT,
user=config.USERNAME,
passwd=config.PASSWORD,
db=config.DBNAME)
# 使用cursor()方法创建一个游标对象cursor
cur = con.cursor()
# SQL 更新语句
sql_update = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '{}'".format('M')
try:
# 执行SQL语句
cur.execute(sql_update)
# 提交到数据库执行
con.commit()
except:
# 发生错误时回滚
con.rollback()
# 关闭不使用的游标对象
cur.close()
# 关闭数据库连接
con.close()
1.6 删除操作
删除操作用于删除数据表中的数据以下实例演示了删除数据表 EMPLOYEE 中 AGE 大于 22 的所有数据
import pymysql
# (1)数据库信息
class config(object):
HOST = "localhost"
PORT = 3306
USERNAME = "root"
PASSWORD = "xxxx"
DBNAME = "test"
# 创建连接
con = pymysql.Connect(host=config.HOST,
port=config.PORT,
user=config.USERNAME,
passwd=config.PASSWORD,
db=config.DBNAME)
# 使用cursor()方法创建一个游标对象cursor
cur = con.cursor()
# SQL 删除语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > {}".format(22)
try:
# 执行SQL语句
cur.execute(sql)
# 提交修改
con.commit()
except:
# 发生错误时回滚
con.rollback()
# 关闭不使用的游标对象
cur.close()
# 关闭数据库连接
con.close()
1.7 执行事务
事务机制可以确保数据一致性。
事务应该具有4个属性原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
(1)原子性atomicity。一个事务是一个不可分割的工作单位事务中包括的诸操作要么都做要么都不做。
(2)一致性consistency。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
(3)隔离性isolation。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的并发执行的各个事务之间不能互相干扰。
(4)持久性durability。持续性也称永久性permanence指一个事务一旦提交它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
Python DB API 2.0 的事务提供了两个方法 commit 和 rollback。
# SQL删除记录语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
# 执行SQL语句
cur.execute(sql)
# 向数据库提交
con.commit()
except:
# 发生错误时回滚
con.rollback()
对于支持事务的数据库 在Python数据库编程中当游标建立之时就自动开始了一个隐形的数据库事务。commit()方法提交游标的所有更新操作rollback()方法回滚当前游标的所有操作。每一个方法都开始了一个新的事务。
1.8 自动提交
在创建连接的时候增加参数 autocommit = 1 当发生update等操作时会实时更新到数据库内。否则要通过 conn.commit() 来提交到数据库进行具体操作。
如果没有设置自动提交也没有手动提交当进行插入或更新等操作时只在本地客户端能看到更新在其他客户端或数据库内数据无变化。
# 实时操作适合随时少量、频繁的更新
import pymysql
con = pymysql.connect(
host='XXX.XXX.XXX.XXX',
user='XXXXX',
password='XXXXX',
db='XXXXX',
port=3306,
charset='utf8',
autocommit = 1 ,
)
# 集中大批量操作数据
import pymysql
con = pymysql.connect(
host='XXX.XXX.XXX.XXX',
user='XXXXX',
password='XXXXX',
db='XXXXX',
port=3306,
charset='utf8',
)
SQl相关操作略
con.commit() # 提交
2 pandas操作mysql
使用PyMySQL包获取的数据为元组或者字典格式如果要做进一步的处理还需要转换成pandas的DataFrame格式比较麻烦。而直接用pandas的read_sql_query()函数可以直接得到DataFrame格式的数据而且同样可以对表进行增删改查的操作。
2.1 连接数据库
import pymysql
import pandas as pd
import sqlalchemy
# (1)数据库信息
class config(object):
HOST = "localhost"
PORT = 3306
USERNAME = "root"
PASSWORD = "xxxx"
DBNAME = "test"
#创建数据库的连接引擎
# 例如'mysql+pymysql://root:4869@127.0.0.1:3306/question?charset=utf8'需要结合import pymysql
# 例如'mysql://用户名:密码@主机名或IP地址:端口/库名称?charset=utf8'需要结合import MySQLdb
con_str = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(config.USERNAME,config.PASSWORD,config.HOST,config.PORT,config.DBNAME)
# con_str = 'mysql://{}:{}@{}:{}/{}?charset=utf8'.format(config.USERNAME,config.PASSWORD,config.HOST,config.PORT,config.DBNAME)
engine=sqlalchemy.create_engine(con_str )
2.2 读取数据
sql可以是表名称读取该表所有数据存为dataframe, sql也可以是一句可以执行的sql语句比如select语句。
2.2.1 pd.read_sql()
(1)表名称
df = pd.read_sql(sql='employee', con=engine)
print(df)
(2)执行sql语句
sql_str = "select * from employee"
df1 = pd.read_sql(sql=sql_str, con=engine)
print(df1)
2.2.2 pd.read_sql_query()
只能是sql语句
sql_str = "select * from employee"
df1 = pd.read_sql_query(sql=sql_str, con=engine)
print(df1)
read_sql_query()函数还有一些其他的参数
coerce尝试将非字符串及非数值对象转换成浮点数字默认为True
params可以是列表、字典、元组用于存储动态拼接SQL命令的变量
parse_dates将指定字段解析成日期格式
chunksize如果不设置返回的是DataFrame对象
2.3 写入数据
df.to_sql(name='tablename', con=engine, if_exists='append', index=False, chunksize=1000)
(1)name 指定的是目标数据库表
(2)con 与数据库链接的方式推荐使用sqlalchemy的engine类型
(3)if_exists 当数据库中已经存在数据表时对数据表的操作有replace替换、append追加fail则当表存在时提示ValueError
(4)index 对DataFrame的index索引的处理=True时索引也将作为数据写入数据表
(5)chunsize 整数一次写入数据时的数据行数量
如果没有表会自动创建表其字段类型可能不符合要求所以还是提前创建。
列名对应表中的字段。