SQLAlchemy+pandas_MySql数据库操作

导入数据包

import pandas as pd
from sqlalchemy import create_engine,Table,Column,MetaData,ForeignKey
from sqlalchemy import Column, Integer, String, Float, DECIMAL, Boolean, Enum, Date, DateTime, Time
from sqlalchemy.dialects.mysql import LONGTEXT

创建一个连接引擎

create_engine("数据库类型+数据库驱动://数据库用户名:数据库密码@IP地址:端口/数据库"，其他参数)

engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test_table',echo=True,pool_size=0,pool_recycle=60*30)
print('连接成功！')

1. echo: 当设置为True时会将orm语句转化为sql语句打印，一般debug的时候可用
2. pool_size: 连接池的大小，默认为5个，设置为0时表示连接无限制
3. pool_recycle: 设置时间以限制数据库多久没连接自动断开

创建元数据

metadata=MetaData(engine)

元数据就是描述数据的数据,当我们创建好连接引擎以后可以通过这个引擎抓取元数据，通过MetaData()方法创建了metadata实例，engine绑定要连接引擎，当我们对这个metadata实例进行操作的时候就会直接连接到数据库。

添加表结构

#使用SQLAlchemy通过engine.execute（）方法执行原始SQL删除指定表
sql = """DROP TABLE IF EXISTS address"""
engine.execute(sql)
sql = """DROP TABLE IF EXISTS custom"""
engine.execute(sql)

custom=Table('custom',metadata,
    Column('custom_id',Integer,primary_key=True,autoincrement=True),
    Column('custom_name',String(20)),
    Column('custom_fullname',String(40)),
    Column('custom_gender',Enum('男','女'))      
    )
address = Table('address', metadata,
    Column('id', Integer, primary_key=True),
    Column('custom_id', None, ForeignKey('custom.custom_id')),
    Column('custom_email', String(128), nullable=False),
    Column('custom_Asset', DECIMAL(20,5)),                    #共20位保留5位
                
    )
 
metadata.create_all()

删除表结构

#删除库内的所有表结构
metadata.drop_all(engine)

#使用SQLAlchemy通过engine.execute（）方法执行原始SQL删除指定表
sql = """DROP TABLE IF EXISTS address"""
result = engine.execute(sql)

#删除指定的数据表,要启用“先检查现有表”逻辑，添加 checkfirst=True
address.drop(engine,checkfirst = False)

数据类型

Integer：整形。
Float：浮点类型（四舍五入保留 4 为小数）。
DECIMAL：定点类型（可规定数据长度及小数位数）。
Boolean：传递 True 、 False 进去，在数据库中的显示 1 和 0。
enum：枚举类型（只能输入 Enum 所包含的数值。如：男，女）。
Date：传递 datetime.date() 进去，数据库中存储 年月日。
Time：传递 datetime.time() 进去，数据库中存储 时分秒。
DateTime：传递 datetime.datetime() 进去，数据库中存储 年月日 时分秒 。传入 datetime(2020, 4, 20, 21, 35, 23)
String：字符类型，使用时需要指定长度，区别于 Text 类型。
Text：文本类型。
LONGTEXT：长文本类型

# 这些数据类型都是要导入的
from sqlalchemy import Column, Integer, String, Float, DECIMAL, Boolean, Enum, Date, DateTime, Time
from sqlalchemy.dialects.mysql import LONGTEXT

执行原始SQL

#使用SQLAlchemy通过engine.execute（）方法执行原始SQL
sql = """select * from custom"""
result = engine.execute(sql)
result.fetchmany()

DataFrame写入MYSQL

to_sql(self, name, con, schema=None, if_exists='fail', index=True, index_label=None, 
       chunksize=None, dtype=None, method=None)

name       SQL表的表名, 字符串
con        sqlalchemy.engine.Engine 或 sqlite3.Connection
           使用SQLAlchemy可以使用该库支持的任何数据库
schema     数据库的名字, 可选, 默认为None, 如果不填, 将使用默认的schema
一般参数
if_exists: 如果表已经存在, 如何操作, {'fail', 'replace', 'append'}中的一种, 默认为'fail'
           * fail: 引发ValueError
           * replace: 在插入新值之前删除表
           * append: 将新值插入到现有表
index      将DataFrame的index索引写为一列, 使用'index_label'作为表中的列名. 
           bool型, 默认为True
index_label  索引列的列标签. 字符串或序列, 默认为None
             * 如果index_label为None, 同时index为True, 那么索引名将被使用(index names)
             * 如果DataFrame使用MultiIndex, 则应该给出一个序列
chunksize    行将按指定的大小分批次写入. 整型, 可选, 默认为None
             默认一次性写入所有行
dtype        指定列的数据类型. dict类型, 可选, 默认为None
             字典的键为columns names, 字典的值为SQLAlchemy types或
             strings for the sqlite3 legacy mode
method       控制SQL插入子句的使用, {None, 'multi', callable}中的一个, 默认为None
             * None: 使用标准的SQL'INSERT'子句(每行一个)
             * 'multi': 在单个'INSERT'子句内传递多个值
             * 带'(pd_table, conn, keys, data_iter)'签名的可调用对象 

df.to_sql('address',engine,if_exists = 'replace',index = False)

DataFrame读取MYSQL

这个方法其实是read_sql_query和read_sql_table的封装，read_sql()根据输入选择不同的方法执行

pd.read_sql(sql, con, index_col = None, coerce_float  = True, params = None, sparse_date =None,  columns = None,  chunksize=None,)

df = pd.read_sql(text(sql), engine.connect())**因为存在版本问题pandas与sqlalchemy存在不兼容，需改该种方式

sql  表名或查询语句

con  数据库连接对象, 对于sqlalchemy来说是Engine对象

一般参数

index_col     用作索引的一列或多列
              字符串或字符串的列表, 可选, 默认为None.

coerce_float  尝试把非字符串, 非数值的对象(如decimal.Decimal)转换为浮点数

parse_dates   list或dict, 默认为None
              - 要解析为日期的列名列表
              - {column_name: format string}格式的字典, 其中format string是在解析
                字符串时间时兼容的strftime, 或者是在解析整数时间戳时, 
                (D, s, ns, ms, us)的其中之一       
              - {column_name: arg dict}格式的字典, 其中arg dict对应到函数
                `pandas.to_datetime`的关键字参数
 columns       从SQL表中选取的列名的列表
              列表, 默认为None

chunksize     如果指定, 则返回一个迭代器, 'chunksize'是每个块(chunk)中包含的行数,
              整型, 默认为None

需要注意的是, 时间戳只会被转化为UTC, 而不是我们当地的日期和时间, 
所以我们需要手动加上8小时