Python 如何使用 SQLAlchemy 只创建一张表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19175311/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create only one table with SQLAlchemy?
提问by Lazik
I am unable to create a single table using SQLAlchemy.
我无法使用 SQLAlchemy 创建单个表。
I can create it by calling Base.metadata.create_all(engine)but as the number of table grows, this call takes a long time.
我可以通过调用来创建它，Base.metadata.create_all(engine)但是随着表数量的增加，这个调用需要很长时间。
I create table classes on the fly and then populate them.
我即时创建表类，然后填充它们。
from sqlalchemy import create_engine, Column, Integer, Sequence, String, Date, Float, BIGINT
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class HistoricDay():
id = Column(Integer, Sequence('id_seq'), primary_key=True)
# Date, Open, High, Low, Close, Volume, Adj Close
date = Column(Date)
open = Column(Float)
high = Column(Float)
low = Column(Float)
close = Column(Float)
volume = Column(BIGINT)
adjClose = Column(Float)
def __init__(self, date, open, high, low, close, volume, adjClose):
self.date = date
self.open = open
self.high = high
self.low = low
self.close = close
self.volume = volume
self.adjClose = adjClose
def build_daily_history_table_repr(self):
return "<"+self.__tablename__+"('{}','{}','{}','{}','{}','{}','{}','{}')>".format(self.id, self.date, self.open, self.high, self.low, self.close, self.volume, self.adjClose)
def build_daily_history_table(ticket):
classname = ticket+"_HistoricDay"
globals()[classname] = type(classname, (HistoricDay,Base), {'__tablename__' : ticket+"_daily_history"})
setattr(globals()[classname], '__repr__', build_daily_history_table_repr)
# Initialize the database :: Connection & Metadata retrieval
engine = create_engine('mysql+cymysql://root@localhost/gwc?charset=utf8&use_unicode=0', pool_recycle=3600) # ,echo = True
# SqlAlchemy :: Session setup
Session = sessionmaker(bind=engine)
# Create all tables that do not already exist
Base.metadata.create_all(engine)
# SqlAlchemy :: Starts a session
session = Session()
ticketList = getTicketList()
for ticket in ticketList:
build_daily_history_table(ticket)
class_name = ticket+"_HistoricDay"
meta_create_all_timer = time.time()
# Create all tables that do not already exist
# globals()[class_name]('2005-07-24',0,0,0,0,0,0).create(engine) #doesn't work
#(globals()[class_name]).__table__.create(engine) #doesn't work
# session.commit() #doesn't work
#Base.metadata.create_all(engine) # works but gets very slow
print(" meta_create_all_timer {}s".format(time.time()-meta_create_all_timer))
data = getData(ticket)
for m_date, m_open, m_close, m_high, m_low, m_volume, m_adjClose in data:
entry = globals()[class_name](m_date, m_open, m_high, m_low, m_close, m_volume, m_adjClose)
session.add(entry)
session.commit()
I saw in the documentationthat you can do
我在文档中看到你可以做
engine = create_engine('sqlite:///:memory:')
meta = MetaData()
employees = Table('employees', meta,
Column('employee_id', Integer, primary_key=True),
Column('employee_name', String(60), nullable=False, key='name'),
Column('employee_dept', Integer, ForeignKey("departments.department_id"))
)
employees.create(engine)
However, I'm not able to figure out how to do the same thing as Tabledoes, with declarative_base().
但是，我无法弄清楚如何Table使用declarative_base().
How can I do that with classes that inherit from declarative_base()?
我怎么能用继承自的类做到这一点declarative_base()？
采纳答案by Bleeding Fingers
Above, the declarative_base() callable returns a new base class from which all mapped classes should inherit. When the class definition is completed, a new Table and mapper() will have been generated.
The resulting table and mapper are accessible via
__table__and__mapper__attributes
上面， declarative_base() 可调用返回一个新的基类，所有映射类都应从该基类继承。类定义完成后，将生成一个新的 Table 和 mapper()。
结果表和映射器可通过
__table__和__mapper__属性访问
(From here)
（从这里）
Therefore:
所以：
def build_daily_history_table(ticket):
classname = ticket + "_HistoricDay"
ticket = type(classname, (Base, HistoricDay), {'__tablename__' : ticket+"_daily_history"})
ticket.__repr__ = build_daily_history_table_repr
return ticket
build_daily_history_table("test").__table__.create(bind = engine)
Output:
输出：
2013-10-04 22:36:53,263 INFO sqlalchemy.engine.base.Engine
CREATE TABLE test_daily_history (
id INTEGER NOT NULL,
date DATE,
open FLOAT,
high FLOAT,
low FLOAT,
close FLOAT,
volume BIGINT,
"adjClose" FLOAT,
PRIMARY KEY (id)
)
2013-10-04 22:36:53,263 INFO sqlalchemy.engine.base.Engine ()
2013-10-04 22:36:53,263 INFO sqlalchemy.engine.base.Engine COMMIT
Credit goes to javex'scomment/correction or I might have suggested something akin to:
幸得javex的意见/修正或者我可能会提出一些类似于：
Base.metadata.tables["ticket_daily_history"].create(bind = engine)
Advise:
建议：
The approach used in build_daily_history_tablecould be one of the least elegant ways of doing things, primarily for the reason that it is polluting/cluttering the namespace.
中使用的方法build_daily_history_table可能是最不优雅的做事方式之一，主要是因为它污染/混乱了命名空间。
回答by vlyalcin
To create specific tables, giving tablesparameter to create_all()method is enough.
要创建特定的表，给方法提供tables参数create_all()就足够了。
Base.metadata.create_all(engine, tables=table_objects)
table_objects equals to:
table_objects 等于：
table_objects = [HistoricDay.__table__]
or
或者
table_objects = [Base.metadata.tables["historicday"]]
I showed one table here. You can increase the number of the tables as you wish.
我在这里展示了一张桌子。您可以根据需要增加表的数量。
Reference: http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.MetaData.create_all
参考：http: //docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.MetaData.create_all
回答by W.Perrin
Create all not exists tables with one line. It will check whether the table exists first by default.
用一行创建所有不存在的表。默认情况下，它会首先检查表是否存在。
Base.metadata.create_all(db_engine, Base.metadata.tables.values(),checkfirst=True)
Create one target table with table_name.
创建一个目标表table_name。
Base.metadata.create_all(db_engine, Base.metadata.tables[table_name],checkfirst=True)
It works perfectly with declarative_base.
它与declarative_base.
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
TABLE_PREFIX = "decision_"
class Stock(Base):
__tablename__ = '{}stocks'.format(TABLE_PREFIX)
id = Column(Integer, primary_key=True)
name = Column(String)
class StagePerformance(Base):
__tablename__ = '{}stage_performance'.format(TABLE_PREFIX)
id = Column(Integer, primary_key=True)
date = Column(DateTime)
stock = relationship("Stock", back_populates="stage_performances")
period = Column(Integer )
open = Column(Float)
high = Column(Float)
low = Column(Float)
close = Column(Float)
change_ratio = Column(Float)
turnover = Column(Float)
volume = Column(Float)
