sqlalchemy的基本使用

常用导入

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DATE, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import and_, or_
from sqlalchemy import func

创建模型

Base = declarative_base()


class Code(Base):
    __tablename__ = 'code'
    symbol = Column(String(6), unique=True, primary_key=True)
    ts_code = Column(String(9), unique=True)
    update_by = Column(DATE, default=date.today())

    def __repr__(self):
        return '<%s: symbol(%s)>\n'%(self.__class__.__name__,self.symbol)


class Basic(Base):
    __tablename__ = 'basic'
    id = Column(Integer, primary_key=True)
    code_id = Column(String(6), ForeignKey(Code.symbol))
    name = Column(String(20), nullable=True)
    code = relationship(Code,backref='basic') #使两个对象在内存中可以互相调用

    def __str__(self):
        return '<%s: code_id(%s)>\n'%(self.__class__.__name__,self.code_id)


engine = create_engine("mysql+mysqldb://root:z123@127.0.0.1:3306/test?charset=utf8mb4") 
Base.metadata.create_all(engine)

基本操作

# 创建回话
session = sessionmaker(bind=engine)()


# 定位（返回sql语句）
session.query(Code)
session.query(Code.symbol,Code.ts_code)
session.query(Code.symbol,func.count())


# 增加
code1=Code(symbol='000001',ts_code='000001.SZ')
code2=Code(symbol='000002',ts_code='000002.SZ')
code4=Code(symbol='000004',ts_code='000004.SZ')
session.add(code1) #单项
session.add_all([code2,code4]) #多项
session.commit()


# 删除
session.query(Code).filter(Code.symbol=='000001').delete() #单项
session.query(Code).filter(Code.symbol.in_(['000002','000004'])).delete(synchronize_session=False) #多项
session.commit()


# 改
session.query(Code).filter(Code.symbol=='000001').update({'ts_code':'9999'})


# 单条件查找
session.query(Code).filter(Code.symbol=='000001').all()
session.query(Code).filter_by(symbol='000001').all()
session.query(Code).filter_by(symbol='000001').first()


# 多条件查找
session.query(Code).filter(Code.symbol>'000001').filter(Code.symbol<'000005').all()
session.query(Code).filter(Code.symbol.between('000001','000005'),Code.ts_code == '000002.SZ').all()
session.query(Code).filter(Code.symbol.in_(['000001','000005'])).all()
session.query(Code).filter(or_(Code.symbol == '000001', Code.ts_code == '000002.SZ')).all()


# 其他查找
session.query(Code)[0:3]


# 通配符
session.query(Code).filter(Code.symbol.like('0%')).all()


# 排序
session.query(Code).order_by(Code.symbol.desc(), Code.ts_code.asc()).all()


# 分组
session.query(Code.symbol,func.count(Code.symbol)).group_by(Code.symbol).all()

nums = func.count('*').label('c')
session.query(Code.symbol,nums ).filter_by(symbol='000001').group_by(Code.symbol).having(nums > 10)


# 回滚与提交
session.rollback()
session.commit()


# 关联生成
basic= Basic(name="pingan",code_id='000001')
session.add(basic)
session.commit()


# 关联查询
c = session.query(Code).filter(Code.symbol=='000001').first()
for b in c.basic:
	print(b.name)

b = session.query(Basic).filter(Basic.code_id=='000001').first()
print(b.code.ts_code)

逆向生成表模型

pip install sqlacodegen
sqlacodegen mysql+mysqldb://root:z123@127.0.0.1:3306/test?charset=utf8mb4 > test.py

补充

pip install mysqlclient