Small examples of sqlalchemy mappings

1. Multiple tables mapped to a class

Import Pandas as PD
From Settings Import DATABASES
From SQLAlchemy import Create_engine
Import Sqlalchemy.orm
Import sqlalchemy.ext.declarative

metadata = SQLAlchemy. MetaData ()

stk_basic_info_table = SQLAlchemy. Table (' Quant_stk_basic_info ', metadata,
SQLAlchemy. Column (' Stk_uni_code ', SQLAlchemy. Integer, Primary_key = True),
SQLAlchemy. Column (' Stk_code ', SQLAlchemy. String (23)),
)

fct_value_row_q_table = SQLAlchemy. Table (' quant_fct_value_row_q ', metadata,
SQLAlchemy. Column (' Sec_uni_code ', SQLAlchemy. Integer, SQLAlchemy. ForeignKey (' Quant_stk_basic_info. Stk_uni_code '), primary_key=true),
SQLAlchemy. Column ("End_date", SQLAlchemy. Date),
SQLAlchemy. Column ("A7041000005", SQLAlchemy. Float),

）

J_q = Sqlalchemy.join (stk_basic_info_table, fct_value_row_q_table)

#首先需要生成一个BaseModel类, as the base class for all model classes
Basemodel = Sqlalchemy.ext.declarative.declarative_base ()

#构建 Data Model Factor
Class Factor_q (Basemodel):
__table__ = J_q
Gp_unicode = Sqlalchemy.orm.column_property (Stk_basic_info_table.c.stk_uni_code, Fct_value_row_q_table.c.sec_uni_ CODE)
Gpcode = Stk_basic_info_table.c.stk_code
Date = Fct_value_row_q_table.c.end_date
Acc_pay_tdays = fct_value_row_q_table.c.a7041000005

# link Database with Session object

Self._engine_mysql = Create_engine (
databases["Quant"].get ("engine") + "://" + databases["Quant"].get ("user") + ":" + databases[
"Quant"].get ("password") + "@" + databases["Quant"].get ("host") + "/" + databases["Quant"].get (
"DB") + "? Charset=utf8")
Dbsession = Sqlalchemy.orm.sessionmaker (bind=self._engine_mysql) # Create a callback class
Session = Dbsession () # Create a Reply object

Q = Session.query (factor_q.acc_pay_tdays). Filter (Factor_Q.gpcode.in_ ([' 002252.XSHE ', ' 002253.XSHE '])

For data in Q:

Print data

2. Single-sheet mapping to a class

# First you need to generate a Basemodel class as the base class for all model classes
Basemodel = Sqlalchemy.ext.declarative.declarative_base ()


# Build a data Model Fin_idx_ana
Class Fin_idx_ana (Basemodel):
__tablename__ = ' fin_idx_ana ' # table name
__table_args__ = (
Primarykeyconstraint (' Com_uni_code ', ' end_date '),
)

# table Structure
Com_unicode = SQLAlchemy. Column ("Com_uni_code", SQLAlchemy. Integer)
Gpcode = SQLAlchemy. Column ("Stk_code", SQLAlchemy. String (Ten), Nullable=false)
Date = SQLAlchemy. Column ("End_date", SQLAlchemy. Date)
BEPS = SQLAlchemy. Column ("BEPS", SQLAlchemy. Float, default=0)

The subsequent invocation method is the same as above 1.

Demo:

Baidu Cloud (13207134391)

Python/sqlalchemy

Small examples of sqlalchemy mappings