def connect_database(self):
meta = MetaData()
if self.schema:
meta.schema = self.schema
self.Base = declarative_base(metadata=meta)
if '://' not in self.url and self.url.lower().endswith('.csv'):
# special case for csv file - just load into memory
self.engine = self.wrap_csv(self.url)
else:
try:
self.engine = create_engine(self.url, echo=self.verbose)
except ImportError as e:
print("Support library for this database not installed - {}".
format(e))
exit(1)
except ArgumentError:
if os.path.exists(self.url) or self.can_create:
try:
# maybe this is a local sqlite database?
sqlite_url = 'sqlite:///{}'.format(self.url)
self.engine = create_engine(sqlite_url,
echo=self.verbose)
self._full_url = sqlite_url
except ArgumentError:
# no joy, recreate the original problem and die.
self.engine = create_engine(self.url,
echo=self.verbose)
else:
raise
only = None
if self.tables:
only = list(self.tables)
self.Base.metadata.reflect(self.engine, only=only)
self._session = create_session(bind=self.engine)
class DatabaseNorm(Database):
@classmethod
def log_query(cls, message):
print(message)
async def execute(self,
query: typing.Union[ClauseElement, str],
values: dict = None) -> typing.Any:
self.log_query(query)
async with self.connection() as connection:
return await connection.execute(query, values)
async def fetch_all(self, query: typing.Union[ClauseElement, str], values: dict = None) -> \
typing.List[typing.Mapping]:
self.log_query(query)
return await super(DatabaseNorm, self).fetch_all(query=query,
values=values)
async def fetch_one(self, query: typing.Union[ClauseElement, str], values: dict = None) -> \
typing.Optional[typing.Mapping]:
self.log_query(query)
return await super(DatabaseNorm, self).fetch_one(query=query,
values=values)
database = DatabaseNorm(DATABASE_URL)
engine = create_engine(DATABASE_URL)
metadata = MetaData()
metadata.schema = DEFAULT_SCHEMA
metadata.create_all(engine)
import os
from sqlalchemy import create_engine, MetaData
from sqlalchemy import Table, Column
from sqlalchemy import Integer, String, DateTime, Float
from sqlalchemy.sql import and_, or_, not_
from sqlalchemy import func
from sqlalchemy.orm import sessionmaker
from contextlib import contextmanager
import config
engine = config.bbraun_bi_engine
metadata = MetaData(engine)
metadata.schema = config.META_SCHEMA
Session = sessionmaker(bind=engine)
# status_history_table = Table('status_history_table', metadata, autoload=True)
status_history_table_name = 'process_history'
status_history_table = Table(
status_history_table_name,
metadata,
Column('run_id', Integer),
Column('script_name', String(50)),
Column('load_name', String(50)),
Column('status', String(50)),
Column('description', String(50)),
Column('start_date', DateTime),
from sqlalchemy import MetaData, Table, create_engine, select
from utils import config
metadata = MetaData()
metadata.schema = config['db']['schema']
engine = create_engine(
"mssql+pymssql://%s:%s@%s:%s/%s" %
(config['db']['user'], config['db']['password'], config['db']['hostname'],
config['db']['port'], config['db']['database']))
# artist = Table('artist', metadata, autoload=True, autoload_with=engine)
# album = Table('album', metadata, autoload=True, autoload_with=engine)
# s = select([artist]).limit(10)
# print(engine.execute(s).fetchall())
#
# s = select([album]).limit(10)
# print(engine.execute(s).fetchall())
# print(metadata.tables)
# print(album.foreign_keys)
# print(str(artist.join(album)))
# ------------------------------------------------------------------------------
metadata.reflect(bind=engine)
# print(metadata.tables.keys())
artist = metadata.tables['practice.artist']
album = metadata.tables['practice.album']
