def drop_tables(names, session):
"""Takes a list of table names and drops them from the database if they exist."""
metadata = MetaData()
metadata.reflect(bind=session.bind)
for table in metadata.sorted_tables:
if table.name in names:
table.drop()
def reflect(engine, models, schema = None):
metadata = MetaData()
metadata.bind = engine
with warnings.catch_warnings():
warnings.simplefilter("ignore", category = SAWarning)
metadata.reflect(schema = schema, views = False)
if schema is not None:
tables = dict((table_name.replace(str(schema) + ".", ""), table)
for table_name, table in metadata.tables.iteritems())
else:
tables = metadata.tables
clear_mappers()
mappers = {}
for table_name, table in tables.iteritems():
modelname = "".join([word.capitalize() for word in table_name.split("_")])
try:
model = getattr(models, modelname)
except AttributeError:
stderr.write("Missing model for table %s\n" % table_name)
else:
mappers[modelname] = mapper(model, table)
Session = sessionmaker(bind = engine, autocommit = False, autoflush = True)
return mappers, tables, Session
def _sql_backend(request,engine,**kwargs):
meta = MetaData(engine)
meta.reflect()
meta.drop_all()
#we enable foreign key checks for SQLITE
if str(engine.url).startswith('sqlite://'):
engine.connect().execute('pragma foreign_keys=ON')
if not 'ondelete' in kwargs:
kwargs['ondelete'] = 'CASCADE'
backend = SqlBackend(engine = engine,**kwargs)
backend.init_schema()
backend.create_schema()
def finalizer():
backend.rollback()
del backend.connection
print("Dropping schema...")
#we disable foreign key checks for SQLITE (as dropping tables with circular foreign keys won't work otherwise...)
if str(engine.url).startswith('sqlite://'):
engine.connect().execute('pragma foreign_keys=OFF')
meta = MetaData(engine)
meta.reflect()
meta.drop_all()
print("Done...")
request.addfinalizer(finalizer)
return backend
def finalizer():
del backend.connection
print("Dropping schema...")
meta = MetaData(engine)
meta.reflect()
meta.drop_all()
print("Done...")
def setupPackage():
os.environ['MONGO_URI'] = 'mongodb://localhost'
os.environ['MONGO_DB_NAME'] = 'royal_example'
os.environ['MONGO_DB_PREFIX'] = ''
# sqla extentsion setup.
global engine
alembic_config = Config()
alembic_config.set_main_option('script_location',
'example/ext/sqla/db')
alembic_config.set_main_option('sqlalchemy.url', mysql_uri)
engine = create_engine(mysql_uri)
try:
command.downgrade(alembic_config, 'base')
except:
log.exception("Migration downgrade failed, clearing all tables")
metadata = MetaData(engine)
metadata.reflect()
for table in metadata.tables.values():
for fk in table.foreign_keys:
engine.execute(DropConstraint(fk.constraint))
metadata.drop_all()
command.upgrade(alembic_config, 'head')
def reflect_table(engine, klass):
"""Inspect and reflect objects"""
try:
meta = MetaData()
meta.reflect(bind=engine)
except OperationalError as e:
raise DatabaseError(error=e.orig.args[1], code=e.orig.args[0])
# Try to reflect from any of the supported tables
table = None
for tb in klass.tables():
if tb in meta.tables:
table = meta.tables[tb]
break
if table is None:
raise DatabaseError(error="Invalid schema. Table not found",
code="-1")
# Map table schema into klass
mapper(klass, table,
column_prefix=klass.column_prefix())
return table
def create_reflected_test_database(read_engine, write_engine):
meta = MetaData(bind=write_engine)
meta.reflect(bind=read_engine)
for tablename in ('matviews', 'stats_station_var'):
meta.remove(meta.tables[tablename])
logger.info("Overriding PG types that are unknown to sqlite")
meta.tables['meta_history'].columns['tz_offset'].type = Integer()
meta.tables['obs_raw'].columns['mod_time'].server_default = None
meta.tables['meta_history'].columns['the_geom'].type = Integer()
# These are all BIGINT in postgres
meta.tables['obs_raw'].columns['obs_raw_id'].type = Integer()
meta.tables['obs_raw_native_flags'].columns['obs_raw_id'].type = Integer()
meta.tables['obs_raw_pcic_flags'].columns['obs_raw_id'].type = Integer()
logger.info("Unsetting all of the sequence defaults")
for tablename, table in meta.tables.iteritems():
if hasattr(table, 'primary_key'):
for column in table.primary_key.columns.values():
if column.server_default:
column.server_default = None
logger.info("Creating a subset of the tables")
to_search = [
'obs_raw', 'meta_history', 'meta_station', 'meta_network', 'meta_vars',
'meta_contact'
]
to_create = [
table for tablename, table in meta.tables.iteritems()
if tablename in to_search
]
# Don't have contact in the postgres database yet 2013.12.04
meta.tables['meta_network'].append_column(Column('contact_id', Integer))
meta.create_all(tables=to_create)
def _clean_database(self):
log.msg("cleaning database %s" % self.db_url)
engine = sqlalchemy.create_engine(self.db_url)
meta = MetaData()
# there are some tables for which reflection sometimes fails, but since
# we're just dropping them, we don't need actual schema - a fake
# table will do the trick
for table in [ 'buildrequests', 'builds',
'buildset_properties', 'buildsets', 'change_properties',
'change_files', 'change_links',
'changes', 'patches', 'sourcestamp_changes', 'sourcestamps',
'scheduler_changes', 'scheduler_upstream_buildsets',
'schedulers' ]:
sqlalchemy.Table(table, meta,
sqlalchemy.Column('tmp', sqlalchemy.Integer))
# load any remaining tables
meta.reflect(bind=engine)
# and drop them, if they exist
meta.drop_all(bind=engine, checkfirst=True)
engine.dispose()
class Database:
def __init__(self, url, config=None):
self.url = url
self.engine = self.make_engine(url)
self.metadata = MetaData(bind=self.engine)
self.metadata.reflect()
self.config = config
# parallel table init
self.tables_lock = threading.Lock()
self.tables = {}
threads = []
for table in self.metadata.sorted_tables:
t = threading.Thread(target=self.make_dsm_table, args=(table,))
t.start()
threads.append(t)
[t.join() for t in threads]
def execute(self, qry):
try:
res = self.engine.execute(qry)
except Exception, e:
if e.message == "(OperationalError) (1205, 'Lock wait timeout exceeded; try restarting transaction')":
print e
res = self.execute(qry)
else:
print e
raise e
return res
def read_sql_table(table_name, con, index_col=None, coerce_float=True,
parse_dates=None, columns=None):
"""Read SQL database table into a DataFrame.
Given a table name and an SQLAlchemy engine, returns a DataFrame.
This function does not support DBAPI connections.
Parameters
----------
table_name : string
Name of SQL table in database
con : SQLAlchemy engine
Sqlite DBAPI connection mode not supported
index_col : string, optional
Column to set as index
coerce_float : boolean, default True
Attempt to convert values to non-string, non-numeric objects (like
decimal.Decimal) to floating point. Can result in loss of Precision.
parse_dates : list or dict
- List of column names to parse as dates
- Dict of ``{column_name: format string}`` where format string is
strftime compatible in case of parsing string times or is one of
(D, s, ns, ms, us) in case of parsing integer timestamps
- Dict of ``{column_name: arg dict}``, where the arg dict corresponds
to the keyword arguments of :func:`pandas.to_datetime`
Especially useful with databases without native Datetime support,
such as SQLite
columns : list
List of column names to select from sql table
Returns
-------
DataFrame
See also
--------
read_sql_query : Read SQL query into a DataFrame.
read_sql
"""
if not _is_sqlalchemy_engine(con):
raise NotImplementedError("read_sql_table only supported for "
"SQLAlchemy engines.")
import sqlalchemy
from sqlalchemy.schema import MetaData
meta = MetaData(con)
try:
meta.reflect(only=[table_name])
except sqlalchemy.exc.InvalidRequestError:
raise ValueError("Table %s not found" % table_name)
pandas_sql = PandasSQLAlchemy(con, meta=meta)
table = pandas_sql.read_table(
table_name, index_col=index_col, coerce_float=coerce_float,
parse_dates=parse_dates, columns=columns)
if table is not None:
return table
else:
raise ValueError("Table %s not found" % table_name, con)
def main():
parser = argparse.ArgumentParser(description='Generates SQLAlchemy model code from an existing database.')
parser.add_argument('url', nargs='?', help='SQLAlchemy url to the database')
parser.add_argument('--version', action='store_true', help="print the version number and exit")
parser.add_argument('--schema', help='load tables from an alternate schema')
parser.add_argument('--tables', help='tables to process (comma-separated, default: all)')
parser.add_argument('--noviews', action='store_true', help="ignore views")
parser.add_argument('--noindexes', action='store_true', help='ignore indexes')
parser.add_argument('--noconstraints', action='store_true', help='ignore constraints')
parser.add_argument('--nojoined', action='store_true', help="don't autodetect joined table inheritance")
parser.add_argument('--noinflect', action='store_true', help="don't try to convert tables names to singular form")
parser.add_argument('--noclasses', action='store_true', help="don't generate classes, only tables")
parser.add_argument('--alwaysclasses', action='store_true', help="always generate classes")
parser.add_argument('--nosequences', action='store_true', help="don't auto-generate postgresql sequences")
parser.add_argument('--outfile', help='file to write output to (default: stdout)')
args = parser.parse_args()
if args.version:
print(sqlacodegen.version)
return
if not args.url:
print('You must supply a url\n', file=sys.stderr)
parser.print_help()
return
engine = create_engine(args.url)
metadata = MetaData(engine)
tables = args.tables.split(',') if args.tables else None
metadata.reflect(engine, args.schema, not args.noviews, tables)
outfile = codecs.open(args.outfile, 'w', encoding='utf-8') if args.outfile else sys.stdout
generator = CodeGenerator(metadata, args.noindexes, args.noconstraints, args.nojoined, args.noinflect,
args.noclasses, args.alwaysclasses, args.nosequences)
generator.render(outfile)
def main(sqlalchemy_connection, show_type=False):
"""
The main function used to generate database schema
description in the dot language
"""
engine = create_engine(sqlalchemy_connection)
meta = MetaData()
meta.reflect(bind=engine)
env = Environment(
loader=FileSystemLoader(THIS_DIR),
trim_blocks=True,
lstrip_blocks=True)
node_template = env.get_template('node-template.html')
g = pgv.AGraph(strict=False,
directed=True,
name=engine.url.database,
graph_type='digraph',
compound='true',
rankdir='RL')
tables = meta.tables.values()
# tables = engine.table_names()
for table in tables:
if table.name.lower().startswith('sqlite_'):
continue
g.add_node(
table.name,
label=node_template.render(
title=table.name,
columns=table.columns.values(),
show_type=show_type),
shape='plaintext'
)
for table in tables:
for fk in table.foreign_keys:
if fk.column.table not in tables:
# print fk.column.table
continue
uniq_child = fk.parent.primary_key or fk.parent.unique
g.add_edge(
table.name,
fk.column.table.name,
tailport="{0}:w".format(fk.parent.name),
headport="{0}:e".format(fk.column.name +
('_type' if show_type else '')),
# headlabel="+ %s"%fk.column.name,
# taillabel='+ %s'%fk.parent.name,
# arrowhead='odottee' if fk.parent.nullable else 'teetee',
# dir='both',
arrowhead='none', dir='back',
arrowtail='teeodot' if uniq_child else 'crowodot'
# samehead=fk.column.name, sametail=fk.parent.name,
)
return g.to_string()
def __thd_clean_database(self, conn):
# drop the known tables
model.Model.metadata.drop_all(bind=conn, checkfirst=True)
# see if we can find any other tables to drop
meta = MetaData(bind=conn)
meta.reflect()
meta.drop_all()
def __init__(self, engine, meta=None):
self.engine = engine
if not meta:
from sqlalchemy.schema import MetaData
meta = MetaData(self.engine)
meta.reflect(self.engine)
self.meta = meta
def create_connection(url, schema_name, datasource_name=''):
__engine = create_engine(url)
__metadata = MetaData(schema=schema_name, bind=__engine)
__metadata.reflect(schema=schema_name, bind=__engine)
dbUtil = DbUtil(engine=__engine, metadata=__metadata)
provideUtility(dbUtil, IDbUtil, name=datasource_name)
def is_empty(self):
"""Check if the DB schema is empty.
An empty schema indicates a new uninitialised database."""
metadata = MetaData()
metadata.bind = sqlalchemy.create_engine(self._db_url)
metadata.reflect()
tables = metadata.tables
return not tables
def __thd_clean_database(self, conn):
# drop the known tables, although sometimes this misses dependencies
try:
model.Model.metadata.drop_all(bind=conn, checkfirst=True)
except sa.exc.ProgrammingError:
pass
# see if we can find any other tables to drop
meta = MetaData(bind=conn)
meta.reflect()
meta.drop_all()
def test_get_table_names(self, engine, connection):
meta = MetaData()
meta.reflect(bind=engine)
self.assertIn('one_row', meta.tables)
self.assertIn('one_row_complex', meta.tables)
insp = sqlalchemy.inspect(engine)
self.assertIn(
'dummy_table',
insp.get_table_names(schema='pyhive_test_database'),
)
def finalizer():
backend.rollback()
del backend.connection
print("Dropping schema...")
#we disable foreign key checks for SQLITE (as dropping tables with circular foreign keys won't work otherwise...)
if str(engine.url).startswith('sqlite://'):
engine.connect().execute('pragma foreign_keys=OFF')
meta = MetaData(engine)
meta.reflect()
meta.drop_all()
print("Done...")
def setup_clean_db(app):
# Clear out any existing tables
from subscity.models.base import DB
# db_name = os.environ.get('DN_NAME')
engine = DB.get_engine(app)
metadata = MetaData(engine)
metadata.reflect()
# for table in metadata.tables.values():
# for fk in table.foreign_keys:
# engine.execute(DropConstraint(fk.constraint))
metadata.drop_all()
apply_migrations()
def metadata_from_session(s):
"""
Args:
s: an SQLAlchemy :class:`Session`
Returns:
The metadata.
Get the metadata associated with the schema.
"""
meta = MetaData()
meta.reflect(bind=s.bind)
return meta
def __init__(self):
Base = automap_base()
engine = create_engine("mysql://{}:{}@{}/{}".format(user, password, host, dbname))
# reflect the tables
Base.prepare(engine, reflect=True)
metadata = MetaData()
metadata.reflect(bind=engine)
self.connection = engine.connect()
self.events = Table("events", metadata, autoload=True)
self.attributes = Table("attributes", metadata, autoload=True)
self.r = StrictRedis(unix_socket_path=redis_socket)
def initialize_db(config_uri, options={}):
setup_logging(config_uri)
settings = get_appsettings(config_uri, options=options)
engine = engine_from_config(settings, 'sqlalchemy.')
# delete all tables
meta = MetaData()
meta.reflect(bind=engine)
meta.drop_all(engine)
upgrade_db(config_uri)
def _init_tables(engine):
global _tables
logger.debug("Cleaning tables")
metadata = MetaData(bind=engine)
metadata.reflect()
logger.debug("drop all tables")
metadata.drop_all()
logger.debug("create all tables")
Base.metadata.create_all(bind=engine)
engine.dispose()
logger.debug("Tables cleaned")
metadata = MetaData(bind=engine)
metadata.reflect()
_tables = [table for table in metadata.tables.iterkeys()]
class DBProxyServer(BaseServer):
def __init__(self):
self.user = '******'
self.password = '******'
self.host = '127.0.0.1'
self.db = 'demo'
self.url = 'mysql://%s:%s@%s/%s' % (self.user, self.password, self.host, self.db)
self.engine = create_engine(self.url, echo=True, encoding='utf-8', pool_size=100, pool_recycle=3600)
self.meta = MetaData(self.engine)
self.meta.reflect()
server = RPCServer(12000)
server.add_avatar('admin', '123456', DBProxyAdminAvatar, (self,))
server.start()
def shutdown(self):
self.stop()
class Database(object):
def __init__(self, *args, **kw):
self.engine = create_engine(*args, **kw)
self.metadata = MetaData(bind=self.engine)
self.metadata.reflect(bind=self.engine)
Session = sessionmaker(bind=self.engine)
self.session = Session()
def __contains__(self, tablename):
return tablename in self.metadata.tables.keys()
def __getitem__(self, tablename):
return Table(self.metadata.tables[tablename], self.session)
def __setitem__(self, dn, node):
raise NotImplementedError()
def __delitem__(self, tablename):
table = self[tablename]
table._table.drop(self.engine)
self.metadata.remove(table._table)
def __iter__(self):
for tablename in self.metadata.tables:
yield tablename
def __len__(self):
return sum(1 for table in iter(self))
iterkeys = __iter__
def itervalues(self):
for table in self.metadata.tables.values():
yield Table(table, self.session)
def iteritems(self):
return ((table.name, table) for table in ValuesView(self))
def items(self):
return ItemsView(dictionary=self)
def keys(self):
return KeysView(dictionary=self)
def values(self):
return ValuesView(dictionary=self)
def get_options_from_database(config):
table_name = "%soptions" % config['wordpress']['prefix']
engine = create_engine(config['sqlalchemy']['engine'])
meta = MetaData()
meta.reflect(bind=engine)
options_table = meta.tables[table_name]
query = select([options_table.c.option_value],
options_table.c.option_name==config['wordpress']['option'])
connection = engine.connect()
result = connection.execute(query)
row = result.fetchone()
values = phpload(row[0])
return dict([(k.replace("dropbox_music_", ""), v)
for k,v in values.items()])
def _make_instance(mcs, class_, classdict):
metadata = classdict.get('__metadata__', None)
if not metadata:
connection = classdict.get('__connection__', None)
if not connection:
raise ValueError(
'Cube must have at least a __metadata__'
' or a __connection__ attribute')
metadata = MetaData(bind=create_engine(connection))
metadata.reflect()
fact_table = classdict.get('__fact_table__', None)
if fact_table is None:
raise ValueError(
'Cube must have a __fact_table__ attribute')
fact_table = table(fact_table, metadata)
dimensions = []
measures = []
for key in dir(class_):
value = getattr(class_, key)
if isinstance(value, pypet.Dimension):
value.name = key
dimensions.append(value)
elif isinstance(value, pypet.Measure):
value.name = key
expr = getattr(value, 'expression', None)
if expr is UNKNOWN_VALUE:
value.expression = key
if expr is not None:
value.expression = column(value.expression, fact_table)
measures.append(value)
dimensions = sorted(dimensions,
key=lambda x: getattr(x, '_count', float('inf')))
measures = sorted(measures,
key=lambda x: getattr(x, '_count', float('inf')))
cube = pypet.Cube(
metadata, fact_table, dimensions, measures,
aggregates=classdict.get('__aggregates__', None),
fact_count_column=column(
classdict.get('__fact_count_column__', None), fact_table))
for thing in dimensions + measures:
if not hasattr(cube, thing.name):
setattr(cube, thing.name, thing)
return cube
def open_db(self,engine,mode='open'):
"""open the database with engine URL. possible modes:
drop - drop own tables
dropall - drop all tables
create - create the tables
open - reflect the existing tables
"""
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection
from sqlalchemy.schema import MetaData
from sqlalchemy import event, Table
metadata = None
@event.listens_for(Table, "column_reflect")
def column_reflect(inspector, table, column_info):
if table.metadata is metadata:
if self.column_map and table.name in self.column_map:
column_info['key'] = self.column_map[table.name][column_info['name']]
else:
column_info['key'] = column_info['name']
convention = dict(
ix='ix_%(column_0_label)s',
uq="uq_%(table_name)s_%(column_0_name)s",
ck="ck_%(table_name)s_%(column_0_name)s",
fk="fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
pk="pk_%(table_name)s"
)
if mode=='dropall':
# Clear out any existing tables
metadata = MetaData(engine, naming_convention=convention)
metadata.reflect()
_drop_all(metadata)
return
metadata = MetaData(naming_convention=convention)
self.Base = declarative_base(bind=engine, cls=DeferredReflection, metadata=metadata)
metadata = self.Base.metadata
self.declare(reflect= mode=='open')
if mode=='drop':
_drop_all(metadata)
return
if mode=='create':
metadata.create_all(engine,checkfirst=False)
from sqlalchemy.orm import sessionmaker
self.Base.prepare(engine)
self.reflect_classes()
Session = sessionmaker(bind=engine)
self.session = Session()
def initialize_db(config_uri):
# setup_logging(config_uri)
# settings = get_appsettings(config_uri, options=options)
config = configparser.ConfigParser()
config.read(config_uri)
settings = dict(config.items("alembic"))
engine = engine_from_config(settings, 'sqlalchemy.')
# delete all tables
meta = MetaData()
meta.reflect(bind=engine)
meta.drop_all(engine)
upgrade_db(config_uri)
def db(app): # pylint: disable=redefined-outer-name, invalid-name
"""Return a session-wide initialised database.
Drops all existing tables - Meta follows Postgres FKs
"""
with app.app_context():
# Clear out any existing tables
metadata = MetaData(_db.engine)
metadata.reflect()
for table in metadata.tables.values():
for fk in table.foreign_keys: # pylint: disable=invalid-name
_db.engine.execute(DropConstraint(fk.constraint))
metadata.drop_all()
_db.drop_all()
sequence_sql = """SELECT sequence_name FROM information_schema.sequences
WHERE sequence_schema='public'
"""
sess = _db.session()
for seq in [name for (name,) in sess.execute(text(sequence_sql))]:
try:
sess.execute(text('DROP SEQUENCE public.%s ;' % seq))
print('DROP SEQUENCE public.%s ' % seq)
except Exception as err: # pylint: disable=broad-except
print(f'Error: {err}')
sess.commit()
# ############################################
# There are 2 approaches, an empty database, or the same one that the app will use
# create the tables
# _db.create_all()
# or
# Use Alembic to load all of the DB revisions including supporting lookup data
# This is the path we'll use in auth_api!!
# even though this isn't referenced directly, it sets up the internal configs that upgrade needs
Migrate(app, _db)
upgrade()
return _db
def import_schema(self, schema, srv_options, options,
restriction_type, restricts):
"""
Reflects the remote schema.
"""
metadata = MetaData()
url = _parse_url_from_options(srv_options)
engine = create_engine(url)
dialect = PGDialect()
if restriction_type == 'limit':
only = restricts
elif restriction_type == 'except':
only = lambda t, _: t not in restricts
else:
only = None
metadata.reflect(bind=engine,
schema=schema,
only=only)
to_import = []
for _, table in sorted(metadata.tables.items()):
ftable = TableDefinition(table.name)
ftable.options['schema'] = schema
ftable.options['tablename'] = table.name
for c in table.c:
# Force collation to None to prevent imcompatibilities
setattr(c.type, "collation", None)
# If the type is specialized, call the generic
# superclass method
if type(c.type) in CONVERSION_MAP:
class_name = CONVERSION_MAP[type(c.type)]
old_args = c.type.__dict__
c.type = class_name()
c.type.__dict__.update(old_args)
if c.primary_key:
ftable.options['primary_key'] = c.name
ftable.columns.append(ColumnDefinition(
c.name,
type_name=c.type.compile(dialect)))
to_import.append(ftable)
return to_import
def main():
parser = argparse.ArgumentParser(
description='Generates SQLAlchemy model code from an existing database.')
parser.add_argument('url', nargs='?', help='SQLAlchemy url to the database')
parser.add_argument('--version', action='store_true', help="print the version number and exit")
parser.add_argument('--schema', help='load tables from an alternate schema')
parser.add_argument('--tables', help='tables to process (comma-separated, default: all)')
parser.add_argument('--noviews', action='store_true', help="ignore views")
parser.add_argument('--noindexes', action='store_true', help='ignore indexes')
parser.add_argument('--noconstraints', action='store_true', help='ignore constraints')
parser.add_argument('--nojoined', action='store_true',
help="don't autodetect joined table inheritance")
parser.add_argument('--noinflect', action='store_true',
help="don't try to convert tables names to singular form")
parser.add_argument('--noclasses', action='store_true',
help="don't generate classes, only tables")
parser.add_argument('--outfile', help='file to write output to (default: stdout)')
args = parser.parse_args()
if args.version:
version = pkg_resources.get_distribution('sqlacodegen').parsed_version
print(version.public)
return
if not args.url:
print('You must supply a url\n', file=sys.stderr)
parser.print_help()
return
# Use reflection to fill in the metadata
engine = create_engine(args.url)
import_dialect_specificities(engine)
metadata = MetaData(engine)
tables = args.tables.split(',') if args.tables else None
metadata.reflect(engine, args.schema, not args.noviews, tables)
# Write the generated model code to the specified file or standard output
outfile = io.open(args.outfile, 'w', encoding='utf-8') if args.outfile else sys.stdout
generator = CodeGenerator(metadata, args.noindexes, args.noconstraints, args.nojoined,
args.noinflect, args.noclasses)
generator.render(outfile)
def ioc_mysql():
printv("Connection to mysql database")
Base = automap_base()
engine = create_engine('mysql://{}:{}@{}/{}'.format(
conf['mysql']['user'], conf['mysql']['password'],
conf['mysql']['host'], conf['mysql']['dbname']))
Base.prepare(engine, reflect=True)
metadata = MetaData()
metadata.reflect(bind=engine)
connection = engine.connect()
attributes_table = Table("attributes", metadata, autoload=True)
users_table = Table("users", metadata, autoload=True)
# MISP token must be the same as the authkey
printv("Check authentication key (token)")
query = select([users_table.c.authkey
]).where(users_table.c.email == conf['misp']['email'])
resp = connection.execute(query)
for authkey in resp:
if not conf['misp']['token'] == authkey[0]:
sys.exit(
"Your misp_token must be your authentication key. Please check your configuration file"
)
# Get all ids attributes
printv("Get Attributes")
attributes = connection.execute(select([attributes_table]))
for attr in attributes:
dic_attr = OrderedDict(attr.items())
if dic_attr['to_ids'] == 1:
timestamp = dic_attr['timestamp']
dic_attr['date'] = datetime.datetime.fromtimestamp(
int(timestamp)).strftime("%Y%m%d")
dic_attr['value'] = dic_attr['value1']
if (attr['value2']):
dic_attr[
'value'] = dic_attr['value'] + '|' + dic_attr['value2']
IOCs.append(dic_attr)
def __thd_clean_database(self, conn):
# drop the known tables, although sometimes this misses dependencies
try:
model.Model.metadata.drop_all(bind=conn, checkfirst=True)
except sa.exc.ProgrammingError:
pass
# see if we can find any other tables to drop
try:
meta = MetaData(bind=conn)
meta.reflect()
meta.drop_all()
except Exception:
# sometimes this goes badly wrong; being able to see the schema
# can be a big help
if conn.engine.dialect.name == 'sqlite':
r = conn.execute("select sql from sqlite_master "
"where type='table'")
log.msg("Current schema:")
for row in r.fetchall():
log.msg(row.sql)
raise
def init():
global engine, connection, translations
LOGGER.info('PostgreSQL at ' + POSTGRESQL_URL)
while True:
try:
engine = create_engine(POSTGRESQL_URL, poolclass=StaticPool)
connection = engine.connect()
meta = MetaData()
meta.reflect(bind=engine)
translations = meta.tables['translations']
break
except sqlalchemy.exc.OperationalError as e:
time.sleep(1.0)
print(e)
except sqlalchemy.exc.NoSuchTableError as e:
time.sleep(1.0)
print(e)
except Exception as e:
LOGGER.error(e)
print(e)
time.sleep(1.0)
def main():
parser = argparse.ArgumentParser(description='Generates SQLAlchemy model code from an existing database.')
parser.add_argument('url', nargs='?', help='SQLAlchemy url to the database')
parser.add_argument('--version', action='store_true', help="print the version number and exit")
parser.add_argument('--schema', help='load tables from an alternate schema')
parser.add_argument('--tables', help='tables to process (comma-separated, default: all)')
parser.add_argument('--noviews', action='store_true', help="ignore views")
parser.add_argument('--noindexes', action='store_true', help='ignore indexes')
parser.add_argument('--noconstraints', action='store_true', help='ignore constraints')
parser.add_argument('--nojoined', action='store_true', help="don't autodetect joined table inheritance")
parser.add_argument('--noinflect', action='store_true', help="don't try to convert tables names to singular form")
parser.add_argument('--noclasses', action='store_true', help="don't generate classes, only tables")
parser.add_argument('--outfile', help='file to write output to (default: stdout)')
parser.add_argument('--nobackrefs', action='store_true', help="don't include backrefs")
parser.add_argument('--flask', action='store_true', help="use Flask-SQLAlchemy columns")
parser.add_argument('--ignore-cols', help="Don't check foreign key constraints on specified columns (comma-separated)")
parser.add_argument('--nocomments', action='store_true', help="don't render column comments")
args = parser.parse_args()
if args.version:
print(sqlacodegen.version)
return
if not args.url:
print('You must supply a url\n', file=sys.stderr)
parser.print_help()
return
engine = create_engine(args.url)
import_dialect_specificities(engine)
metadata = MetaData(engine)
tables = args.tables.split(',') if args.tables else None
ignore_cols = args.ignore_cols.split(',') if args.ignore_cols else None
metadata.reflect(engine, args.schema, not args.noviews, tables)
outfile = codecs.open(args.outfile, 'w', encoding='utf-8') if args.outfile else sys.stdout
generator = CodeGenerator(metadata, args.noindexes, args.noconstraints,
args.nojoined, args.noinflect, args.nobackrefs,
args.flask, ignore_cols, args.noclasses, args.nocomments)
generator.render(outfile)
def check_colonne_in_table_bdd(bdd, schema_r, table_r,*colonnes) :
"""
verifier qu'une table d'une bdd contient les colonnes ciblees
in :
bdd : string : descriptions de la bdd, cf modules id_connexion
schema_r : string : le nom du schema supportant la table
table_r : le nom de la table
colonnes : le nom des colonnes devant etre dans la table, separe par une virgule
out :
flag : booleen : true si toute les colonnes sont das la table, False sinon
list_colonne_manquante : lisrte des colonnes qui manque
"""
with ct.ConnexionBdd(bdd) as c :
m=MetaData(bind=c.engine,schema=schema_r)
m.reflect()
inspector=inspect(c.engine)
for t in m.tables.keys() :
if t==f'{schema_r}.{table_r}' :
columns=[c['name'] for c in inspector.get_columns(table_r, schema=schema_r)]
if all([e in columns for e in colonnes]) :
return True,[]
else :
return False,[e for e in colonnes if e not in columns]
def do_cleardb(self, s):
'''
cleardb
Clears out the main database (not the settings db). This should only
be done while dofler is not running!
'''
from sqlalchemy.engine import reflection
from sqlalchemy.schema import (MetaData, Table, DropTable,
ForeignKeyConstraint, DropConstraint)
conn = db.engine.connect()
trans = conn.begin()
inspector = reflection.Inspector.from_engine(db.engine)
metadata = MetaData(db.engine)
metadata.reflect()
metadata.drop_all()
trans.commit()
conn.close()
vuln_db = '/opt/pvs/var/pvs/db/reports.db'
if os.path.exists(vuln_db):
os.system('service pvs stop')
os.remove(vuln_db)
os.system('service pvs start')
class Database():
def __init__(self, config, database_url, before_rev, after_rev):
self.before_rev = before_rev
self.after_rev = after_rev
self.alembic_cfg = Config(config)
self.alembic_cfg.set_main_option("sqlalchemy.url", database_url)
command.upgrade(self.alembic_cfg, before_rev)
self.engine = create_engine(
self.alembic_cfg.get_main_option("sqlalchemy.url"))
self.metadata = MetaData()
self.metadata.reflect(bind=self.engine)
def insert(self, data):
connection = self.engine.connect()
for table_name, table_data in data.items():
table = self.metadata.tables[table_name]
for entry in table_data:
for column in table.c:
if not column.nullable and column.name not in entry:
entry[column.name.split(".")[-1]] = generate_fake_data(
column.type)
print(entry)
connection.execute(table.insert().values(**entry))
connection.close()
def migrate(self):
command.upgrade(self.alembic_cfg, self.after_rev)
self.metadata = MetaData()
self.metadata.reflect(bind=self.engine)
def get_table(self, table_name):
return self.metadata.tables[table_name]
def connection(self):
return self.engine.connect()
def upgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
# Check affected tables present
if not engine.has_table('easaccount'):
return
meta = MetaData()
meta.reflect(bind=engine)
easuid = meta.tables['easuid']
# Check this migration hasn't run before
if 'folder_id' not in [c.name for c in easuid.columns]:
print 'This migration has been run, skipping.'
return
print 'Running migration'
conn = op.get_bind()
conn.execute('''ALTER TABLE easfoldersyncstatus
CHANGE name name varchar(191) NOT NULL''')
conn.execute('''ALTER TABLE easuid DROP COLUMN folder_id''')
conn.execute('''ALTER TABLE easfoldersyncstatus DROP COLUMN folder_id''')
def codegen(args):
# Use reflection to fill in the metadata
engine = create_engine(args.url)
metadata = MetaData(engine)
tables = args.tables.split(",") if args.tables else None
metadata.reflect(engine, args.schema, not args.noviews, tables)
if "sqlite" in args.url: # db.session.bind.dialect.name == "sqlite": FIXME review
# dirty hack for sqlite
engine.execute("""PRAGMA journal_mode = OFF""")
# Write the generated model code to the specified file or standard output
capture = StringIO()
# outfile = io.open(args.outfile, 'w', encoding='utf-8') if args.outfile else capture # sys.stdout
generator = CodeGenerator(metadata, args.noindexes, args.noconstraints, args.nojoined, args.noinflect, args.noclasses)
generator.render(capture)
generated = capture.getvalue()
generated = fix_generated(generated, args)
if args.outfile:
outfile = io.open(args.outfile, "w", encoding="utf-8")
outfile.write(generated)
return generated
def create_links_for_cluster_collection(session,
entity,
minimum_similarity=None):
"""
Main entry point to cluster creation
Assumes existence of similiarity and importance tables (via setup_company_similarities)
Creates 'entity_standardization' table to map members to standards
"""
metadata = MetaData(bind=session.get_bind())
similarity_table_name = SIMILAR_ENTITY_TABLE_MAP[entity]
importance_table_name = entity + '_importance'
link_table_name = entity + '_standardization'
initialize_link_table(session, link_table_name)
metadata.reflect()
[link_table
] = [x for x in metadata.sorted_tables if x.name == link_table_name]
clusters = bucket_by_trigram_signature(
session, similarity_table_name, minimum_similarity=minimum_similarity)
for cluster in clusters.values():
standard = select_standard_name(session, cluster,
importance_table_name)
link_members_to_standard(session, cluster, standard, link_table)
def test_get_columns_exclude_geo_point(self):
"""
SQLAlchemy: Test get_columns exclude geo point (odelasticsearch only)
"""
if self.driver_name == "elasticsearch":
return
metadata = MetaData()
metadata.reflect(bind=self.engine)
source_cols = [c.name for c in metadata.tables["data1"].c]
expected_columns = [
"field_array",
"field_array.keyword",
"field_boolean",
"field_float",
"field_nested.c1",
"field_nested.c1.keyword",
"field_nested.c2",
"field_number",
"field_str",
"field_str.keyword",
"timestamp",
]
self.assertEqual(source_cols,
self.make_columns_compliant(expected_columns))
def reflect_table(engine, klass):
"""Inspect and reflect objects"""
try:
meta = MetaData()
meta.reflect(bind=engine)
except OperationalError as e:
raise DatabaseError(error=e.orig.args[1], code=e.orig.args[0])
# Try to reflect from any of the supported tables
table = None
for tb in klass.tables():
if tb in meta.tables:
table = meta.tables[tb]
break
if table is None:
raise DatabaseError(error="Invalid schema. Table not found", code="-1")
# Map table schema into klass
mapper(klass, table, column_prefix=klass.column_prefix())
return table
# -*- coding: utf-8 -*-
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import RelationshipProperty
from sqlalchemy import inspect
from models import db, Models, app
from faker import Faker
from random import randint
from sqlalchemy.schema import MetaData
import re
arglist = {}
meta = MetaData()
meta.reflect(bind=db.engine)
processed = []
# def is_foreign_key(table_name, col_name):
# return table_name+"."+col_name in [e.target_fullname for e in meta.tables[table_name].foreign_keys]
def add_model_instance(model_class, amount=10, parent=None):
# print(model_class.__name__)
with app.app_context():
insp = inspect(model_class)
insp = inspect(model_class)
children = []
child = {}
has_parent = False
class Database(object):
def __init__(self,
url,
schema=None,
reflect_metadata=True,
engine_kwargs=None,
reflect_views=True,
row_type=row_type):
if engine_kwargs is None:
engine_kwargs = {}
self.lock = threading.RLock()
self.local = threading.local()
if '?' in url:
url, query = url.split('?', 1)
query = parse_qs(query)
if schema is None:
# le pop
schema_qs = query.pop('schema', query.pop('searchpath', []))
if len(schema_qs):
schema = schema_qs.pop()
if len(query):
url = url + '?' + urlencode(query, doseq=True)
self.schema = schema
self.engine = create_engine(url, **engine_kwargs)
self.url = url
self.metadata = MetaData(schema=schema)
self.metadata.bind = self.engine
if reflect_metadata:
self.metadata.reflect(self.engine, views=reflect_views)
self.row_type = row_type
self._tables = {}
@property
def executable(self):
""" The current connection or engine against which statements
will be executed. """
if hasattr(self.local, 'connection'):
return self.local.connection
return self.engine
@property
def op(self):
ctx = MigrationContext.configure(self.engine)
return Operations(ctx)
def _acquire(self):
self.lock.acquire()
def _release(self):
if not hasattr(self.local, 'tx'):
self.lock.release()
self.local.must_release = False
else:
self.local.must_release = True
def _release_internal(self):
if getattr(self.local, 'must_release', None):
self.lock.release()
self.local.must_release = False
def _dispose_transaction(self):
self.local.tx.remove(self.local.tx[-1])
if not self.local.tx:
del self.local.tx
self.local.connection.close()
del self.local.connection
self._release_internal()
def begin(self):
""" Enter a transaction explicitly. No data will be written
until the transaction has been committed.
**NOTICE:** Schema modification operations, such as the creation
of tables or columns will not be part of the transactional context."""
if not hasattr(self.local, 'connection'):
self.local.connection = self.engine.connect()
if not hasattr(self.local, 'tx'):
self.local.tx = []
self.local.tx.append(self.local.connection.begin())
def commit(self):
""" Commit the current transaction, making all statements executed
since the transaction was begun permanent. """
if hasattr(self.local, 'tx') and self.local.tx:
self.local.tx[-1].commit()
self._dispose_transaction()
def rollback(self):
""" Roll back the current transaction, discarding all statements
executed since the transaction was begun. """
if hasattr(self.local, 'tx') and self.local.tx:
self.local.tx[-1].rollback()
self._dispose_transaction()
def __enter__(self):
self.begin()
return self
def __exit__(self, error_type, error_value, traceback):
if error_type is None:
try:
self.commit()
except:
with safe_reraise():
self.rollback()
else:
self.rollback()
@property
def tables(self):
"""
Get a listing of all tables that exist in the database.
"""
return list(
set(self.metadata.tables.keys()) | set(self._tables.keys()))
def __contains__(self, member):
return member in self.tables
def _valid_table_name(self, table_name):
""" Check if the table name is obviously invalid. """
if table_name is None or not len(table_name.strip()):
raise ValueError("Invalid table name: %r" % table_name)
return table_name.strip()
def create_table(self,
table_name,
primary_id='id',
primary_type='Integer'):
"""
Creates a new table. The new table will automatically have an `id` column
unless specified via optional parameter primary_id, which will be used
as the primary key of the table. Automatic id is set to be an
auto-incrementing integer, while the type of custom primary_id can be a
String or an Integer as specified with primary_type flag. The default
length of String is 255. The caller can specify the length.
The caller will be responsible for the uniqueness of manual primary_id.
This custom id feature is only available via direct create_table call.
Returns a :py:class:`Table <dataset.Table>` instance.
::
table = db.create_table('population')
# custom id and type
table2 = db.create_table('population2', 'age')
table3 = db.create_table('population3', primary_id='race', primary_type='String')
# custom length of String
table4 = db.create_table('population4', primary_id='race', primary_type='String(50)')
"""
table_name = self._valid_table_name(table_name)
self._acquire()
try:
log.debug("Creating table: %s on %r" % (table_name, self.engine))
match = re.match(r'^(Integer)$|^(String)(\(\d+\))?$', primary_type)
if match:
if match.group(1) == 'Integer':
auto_flag = False
if primary_id == 'id':
auto_flag = True
col = Column(primary_id,
Integer,
primary_key=True,
autoincrement=auto_flag)
elif not match.group(3):
col = Column(primary_id, String(255), primary_key=True)
else:
len_string = int(match.group(3)[1:-1])
len_string = min(len_string, 255)
col = Column(primary_id,
String(len_string),
primary_key=True)
else:
raise DatasetException(
"The primary_type has to be either 'Integer' or 'String'.")
table = SQLATable(table_name, self.metadata, schema=self.schema)
table.append_column(col)
table.create(self.engine)
self._tables[table_name] = table
return Table(self, table)
finally:
self._release()
def load_table(self, table_name):
"""
Loads a table. This will fail if the tables does not already
exist in the database. If the table exists, its columns will be
reflected and are available on the :py:class:`Table <dataset.Table>`
object.
Returns a :py:class:`Table <dataset.Table>` instance.
::
table = db.load_table('population')
"""
table_name = self._valid_table_name(table_name)
self._acquire()
try:
log.debug("Loading table: %s on %r" % (table_name, self))
table = SQLATable(table_name,
self.metadata,
schema=self.schema,
autoload=True)
self._tables[table_name] = table
return Table(self, table)
finally:
self._release()
def update_table(self, table_name):
table_name = self._valid_table_name(table_name)
self.metadata = MetaData(schema=self.schema)
self.metadata.bind = self.engine
self.metadata.reflect(self.engine)
self._tables[table_name] = SQLATable(table_name,
self.metadata,
schema=self.schema)
return self._tables[table_name]
def get_table(self, table_name, primary_id='id', primary_type='Integer'):
"""
Smart wrapper around *load_table* and *create_table*. Either loads a table
or creates it if it doesn't exist yet.
For short-hand to create a table with custom id and type using [], where
table_name, primary_id, and primary_type are specified as a tuple
Returns a :py:class:`Table <dataset.Table>` instance.
::
table = db.get_table('population')
# you can also use the short-hand syntax:
table = db['population']
"""
if table_name in self._tables:
return Table(self, self._tables[table_name])
self._acquire()
try:
if self.engine.has_table(table_name, schema=self.schema):
return self.load_table(table_name)
else:
return self.create_table(table_name, primary_id, primary_type)
finally:
self._release()
def __getitem__(self, table_name):
return self.get_table(table_name)
def query(self, query, **kw):
"""
Run a statement on the database directly, allowing for the
execution of arbitrary read/write queries. A query can either be
a plain text string, or a `SQLAlchemy expression <http://docs.sqlalchemy.org/en/latest/core/tutorial.html#selecting>`_.
If a plain string is passed in, it will be converted to an expression automatically.
Keyword arguments will be used for parameter binding. See the `SQLAlchemy
documentation <http://docs.sqlalchemy.org/en/rel_0_9/core/connections.html#sqlalchemy.engine.Connection.execute>`_ for details.
The returned iterator will yield each result sequentially.
::
res = db.query('SELECT user, COUNT(*) c FROM photos GROUP BY user')
for row in res:
print(row['user'], row['c'])
"""
if isinstance(query, six.string_types):
query = text(query)
return ResultIter(self.executable.execute(query, **kw),
row_type=self.row_type)
def __repr__(self):
return '<Database(%s)>' % self.url
class HGECtx:
def __init__(self, hge_url, pg_url, config):
self.http = requests.Session()
self.hge_key = config.getoption('--hge-key')
self.hge_url = hge_url
self.pg_url = pg_url
self.hge_webhook = config.getoption('--hge-webhook')
hge_jwt_key_file = config.getoption('--hge-jwt-key-file')
if hge_jwt_key_file is None:
self.hge_jwt_key = None
else:
with open(hge_jwt_key_file) as f:
self.hge_jwt_key = f.read()
self.hge_jwt_conf = config.getoption('--hge-jwt-conf')
if self.hge_jwt_conf is not None:
self.hge_jwt_conf_dict = json.loads(self.hge_jwt_conf)
self.webhook_insecure = config.getoption('--test-webhook-insecure')
self.metadata_disabled = config.getoption('--test-metadata-disabled')
self.may_skip_test_teardown = False
self.function_permissions = config.getoption(
'--test-function-permissions')
self.engine = create_engine(self.pg_url)
self.meta = MetaData()
self.ws_read_cookie = config.getoption('--test-ws-init-cookie')
self.hge_scale_url = config.getoption('--test-hge-scale-url')
self.avoid_err_msg_checks = config.getoption(
'--avoid-error-message-checks')
self.inherited_roles_tests = config.getoption('--test-inherited-roles')
self.ws_client = GQLWsClient(self, '/v1/graphql')
self.backend = config.getoption('--backend')
# HGE version
result = subprocess.run(['../../scripts/get-version.sh'],
shell=False,
stdout=subprocess.PIPE,
check=True)
env_version = os.getenv('VERSION')
self.version = env_version if env_version else result.stdout.decode(
'utf-8').strip()
if not self.metadata_disabled and not config.getoption(
'--skip-schema-setup'):
try:
st_code, resp = self.v2q_f("queries/" +
self.backend_suffix("clear_db") +
".yaml")
except requests.exceptions.RequestException as e:
self.teardown()
raise HGECtxError(repr(e))
assert st_code == 200, resp
# Postgres version
if self.backend == 'postgres':
pg_version_text = self.sql(
'show server_version_num').fetchone()['server_version_num']
self.pg_version = int(pg_version_text)
def reflect_tables(self):
self.meta.reflect(bind=self.engine)
def anyq(self, u, q, h, b=None, v=None):
resp = None
if v == 'GET':
resp = self.http.get(self.hge_url + u, headers=h)
elif v == 'POST' and b:
# TODO: Figure out why the requests are failing with a byte object passed in as `data`
resp = self.http.post(self.hge_url + u, data=b, headers=h)
elif v == 'PATCH' and b:
resp = self.http.patch(self.hge_url + u, data=b, headers=h)
elif v == 'PUT' and b:
resp = self.http.put(self.hge_url + u, data=b, headers=h)
elif v == 'DELETE':
resp = self.http.delete(self.hge_url + u, headers=h)
else:
resp = self.http.post(self.hge_url + u, json=q, headers=h)
# NOTE: make sure we preserve key ordering so we can test the ordering
# properties in the graphql spec properly
# Returning response headers to get the request id from response
return resp.status_code, resp.json(
object_pairs_hook=OrderedDict), resp.headers
def sql(self, q):
conn = self.engine.connect()
res = conn.execute(q)
conn.close()
return res
def execute_query(self, q, url_path, headers={}):
h = headers.copy()
if self.hge_key is not None:
h['X-Hasura-Admin-Secret'] = self.hge_key
resp = self.http.post(self.hge_url + url_path, json=q, headers=h)
# NOTE: make sure we preserve key ordering so we can test the ordering
# properties in the graphql spec properly
return resp.status_code, resp.json(object_pairs_hook=OrderedDict)
def v1q(self, q, headers={}):
return self.execute_query(q, "/v1/query", headers)
def v1q_f(self, fn):
with open(fn) as f:
# NOTE: preserve ordering with ruamel
yml = yaml.YAML()
return self.v1q(yml.load(f))
def v2q(self, q, headers={}):
return self.execute_query(q, "/v2/query", headers)
def v2q_f(self, fn):
with open(fn) as f:
# NOTE: preserve ordering with ruamel
yml = yaml.YAML()
return self.v2q(yml.load(f))
def backend_suffix(self, filename):
if self.backend == 'postgres':
return filename
else:
return filename + "_" + self.backend
def v1metadataq(self, q, headers={}):
return self.execute_query(q, "/v1/metadata", headers)
def v1metadataq_f(self, fn):
with open(fn) as f:
# NOTE: preserve ordering with ruamel
yml = yaml.YAML()
return self.v1metadataq(yml.load(f))
def teardown(self):
self.http.close()
self.engine.dispose()
def read_sql_table(table_name,
con,
schema=None,
column_dtypes=None,
coerce_float=True,
parse_dates=None,
columns=None,
chunksize=None):
"""Read SQL database table into an afw table.
Given a table name and an SQLAlchemy connectable, returns an Table.
This function does not support DBAPI connections.
Parameters
----------
table_name : string
Name of SQL table in database
con : SQLAlchemy connectable (or database string URI)
schema : string, default: None
Name of SQL schema in database to query (if database flavor
supports this). If None, use default schema (default).
column_dtypes : dict, default: None
Dict of ``{column_name: dtype}`` where dtype is the target
dtype of the column. This is used to harmonize types and downcast
from doubles and longs.
coerce_float : boolean, default True
Attempt to convert values of non-string, non-numeric objects (like
decimal.Decimal) to floating point. Can result in loss of Precision.
parse_dates : list or dict, default: None
- List of column names to parse as dates
- Dict of ``{column_name: format string}`` where format string is
strftime compatible in case of parsing string times or is one of
(D, s, ns, ms, us) in case of parsing integer timestamps
columns : list, default: None
List of column names to select from sql table
chunksize : int, default: None
If specified, return an iterator where ``chunksize`` is the number
of rows to include in each chunk.
Returns
-------
afw table
Notes
-----
Any datetime values with time zone information will be converted to UTC
See also
--------
read_sql_query : Read SQL query into a afw table.
read_sql
"""
con = _engine_builder(con)
meta = MetaData(con, schema=schema)
try:
meta.reflect(only=[table_name], views=True)
except sqlalchemy.exc.InvalidRequestError:
raise ValueError("Table %s not found" % table_name)
sql_io = SQLDatabase(con, meta=meta)
table = sql_io.read_table(table_name,
column_dtypes=column_dtypes,
coerce_float=coerce_float,
parse_dates=parse_dates,
columns=columns,
chunksize=chunksize)
if table is not None:
return table
else:
raise ValueError("Table %s not found" % table_name, con)
class HGECtx:
def __init__(self, hge_url, pg_url):
server_address = ('0.0.0.0', 5592)
self.resp_queue = queue.Queue(maxsize=1)
self.error_queue = queue.Queue()
self.ws_queue = queue.Queue(maxsize=-1)
self.httpd = WebhookServer(self.resp_queue, self.error_queue,
server_address)
self.web_server = threading.Thread(target=self.httpd.serve_forever)
self.web_server.start()
self.pg_url = pg_url
self.engine = create_engine(self.pg_url)
self.meta = MetaData()
self.http = requests.Session()
self.hge_url = hge_url
self.ws_url = urlparse(hge_url)
self.ws_url = self.ws_url._replace(scheme='ws')
self.ws_url = self.ws_url._replace(path='/v1alpha1/graphql')
self.ws = websocket.WebSocketApp(self.ws_url.geturl(),
on_message=self._on_message)
self.wst = threading.Thread(target=self.ws.run_forever)
self.wst.daemon = True
self.wst.start()
result = subprocess.run(['../../scripts/get-version.sh'],
shell=True,
stdout=subprocess.PIPE,
check=True)
self.version = result.stdout.decode('utf-8').strip()
try:
st_code, resp = self.v1q_f('queries/clear_db.yaml')
except requests.exceptions.RequestException as e:
self.teardown()
raise HGECtxError(repr(e))
assert st_code == 200, resp
def _on_message(self, message):
my_json = json.loads(message)
if my_json['type'] != 'ka':
self.ws_queue.put(message)
def get_event(self, timeout):
return self.resp_queue.get(timeout=timeout)
def get_error_queue_size(self):
sz = 0
while not self.error_queue.empty():
self.error_queue.get()
sz = sz + 1
return sz
def get_ws_event(self, timeout):
return json.loads(self.ws_queue.get(timeout=timeout))
def reflect_tables(self):
self.meta.reflect(bind=self.engine)
def anyq(self, u, q, h):
resp = self.http.post(self.hge_url + u, json=q, headers=h)
return resp.status_code, resp.json()
def v1q(self, q):
resp = self.http.post(self.hge_url + "/v1/query", json=q)
return resp.status_code, resp.json()
def v1q_f(self, fn):
with open(fn) as f:
return self.v1q(yaml.load(f))
def teardown(self):
self.http.close()
self.engine.dispose()
self.httpd.shutdown()
self.httpd.server_close()
self.ws.close()
self.web_server.join()
self.wst.join()
class HGECtx:
def __init__(self, hge_url, pg_url, hge_key, hge_webhook, webhook_insecure,
hge_jwt_key_file, hge_jwt_conf, metadata_disabled):
server_address = ('0.0.0.0', 5592)
self.resp_queue = queue.Queue(maxsize=1)
self.error_queue = queue.Queue()
self.ws_queue = queue.Queue(maxsize=-1)
self.httpd = WebhookServer(self.resp_queue, self.error_queue,
server_address)
self.web_server = threading.Thread(target=self.httpd.serve_forever)
self.web_server.start()
self.pg_url = pg_url
self.engine = create_engine(self.pg_url)
self.meta = MetaData()
self.http = requests.Session()
self.hge_url = hge_url
self.hge_key = hge_key
self.hge_webhook = hge_webhook
if hge_jwt_key_file is None:
self.hge_jwt_key = None
else:
with open(hge_jwt_key_file) as f:
self.hge_jwt_key = f.read()
self.hge_jwt_conf = hge_jwt_conf
self.webhook_insecure = webhook_insecure
self.metadata_disabled = metadata_disabled
self.may_skip_test_teardown = False
self.ws_url = urlparse(hge_url)
self.ws_url = self.ws_url._replace(scheme='ws')
self.ws_url = self.ws_url._replace(path='/v1alpha1/graphql')
self.ws = websocket.WebSocketApp(self.ws_url.geturl(),
on_message=self._on_message)
self.wst = threading.Thread(target=self.ws.run_forever)
self.wst.daemon = True
self.wst.start()
# start the graphql server
self.graphql_server = graphql_server.create_server('127.0.0.1', 5000)
self.gql_srvr_thread = threading.Thread(
target=self.graphql_server.serve_forever)
self.gql_srvr_thread.start()
result = subprocess.run(['../../scripts/get-version.sh'],
shell=False,
stdout=subprocess.PIPE,
check=True)
self.version = result.stdout.decode('utf-8').strip()
if not self.metadata_disabled:
try:
st_code, resp = self.v1q_f('queries/clear_db.yaml')
except requests.exceptions.RequestException as e:
self.teardown()
raise HGECtxError(repr(e))
assert st_code == 200, resp
def _on_message(self, message):
my_json = json.loads(message)
if my_json['type'] != 'ka':
self.ws_queue.put(message)
def get_event(self, timeout):
return self.resp_queue.get(timeout=timeout)
def get_error_queue_size(self):
sz = 0
while not self.error_queue.empty():
self.error_queue.get()
sz = sz + 1
return sz
def get_ws_event(self, timeout):
return json.loads(self.ws_queue.get(timeout=timeout))
def reflect_tables(self):
self.meta.reflect(bind=self.engine)
def anyq(self, u, q, h):
resp = self.http.post(self.hge_url + u, json=q, headers=h)
return resp.status_code, resp.json()
def sql(self, q):
conn = self.engine.connect()
res = conn.execute(q)
conn.close()
return res
def v1q(self, q, headers={}):
h = headers.copy()
if self.hge_key is not None:
h['X-Hasura-Admin-Secret'] = self.hge_key
resp = self.http.post(self.hge_url + "/v1/query", json=q, headers=h)
return resp.status_code, resp.json()
def v1q_f(self, fn):
with open(fn) as f:
return self.v1q(yaml.load(f))
def teardown(self):
self.http.close()
self.engine.dispose()
self.httpd.shutdown()
self.httpd.server_close()
self.ws.close()
self.web_server.join()
self.wst.join()
graphql_server.stop_server(self.graphql_server)
self.gql_srvr_thread.join()
def main() -> None:
generators = {
ep.name: ep
for ep in entry_points()['sqlacodegen.generators']
}
parser = argparse.ArgumentParser(
description='Generates SQLAlchemy model code from an existing database.'
)
parser.add_argument('url',
nargs='?',
help='SQLAlchemy url to the database')
parser.add_argument('--option',
nargs='*',
help="options passed to the generator class")
parser.add_argument('--version',
action='store_true',
help="print the version number and exit")
parser.add_argument(
'--schemas',
help='load tables from the given schemas (comma separated)')
parser.add_argument('--generator',
choices=generators,
default='declarative',
help="generator class to use")
parser.add_argument(
'--tables', help='tables to process (comma-separated, default: all)')
parser.add_argument('--noviews', action='store_true', help="ignore views")
parser.add_argument('--outfile',
help='file to write output to (default: stdout)')
args = parser.parse_args()
if args.version:
print(version('sqlacodegen'))
return
if not args.url:
print('You must supply a url\n', file=sys.stderr)
parser.print_help()
return
# Use reflection to fill in the metadata
engine = create_engine(args.url)
metadata = MetaData()
tables = args.tables.split(',') if args.tables else None
schemas = args.schemas.split(',') if args.schemas else [None]
for schema in schemas:
metadata.reflect(engine, schema, not args.noviews, tables)
# Instantiate the generator
generator_class = generators[args.generator].load()
generator = generator_class(metadata, engine, set(args.option or ()))
# Open the target file (if given)
with ExitStack() as stack:
if args.outfile:
outfile = open(args.outfile, 'w', encoding='utf-8')
stack.enter_context(outfile)
else:
outfile = sys.stdout
# Write the generated model code to the specified file or standard output
outfile.write(generator.generate())
def inspect_db(self):
metadata = MetaData()
metadata.reflect(bind=self.db.engine)
return metadata
def __thd_clean_database(self, conn):
# In general it's nearly impossible to do "bullet proof" database
# cleanup with SQLAlchemy that will work on a range of databases
# and they configurations.
#
# Following approaches were considered.
#
# 1. Drop Buildbot Model schema:
#
# model.Model.metadata.drop_all(bind=conn, checkfirst=True)
#
# Dropping schema from model is correct and working operation only
# if database schema is exactly corresponds to the model schema.
#
# If it is not (e.g. migration script failed or migration results in
# old version of model), then some tables outside model schema may be
# present, which may reference tables in the model schema.
# In this case either dropping model schema will fail (if database
# enforces referential integrity, e.g. PostgreSQL), or
# dropping left tables in the code below will fail (if database allows
# removing of tables on which other tables have references,
# e.g. SQLite).
#
# 2. Introspect database contents and drop found tables.
#
# meta = MetaData(bind=conn)
# meta.reflect()
# meta.drop_all()
#
# May fail if schema contains reference cycles (and Buildbot schema
# has them). Reflection looses metadata about how reference cycles
# can be teared up (e.g. use_alter=True).
# Introspection may fail if schema has invalid references
# (e.g. possible in SQLite).
#
# 3. What is actually needed here is accurate code for each engine
# and each engine configuration that will drop all tables,
# indexes, constraints, etc in proper order or in a proper way
# (using tables alternation, or DROP TABLE ... CASCADE, etc).
#
# Conclusion: use approach 2 with manually teared apart known
# reference cycles.
# pylint: disable=too-many-nested-blocks
try:
meta = MetaData(bind=conn)
# Reflect database contents. May fail, e.g. if table references
# non-existent table in SQLite.
meta.reflect()
# Table.foreign_key_constraints introduced in SQLAlchemy 1.0.
if sa_version()[:2] >= (1, 0):
# Restore `use_alter` settings to break known reference cycles.
# Main goal of this part is to remove SQLAlchemy warning
# about reference cycle.
# Looks like it's OK to do it only with SQLAlchemy >= 1.0.0,
# since it's not issued in SQLAlchemy == 0.8.0
# List of reference links (table_name, ref_table_name) that
# should be broken by adding use_alter=True.
table_referenced_table_links = [('buildsets', 'builds'),
('builds', 'buildrequests')]
for table_name, ref_table_name in table_referenced_table_links:
if table_name in meta.tables:
table = meta.tables[table_name]
for fkc in table.foreign_key_constraints:
if fkc.referred_table.name == ref_table_name:
fkc.use_alter = True
# Drop all reflected tables and indices. May fail, e.g. if
# SQLAlchemy wouldn't be able to break circular references.
# Sqlalchemy fk support with sqlite is not yet perfect, so we must deactivate fk during that
# operation, even though we made our possible to use use_alter
with withoutSqliteForeignKeys(conn.engine, conn):
meta.drop_all()
except Exception:
# sometimes this goes badly wrong; being able to see the schema
# can be a big help
if conn.engine.dialect.name == 'sqlite':
r = conn.execute("select sql from sqlite_master "
"where type='table'")
log.msg("Current schema:")
for row in r.fetchall():
log.msg(row.sql)
raise
class HGECtx:
def __init__(self, hge_url, pg_url, hge_key, hge_webhook, webhook_insecure,
hge_jwt_key_file, hge_jwt_conf, metadata_disabled, ws_read_cookie, hge_scale_url):
self.http = requests.Session()
self.hge_key = hge_key
self.hge_url = hge_url
self.pg_url = pg_url
self.hge_webhook = hge_webhook
if hge_jwt_key_file is None:
self.hge_jwt_key = None
else:
with open(hge_jwt_key_file) as f:
self.hge_jwt_key = f.read()
self.hge_jwt_conf = hge_jwt_conf
self.webhook_insecure = webhook_insecure
self.metadata_disabled = metadata_disabled
self.may_skip_test_teardown = False
self.engine = create_engine(self.pg_url)
self.meta = MetaData()
self.ws_read_cookie = ws_read_cookie
self.hge_scale_url = hge_scale_url
self.ws_client = GQLWsClient(self, '/v1/graphql')
result = subprocess.run(['../../scripts/get-version.sh'], shell=False, stdout=subprocess.PIPE, check=True)
self.version = result.stdout.decode('utf-8').strip()
if not self.metadata_disabled:
try:
st_code, resp = self.v1q_f('queries/clear_db.yaml')
except requests.exceptions.RequestException as e:
self.teardown()
raise HGECtxError(repr(e))
assert st_code == 200, resp
def reflect_tables(self):
self.meta.reflect(bind=self.engine)
def anyq(self, u, q, h):
resp = self.http.post(
self.hge_url + u,
json=q,
headers=h
)
return resp.status_code, resp.json()
def sql(self, q):
conn = self.engine.connect()
res = conn.execute(q)
conn.close()
return res
def v1q(self, q, headers = {}):
h = headers.copy()
if self.hge_key is not None:
h['X-Hasura-Admin-Secret'] = self.hge_key
resp = self.http.post(
self.hge_url + "/v1/query",
json=q,
headers=h
)
return resp.status_code, resp.json()
def v1q_f(self, fn):
with open(fn) as f:
return self.v1q(yaml.safe_load(f))
def teardown(self):
self.http.close()
self.engine.dispose()
def _get_reflected_tabname_to_colnames(self):
reflected_meta = MetaData()
with self.suppressed_stderr(
): # Let's get rid of irrelevant warnings...
reflected_meta.reflect(bind=self.old_db_engine)
return self._get_tabname_to_colnames(reflected_meta)
class MainWindow(mainwindow.Ui_MainWindow, QtWidgets.QMainWindow):
def __init__(self):
super(MainWindow, self).__init__()
self.setupUi(self)
self.orRadioButton.setChecked(True)
self.orRadioButton.setEnabled(False)
self.andRadioButton.setEnabled(False)
self.AddToDBButton.setEnabled(False)
self.removeFromDBButton.setEnabled(False)
self.removeFromDBButton2.setEnabled(False)
self.conditionChain = ConditionChain()
self.conditions = []
self.columnTypes = []
self.engine = None
self.session = None
self.table = None
self.initSignals()
self.initActions()
def initSignals(self):
self.openButton.clicked.connect(self.openDb)
self.searchByQueryButton.clicked.connect(self.executeSqlQueryByText)
self.addConditionButton.clicked.connect(self.addCondition)
self.addToConditionsComboBoxButton.clicked.connect(
self.chooseCondition)
self.removeFromResultConditionsButton.clicked.connect(
self.removeFromChosenCondition)
self.selectColumnComboBox.currentIndexChanged.connect(
self.updateOperatorComboBox)
self.showQueryButton.clicked.connect(self.showQuery)
self.clearQueryTextButton.clicked.connect(self.clearQuery)
self.searchByConditionsButton.clicked.connect(self.searchByConditions)
def initActions(self):
self.actionExit.triggered.connect(sys.exit)
self.actionOpen.triggered.connect(self.openDb)
def initColumnKeysComboBox(self):
if self.table == None:
raise NoDataBaseException
for key in self.table.columns.keys():
self.selectColumnComboBox.addItem(str(key))
def updateOperatorComboBox(self):
if self.table == None:
return
currentColumn = self.selectColumnComboBox.currentIndex()
operatorsCount = self.selectOperatorComboBox.count()
if not str(self.columnTypes[currentColumn]).startswith("VARCHAR"):
for i in range(operatorsCount):
self.selectOperatorComboBox.model().item(i).setEnabled(True)
else:
self.selectOperatorComboBox.setCurrentIndex(0)
for i in range(1, operatorsCount):
self.selectOperatorComboBox.model().item(i).setEnabled(False)
def showQuery(self):
if not self.conditionChain or self.table == None:
return
query = QueryCreator.createQuery(self.conditionChain, self.table.name)
self.queryTextWidget.setText(query)
def clearQuery(self):
self.queryTextWidget.setText("")
def addCondition(self):
if self.table == None:
return
columnStr = self.selectColumnComboBox.currentText()
compareOperatorStr = self.selectOperatorComboBox.currentText()
valueStr = self.selectValueLine.text()
currentIndex = self.selectColumnComboBox.currentIndex()
if str(self.columnTypes[currentIndex]).startswith("VARCHAR"):
valueStr = "\"{0}\"".format(valueStr)
condition = Condition(columnStr, compareOperatorStr, valueStr)
self.conditions.append(condition)
self.allConditionsListWidget.addItem(str(condition))
self.selectValueLine.setText("")
def chooseCondition(self):
if not self.conditions or self.table == None:
return
selectedConditionIndex = self.allConditionsListWidget.currentRow()
currentCondition = self.conditions[selectedConditionIndex]
currentOperator = Operator.OR if self.orRadioButton.isChecked(
) else Operator.AND
self.conditionChain.addCondition(currentCondition, currentOperator)
self.resultConditionsListWidget.addItem(str(currentCondition))
self.orRadioButton.setEnabled(True)
self.andRadioButton.setEnabled(True)
print(str(self.conditionChain))
query = QueryCreator.createQuery(self.conditionChain, self.table.name)
print(query)
self.executeSqlQueryByConditions(query)
def removeFromChosenCondition(self):
selectedIndex = self.resultConditionsListWidget.currentRow()
if selectedIndex == -1:
return
self.resultConditionsListWidget.model().removeRow(selectedIndex)
self.conditionChain.popCondition(selectedIndex)
query = QueryCreator.createQuery(self.conditionChain, self.table.name)
print(query)
self.executeSqlQueryByConditions(query)
if not self.conditionChain.conditions:
self.orRadioButton.setEnabled(False)
self.andRadioButton.setEnabled(False)
def initMainTableWidget(self):
if self.table == None or self.engine == None:
raise NoDataBaseException
columnKeys = self.table.columns.keys()
columnsCount = len(columnKeys)
self.mainTableWidget.setColumnCount(columnsCount)
self.mainTableWidget.setHorizontalHeaderLabels(columnKeys)
self.resultTableWidget.setColumnCount(columnsCount)
self.resultTableWidget.setHorizontalHeaderLabels(columnKeys)
rowsCount = self.mainTableWidget.rowCount()
data = self.session.query(self.table).all()
for rowNumber, row in enumerate(data):
self.mainTableWidget.insertRow(rowNumber)
for columnNumber, value in enumerate(row):
cell = QtWidgets.QTableWidgetItem(str(value))
self.mainTableWidget.setItem(rowNumber, columnNumber, cell)
def initResultTableWidget(self, data):
self.resultTableWidget.setRowCount(0)
for rowNumber, row in enumerate(data):
self.resultTableWidget.insertRow(rowNumber)
for columnNumber, value in enumerate(row):
cell = QtWidgets.QTableWidgetItem(str(value))
self.resultTableWidget.setItem(rowNumber, columnNumber, cell)
def openDb(self):
fileName = QtWidgets.QFileDialog.getOpenFileName(
self, "Open DB", "", "*.db")[0]
engineName = "sqlite:///{0}".format(fileName)
self.engine = create_engine(engineName, echo=True)
engine = self.engine
Session = sessionmaker(bind=engine)
self.session = Session()
with engine.connect() as connection:
result = connection.execute("select * from students")
for row in result:
print(row)
self.meta = MetaData()
self.meta.reflect(bind=engine)
self.table = Table(self.meta.sorted_tables[0],
self.meta,
autoload=True,
autoload_with=engine)
self.columnTypes = []
for column in self.table.columns:
self.columnTypes.append(column.type)
print(self.columnTypes)
self.initMainTableWidget()
self.initColumnKeysComboBox()
def addToDb(self):
# Не успел пока разобраться, как реализовать
if self.table == None:
raise NoDataBaseException
def removeFromDb(self):
# Не успел пока разобраться, как реализовать
if self.table == None:
raise NoDataBaseException
currentIndex = self.mainTableWidget.currentRow()
currentObj = self.engine.execute(
"select * from {0} where id = {1}".format(self.table.name,
currentIndex))
self.session.delete(currentObj)
self.session.commit()
self.initMainTableWidget()
def executeSqlQueryByText(self):
if self.table == None:
return
query = self.queryTextWidget.toPlainText()
if query == "":
return
data = self.engine.execute(query)
self.initResultTableWidget(data)
def searchByConditions(self):
if self.table == None:
return
query = QueryCreator.createQuery(self.conditionChain, self.table.name)
self.executeSqlQueryByConditions(query)
def executeSqlQueryByConditions(self, query):
print(query)
if self.table == None or not self.conditionChain:
return
if query == "":
self.resultTableWidget.setRowCount(0)
return
data = self.engine.execute(query)
self.initResultTableWidget(data)
class DbClient:
def __init__(self, db_url=environ.get("DB_URL"), new=False, echo=False):
self.engine = create_engine(db_url, echo=echo)
self.session_maker = sessionmaker(bind=self.engine)
self.metadata = MetaData(bind=self.engine)
spider_loader = spiderloader.SpiderLoader.from_settings(settings())
s_names = spider_loader.list()
self.spiders = tuple(spider_loader.load(name) for name in s_names)
# todo consider wrapping sqlalchemy.exc.OperationalError instead of using new parameter
if new:
create_database(self.engine.url)
self.create_tables(Base)
else:
self.metadata.reflect()
@staticmethod
def current_date():
# finds the latest day based on the mastercard definition
now = datetime.datetime.now(timezone('US/Eastern'))
today = now.date()
if now.hour < 14:
today -= datetime.timedelta(days=1)
return today
@contextmanager
def session_scope(self, commit=True):
"""Provide a transactional scope around a series of operations."""
session = self.session_maker()
try:
yield session
if commit:
session.commit()
except Exception:
session.rollback()
raise
finally:
session.close()
def create_tables(self, base):
base.metadata.create_all(self.engine)
with self.session_scope() as s:
providers = [s.provider for s in self.spiders]
for pid, p_name in enumerate(providers):
s.add(Provider(id=pid + 1, name=p_name))
self.update_currencies(p_name)
# todo differentiate between card currencies and transaction currencies
def missing(self, provider, end=None, num_days=363, currs=None):
with self.session_scope(commit=False) as s:
if not end:
end = self.current_date()
start = end - datetime.timedelta(days=num_days - 1)
spider = next(spider for spider in self.spiders
if spider.provider == provider)
if not currs:
currs = set(spider.fetch_avail_currs().keys())
avail_dates = (end - datetime.timedelta(days=x)
for x in range(num_days))
all_combos = ((x, y, z)
for x, y, z in product(currs, currs, avail_dates)
if x != y)
# noinspection PyUnresolvedReferences
not_missing = set(
s.query(Rate.card_code, Rate.trans_code, Rate.date).filter(
Rate.provider.has(name=provider)).filter(
Rate.date <= end).filter(Rate.date >= start).filter(
Rate.card_code.in_(currs)).filter(
Rate.trans_code.in_(currs)))
return (x for x in all_combos if x not in not_missing)
# todo multiprocessing to be implemented
@staticmethod
def combos_to_csv(file_count, results, out_path):
out_path = Path(out_path)
try:
out_path.mkdir()
except FileExistsError:
pass
paths = tuple(out_path / f'{i}.csv' for i in range(file_count))
for p in paths:
p.touch()
fs = []
try:
fs = tuple(p.open(mode='w') for p in paths)
for i, (card_c, trans_c, date) in enumerate(results):
std_date = date.strftime(std_date_fmt)
fs[i % file_count].write(f'{card_c},{trans_c},{std_date}\n')
finally:
for f in fs:
f.close()
def rates_from_csv(self, provider, in_path):
with self.session_scope() as s:
provider_id = (s.query(
Provider.id).filter_by(name=provider).first()[0])
for file in Path(in_path).glob('*.csv'):
print(file)
with file.open() as f:
data = csv.reader(f)
next(data) # skip header row #
rates = [
Rate(card_code=card_code,
trans_code=trans_code,
date=strpdate(date, fmt='%m/%d/%Y'),
provider_id=provider_id,
rate=rate)
for card_code, trans_code, date, rate in data
]
s.bulk_save_objects(rates)
s.commit()
def update_currencies(self, provider):
spider = next(s for s in self.spiders if s.provider == provider)
with self.session_scope() as s:
for alpha_code, name in spider.fetch_avail_currs().items():
try:
s.add(CurrencyCode(alpha_code=alpha_code, name=name))
s.commit()
except IntegrityError:
s.rollback()
def drop_all_tables(self):
self.metadata.drop_all()
def drop_database(self):
drop_database(self.engine.url)
return my_args
IB_PORT = os.environ.get('IB_PORT')
if not IB_PORT:
IB_PORT = '4003'
# IB_PORT = '7496'
engine = create_engine(
'postgresql://*****:*****@localhost:2345/option_price_tracking')
connection = engine.connect()
Session = sessionmaker(bind=engine)
session = Session()
meta = MetaData()
meta.reflect(bind=engine)
contract_timestamp_table = meta.tables["contract_ib_first_timestamp"]
contracts = meta.tables["contracts"]
timeout_retry_flag = 0
def set_timeout_flag(flag_value: bool, conId):
stmt = update(contracts). \
where(contracts.c.conId == conId). \
values(timestampReqTimedout=flag_value, timestampLoadAttemptDate=datetime.datetime.now(datetime.timezone.utc))
session.execute(stmt)
session.commit()
def onError(reqId, errorCode, errorString, contract):
def read_sql_table(table_name,
con,
schema=None,
index_col=None,
coerce_float=True,
parse_dates=None,
columns=None):
"""Read SQL database table into a DataFrame.
Given a table name and an SQLAlchemy engine, returns a DataFrame.
This function does not support DBAPI connections.
Parameters
----------
table_name : string
Name of SQL table in database
con : SQLAlchemy engine
Sqlite DBAPI connection mode not supported
schema : string, default None
Name of SQL schema in database to query (if database flavor
supports this). If None, use default schema (default).
index_col : string, optional
Column to set as index
coerce_float : boolean, default True
Attempt to convert values to non-string, non-numeric objects (like
decimal.Decimal) to floating point. Can result in loss of Precision.
parse_dates : list or dict
- List of column names to parse as dates
- Dict of ``{column_name: format string}`` where format string is
strftime compatible in case of parsing string times or is one of
(D, s, ns, ms, us) in case of parsing integer timestamps
- Dict of ``{column_name: arg dict}``, where the arg dict corresponds
to the keyword arguments of :func:`pandas.to_datetime`
Especially useful with databases without native Datetime support,
such as SQLite
columns : list
List of column names to select from sql table
Returns
-------
DataFrame
See also
--------
read_sql_query : Read SQL query into a DataFrame.
read_sql
"""
if not _is_sqlalchemy_engine(con):
raise NotImplementedError("read_sql_table only supported for "
"SQLAlchemy engines.")
import sqlalchemy
from sqlalchemy.schema import MetaData
meta = MetaData(con, schema=schema)
try:
meta.reflect(only=[table_name])
except sqlalchemy.exc.InvalidRequestError:
raise ValueError("Table %s not found" % table_name)
pandas_sql = PandasSQLAlchemy(con, meta=meta)
table = pandas_sql.read_table(table_name,
index_col=index_col,
coerce_float=coerce_float,
parse_dates=parse_dates,
columns=columns)
if table is not None:
return table
else:
raise ValueError("Table %s not found" % table_name, con)
