def __copy_schema(self):
"""
Copies the schema to dest db.
Copies all constraints in sqlite, only pk in mysql and postgres.
"""
o_engine = create_engine(self.o_engine_conn)
d_engine = create_engine(self.d_engine_conn)
metadata = MetaData()
metadata.reflect(o_engine)
insp = inspect(o_engine)
new_metadata_tables = {}
tables = filter(
lambda x: x[0] not in self.exclude, metadata.tables.items())
for table_name, table in tables:
# Keep everything for sqlite. SQLite cant alter table ADD CONSTRAINT.
# Only 1 simultaneous process can write to it.
# Keep only PKs for PostreSQL and MySQL.
# Restoring them after all data is copied.
keep_constraints = list(
filter(
lambda cons: isinstance(cons, PrimaryKeyConstraint),
table.constraints,
)
)
if d_engine.name == "sqlite":
uks = insp.get_unique_constraints(table_name)
for uk in uks:
uk_cols = filter(
lambda c: c.name in uk["column_names"], table._columns
)
keep_constraints.append(
UniqueConstraint(*uk_cols, name=uk["name"]))
for fk in filter(
lambda cons: isinstance(cons, ForeignKeyConstraint),
table.constraints,
):
keep_constraints.append(fk)
for cc in filter(
lambda cons: isinstance(
cons, CheckConstraint), table.constraints
):
cc.sqltext = TextClause(str(cc.sqltext).replace('"', ""))
keep_constraints.append(cc)
table.constraints = set(keep_constraints)
else:
table.constraints = set(keep_constraints)
table.indexes = set()
new_metadata_cols = ColumnCollection()
for col in table._columns:
col = self.__fix_column_type(col, d_engine.name)
col.autoincrement = False
new_metadata_cols.add(col)
table.columns = new_metadata_cols.as_immutable()
new_metadata_tables[table_name] = table
metadata.tables = immutabledict(new_metadata_tables)
metadata.create_all(d_engine)
"""
#%%
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
#%%
srcConnectString = 'oracle://*****:*****@scrdb'
desConnectString = 'oracle://*****:*****@desdb'
#%% 连接数据仓库
srcEngine = create_engine(srcConnectString, echo=True, convert_unicode=True)
srcSession = sessionmaker(bind=srcEngine)()
srcMetaData = MetaData(bind=srcEngine)
#%% 连接ODS
desEngine = create_engine(desConnectString, echo=True, convert_unicode=True)
desSession = sessionmaker(bind=desEngine)()
desMetaData = MetaData(bind=desEngine)
#%% 读取源数据表的结构
Table('duh0725_0', srcMetaData,autoload=True)
Table('duh0725_1', srcMetaData,autoload=True)
#%% 拷贝数据结构到目标schema
desMetaData.tables = srcMetaData.tables.copy()
#%% 创建目标数据结构
desMetaData.create_all(desEngine)
