初步使用sqlachemy, 是因为要把一个定时任务转移到airflow下面. 所以告别的django的ORM. 不过话说, 还是感觉django的orm更好用.
根据现有表生成orm class
pip install -i https://pypi.doubanio.com/simple sqlacodegen
sqlacodegen 'mysql+pymysql://user:password@ip:port/database?charset=utf8mb4'
1.orm建立
import datetime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://username:password@host:port/dbname")
BaseModel = declarative_base()
class SkuSelectedAmount(BaseModel):
__tablename__ = 'sku_selected_amount'
id = Column(Integer, primary_key=True)
client_id = Column(Integer, nullable=True)
user_name = Column(String(100), nullable=True)
selected_time = Column(DateTime, nullable=True)
sku_amount = Column(Integer, nullable=True)
style_amount = Column(Integer, nullable=True)
class_field = Column('class', String(100), nullable=True) # 数据库中的字段名称是class, 但这是python的保留字. 这种方法等同于django的db_column='class'
pid = Column(Integer, nullable=True)
create_time = Column(DateTime, default=datetime.datetime.now) # 等同于auto_now_add=True
update_time = Column(DateTime, onupdate=datetime.datetime.now) # 等同于auto_now=True
2. 调用
DBSession = sessionmaker(bind=engine)
session = DBSession()
查
session.query(SkuSelectedAmount).filter(SkuSelectedAmount.sku_amount==0).first()
或者
session.query(SkuSelectedAmount).filter_by(sku_amount=0)
session.query(SkuSelectedAmount).all()
增
obj = SkuSelectedAmount(sku_amount=1, xx=xx...)
session.add(obj)
session.commit()
改
obj = ...
obj.sku_amount = 1
session.add(obj)
session.commit()
或
obj.update({x:x})
session.commit()
删
session.query(SkuSelectedAmount).filter_by(xx=xx).delete()
session.commit()
get_or_create
def get_or_create(session, model, **kwargs):
"""
定义sqlalchemy的get_or_create方法
:param session:
:param model:
:param kwargs:
:return:
"""
instance = session.query(model).filter_by(**kwargs).first()
if instance:
return instance, False
else:
instance = model(**kwargs)
session.add(instance)
session.commit()
return instance, True
3. 踩过的坑
如果遇到这种报错
OperationalError: (_mysql_exceptions.OperationalError) (1193, "Unknown system variable 'transaction_isolation'") (Background on this error at: [http://sqlalche.me/e/e3q8)]
是因为在mysql中找不到transaction_isolation
可以去你的mysql里show variables like 't%_isolation';, 然后你会发现返回的内容是tx_isolation,READ-COMMITTED, 这是因为自mysql5.7.20之前用的是transaction_isolation之后是tx_isolation.
所以这里的解决办法可以是更新mysql版本. 但如果你像我们的项目一样, 用的是mariadb(版本10.1约等于mysql5.6), 那可能更好的办法就是改sqlalchemy的源码
位置为:lib\site-packages\sqlalchemy\dialects\mysql\base.py", line 1569的get_isolation_level函数下.
