def auto_discover_models_and_migrate() -> bool:
"""
Auto-migrates all sqlalchemy models that been marked for auto-migration database with the alias 'mara'.
Models are marked for auto-migration by being put into a module-level `MARA_AUTOMIGRATE_SQLALCHEMY_MODELS`
variable. E.g.
MARA_AUTOMIGRATE_SQLALCHEMY_MODELS = [MyModel]
For this, all modules that contain sqlalchemy models need to be loaded first
Returns:
True when no failure happened
"""
models = []
for name, module in copy.copy(sys.modules).items():
if 'MARA_AUTOMIGRATE_SQLALCHEMY_MODELS' in dir(module):
module_models = getattr(module,
'MARA_AUTOMIGRATE_SQLALCHEMY_MODELS')
if isinstance(module_models, typing.Callable):
module_models = module_models()
if isinstance(models, typing.Dict):
module_models = module_models.values()
assert (isinstance(module_models, typing.Iterable))
models += module_models
return auto_migrate(engine('mara'), models)
def session_context(alias: str) -> orm.Session:
"""Creates a context with a sql alchemy session for a database alias """
session = orm.sessionmaker(bind=engine(alias))()
try:
yield session
session.commit()
except:
session.rollback()
raise
finally:
session.close()
def __(alias: str, **_):
return engine(mara_db.dbs.db(alias))
import tempfile
import pathlib
with tempfile.TemporaryDirectory() as dir:
db = mara_db.dbs.SQLiteDB(file_name=pathlib.Path(dir) / 'test.sqlite')
# define a model / table
class MyTable(sqlalchemy.ext.declarative.declarative_base()):
__tablename__ = 'my_table'
my_table_id = sqlalchemy.Column(sqlalchemy.Integer,
primary_key=True)
column_1 = sqlalchemy.Column(sqlalchemy.TEXT,
nullable=False,
index=True)
auto_migrate(engine=engine(db), models=[MyTable])
# ->
# Created database "sqlite:////var/folders/gg/8117h7rj08zd9rpt55l315_1xx044y/T/tmpl_sdop4j/test.sqlite"
#
# CREATE TABLE my_table (
# my_table_id SERIAL NOT NULL,
# column_1 TEXT NOT NULL,
# PRIMARY KEY (my_table_id)
# );
#
# CREATE INDEX ix_my_table_column_1 ON my_table (column_1);
# remove index and add another column
class MyTable(sqlalchemy.ext.declarative.declarative_base()):
__tablename__ = 'my_table'
#!/usr/bin/env python
"""
Script to read csv files by chunk and insert to a database table
Uses pandas interpretation of the data type to create the table
"""
import pandas as pd
import sqlalchemy as sa
# Set up parameters
file_path =
conn = ""
db_connection = sa.engine(conn)
db_table_cible = "FCI_client" # Fichier Client Initial
chunk_size = 1000000
# Insert by chunk into the DB
chunk_cnt = 0
for chunk in pd.read_csv(file_path, chunksize=chunksize):
chunk.to_sql(db_table_cible,
con=db_connection,
if_exists="append",
method="multi")
chunk_cnt += 1
# Result output