sqlalchemy mysql python 解决方案及相关问题

三种sqlalchemy 连接方式

pip install pymysql  对应  "mysql+pymysql://...."

pip install mysqlclient对应 "mysql+mysqldb://...."

pip install mysql-connector对应 "mysql+mysqlconnector://...."

 

 

时间戳TimeStamp问题，直接使用sqlalchemy 中的TIMESTAMP作为Column类型参数，当上传会话时会报错类似'Timestamp' object has no attribute 'translate'.

所以如果直接使用pandas里自带的dataframe.to_sql，是无法使用的会报错。

此时，需要将时间数据类型由TimeStamp转为Datetime类型，t.to_pydatetime()，同时from sqlalchemy import Column, DateTime。

from sqlalchemy import Column, Integer, String, create_engine, ForeignKey, Sequence, DateTime
import sqlalchemy
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import pandas as pd
# import pymysql


# 创建对象的基类:
Base = declarative_base()

class StockListOrgin(Base):
    __tablename__ = 'stock_list_orgin'

    id = Column(String(30), Sequence('stock_list_id_seq'), primary_key=True, nullable=False, unique=True)
    display_name = Column(String(50))
    name = Column(String(20))
    start_date = Column(DateTime)
    end_date = Column(DateTime)
    stype = Column(String(20))


class SqlStock:
    def __init__(self, echo=True):
        src = "mysql+pymysql://localhost:3306/test"
        self.engine = create_engine(src, echo=echo)
        Base.metadata.create_all(self.engine)
        # 创建DBSession类型:
        DBSession = sessionmaker(bind=self.engine)
        # 创建session对象:
        self.session = DBSession()

    def __del__(self):
        self.session.close()

    """    def update_stocklist_pd(self, stockpd):
        stockpd.to_sql("stock_list_orgin", self.engine, if_exists="replace", index=False,
                       dtype={"display_name" : sqlalchemy.types.String(length=50),
                            "name" : sqlalchemy.types.String(length=20),
                            "start_date" : sqlalchemy.DateTime(),
                            "end_date" : sqlalchemy.DateTime(),
                            "type" : sqlalchemy.types.String(length=20)})"""

    def update_stocklist_pd(self, stockpd):

        for index in stockpd.index:
            display_name, name, start_date, end_date, stype = stockpd.loc[index]

            # 创建新对象:
            stock = StockListOrgin(id=index, display_name=display_name, name=name,
                                   start_date=start_date.to_pydatetime(), end_date=end_date.to_pydatetime(), stype=stype)
            # 添加到session:
            self.session.add(stock)
        # 提交即保存到数据库:
        self.session.commit()

以上start_date为TimeStamp类型

 

参考：

解决SQLAlchemy（Python）调用MySQL数据库时插入时间戳数据的问题https://blog.csdn.net/xiecheng1995/article/details/90900496

https://docs.sqlalchemy.org/en/13/core/tutorial.html

datetime，Timestamp和datetime64之间转换  https://www.cnblogs.com/nxf-rabbit75/p/11111825.html