def save_data_to_database(self, df: pd.DataFrame, engine: Engine):
for idx, row in df.iterrows():
with engine.connect() as conn:
t = Table('citation', MetaData(), autoload_with=engine)
# Delete and reinsert as no upsert command is available
stmt = t.delete().where(delete_stmt_decisions_with_df(df))
engine.execute(stmt)
for k in row['citations'].keys():
citation_type_id = CitationType(k).value
for citation in row['citations'][k]:
stmt = t.insert().values([{
"decision_id":
str(row['decision_id']),
"citation_type_id":
citation_type_id,
"url":
citation.get("url"),
"text":
citation["text"]
}])
engine.execute(stmt)
def _compile_print_and_run_sql_query(
sql_schema_info: SQLAlchemySchemaInfo,
graphql_query: str,
parameters: Dict[str, Any],
engine: Engine,
) -> List[Dict[str, Any]]:
"""Compile, print, bind the arguments, then execute the query."""
compilation_result = compile_graphql_to_sql(sql_schema_info, graphql_query)
printed_query = print_sqlalchemy_query_string(compilation_result.query,
sql_schema_info.dialect)
query_with_parameters = bind_parameters_to_query_string(
printed_query, compilation_result.input_metadata, parameters)
return materialize_result_proxy(engine.execute(query_with_parameters))
def compile_and_run_sql_query(
sql_schema_info: SQLAlchemySchemaInfo,
graphql_query: str,
parameters: Dict[str, Any],
engine: Engine,
) -> List[Dict[str, Any]]:
"""Compile and run a SQL query against the supplied SQL backend."""
compilation_result = graphql_to_sql(sql_schema_info, graphql_query,
parameters)
query = compilation_result.query
results = []
for result in engine.execute(query):
results.append(dict(result))
return results
def compile_and_run_sql_query(
sql_schema_info: SQLAlchemySchemaInfo,
graphql_query: str,
parameters: Dict[str, Any],
engine: Engine,
) -> Tuple[List[Dict[str, Any]], Dict[str, OutputMetadata]]:
"""Compile and run a SQL query against the SQL engine, return result and output metadata."""
compilation_result = graphql_to_sql(sql_schema_info, graphql_query,
parameters)
query = compilation_result.query
results = []
for result in engine.execute(query):
results.append(dict(result))
# Output metadata is needed for MSSQL fold postprocessing.
return results, compilation_result.output_metadata
def __init__(
self,
db_engine: Engine,
db_table: str,
db_col: str,
order_col: str = "id",
):
self.db_engine = db_engine
self.db_table = db_table
self.db_col = db_col
result = db_engine.execute(
f"SELECT {db_col} FROM {db_table} ORDER BY {order_col}"
) # each row is a tuple
self.tweets = [r[0] for r in result]
self.size = len(self.tweets)
def fetch_job_listings(engine: Engine) -> Optional[List[dict]]:
"""
Select rows from database and parse as list of dicts.
:param engine: Database engine to handle raw SQL queries.
:type engine: engine
:return: Optional[List[dict]]
"""
result = engine.execute(
text("SELECT job_id, agency, business_title, \
salary_range_from, salary_range_to \
FROM nyc_jobs ORDER BY RAND() LIMIT 10;"))
rows = [dict(row) for row in result.fetchall()]
LOGGER.info(f"Selected {result.rowcount} rows: {rows}")
return rows
def update_job_listing(engine: Engine) -> Optional[List[dict]]:
"""
Update row in database with problematic characters escaped.
:param engine: Engine object representing a SQL database.
:type engine: engine
:return: Optional[List[dict]]
"""
result = engine.execute(
text("UPDATE nyc_jobs SET business_title = 'Senior QA Scapegoat 🏆', \
job_category = 'Information? <>!#%%Technology!%%#^&%* & Telecom' \
WHERE job_id = 229837;"))
LOGGER.info(f"Selected {result.rowcount} row: \
{result}")
return result.rowcount
def dump_orm_object_as_insert_sql(engine: Engine, obj: object,
fileobj: TextIO) -> None:
"""
Takes a SQLAlchemy ORM object, and writes ``INSERT`` SQL to replicate it
to the output file-like object.
Args:
engine: SQLAlchemy :class:`Engine`
obj: SQLAlchemy ORM object to write
fileobj: file-like object to write to
"""
# literal_query = make_literal_query_fn(engine.dialect)
insp = inspect(obj)
# insp: an InstanceState
# http://docs.sqlalchemy.org/en/latest/orm/internals.html#sqlalchemy.orm.state.InstanceState # noqa
# insp.mapper: a Mapper
# http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper # noqa
# Don't do this:
# table = insp.mapper.mapped_table
# Do this instead. The method above gives you fancy data types like list
# and Arrow on the Python side. We want the bog-standard datatypes drawn
# from the database itself.
meta = MetaData(bind=engine)
table_name = insp.mapper.mapped_table.name
# log.debug("table_name: {}", table_name)
table = Table(table_name, meta, autoload=True)
# log.debug("table: {}", table)
# NewRecord = quick_mapper(table)
# columns = table.columns.keys()
query = select(table.columns)
# log.debug("query: {}", query)
for orm_pkcol in insp.mapper.primary_key:
core_pkcol = table.columns.get(orm_pkcol.name)
pkval = getattr(obj, orm_pkcol.name)
query = query.where(core_pkcol == pkval)
# log.debug("query: {}", query)
cursor = engine.execute(query)
row = cursor.fetchone() # should only be one...
row_dict = dict(row)
# log.debug("obj: {}", obj)
# log.debug("row_dict: {}", row_dict)
statement = table.insert(values=row_dict)
# insert_str = literal_query(statement)
insert_str = get_literal_query(statement, bind=engine)
writeline_nl(fileobj, insert_str)
def dump_orm_object_as_insert_sql(engine: Engine,
obj: object,
fileobj: TextIO) -> None:
# literal_query = make_literal_query_fn(engine.dialect)
insp = inspect(obj)
# insp: an InstanceState
# http://docs.sqlalchemy.org/en/latest/orm/internals.html#sqlalchemy.orm.state.InstanceState # noqa
# insp.mapper: a Mapper
# http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper # noqa
# Don't do this:
# table = insp.mapper.mapped_table
# Do this instead. The method above gives you fancy data types like list
# and Arrow on the Python side. We want the bog-standard datatypes drawn
# from the database itself.
meta = MetaData(bind=engine)
table_name = insp.mapper.mapped_table.name
# log.debug("table_name: {}".format(table_name))
table = Table(table_name, meta, autoload=True)
# log.debug("table: {}".format(table))
# NewRecord = quick_mapper(table)
# columns = table.columns.keys()
query = sql.select(table.columns)
# log.debug("query: {}".format(query))
for orm_pkcol in insp.mapper.primary_key:
core_pkcol = table.columns.get(orm_pkcol.name)
pkval = getattr(obj, orm_pkcol.name)
query = query.where(core_pkcol == pkval)
# log.debug("query: {}".format(query))
cursor = engine.execute(query)
row = cursor.fetchone() # should only be one...
row_dict = dict(row)
# log.debug("obj: {}".format(obj))
# log.debug("row_dict: {}".format(row_dict))
statement = table.insert(values=row_dict)
# insert_str = literal_query(statement)
insert_str = get_literal_query(statement, bind=engine)
writeline_nl(fileobj, insert_str)
def compile_and_run_sql_query(
sql_schema_info: SQLAlchemySchemaInfo,
graphql_query: str,
parameters: Dict[str, Any],
engine: Engine,
) -> Tuple[List[Dict[str, Any]], Dict[str, OutputMetadata]]:
"""Compile and run a SQL query against the SQL engine, return result and output metadata."""
compilation_result = graphql_to_sql(sql_schema_info, graphql_query,
parameters)
query = compilation_result.query
results = materialize_result_proxy(engine.execute(query))
# Check that when printed the query produces the same result
printed_query_results = _compile_print_and_run_sql_query(
sql_schema_info, graphql_query, parameters, engine)
if sort_db_results(results) != sort_db_results(printed_query_results):
raise AssertionError(
f"Query {graphql_query} with args {parameters} produces different "
f"results when the compiled SQL query is printed before execution."
)
# Output metadata is needed for MSSQL fold postprocessing.
return results, compilation_result.output_metadata
def get_src_iddefs(src_engine: Engine,
src_tables: List[str]) -> Dict[int, IdNumDefinition]:
"""
Get information about all the ID number definitions in the source database.
Args:
src_engine: source SQLAlchemy :class:`Engine`
src_tables: list of all table names in the source database
Returns:
dictionary: ``{which_idnum: idnumdef}`` mappings, where each
``idnumdef`` is a
:class:`camcops_server.cc_modules.cc_idnumdef.IdNumDefinition` not
attached to any database session
"""
iddefs = {} # type: Dict[int, IdNumDefinition]
if IdNumDefinition.__tablename__ in src_tables:
# Source is a more modern CamCOPS database, with an IdNumDefinition
# table.
log.info(
"Fetching source ID number definitions from {!r} table",
IdNumDefinition.__tablename__,
)
# noinspection PyUnresolvedReferences
q = (select([
IdNumDefinition.which_idnum,
IdNumDefinition.description,
IdNumDefinition.short_description,
]).select_from(IdNumDefinition.__table__).order_by(
IdNumDefinition.which_idnum))
rows = src_engine.execute(q).fetchall()
for row in rows:
which_idnum = row[0]
iddefs[which_idnum] = IdNumDefinition(
which_idnum=which_idnum,
description=row[1],
short_description=row[2],
)
elif server_stored_var_table_defunct.name in src_tables:
# Source is an older CamCOPS database.
log.info(
"Fetching source ID number definitions from {!r} table",
server_stored_var_table_defunct.name,
)
for which_idnum in range(1, NUMBER_OF_IDNUMS_DEFUNCT + 1):
nstr = str(which_idnum)
qd = (select([
server_stored_var_table_defunct.columns.valueText
]).select_from(server_stored_var_table_defunct).where(
server_stored_var_table_defunct.columns.name ==
ServerStoredVarNamesDefunct.ID_DESCRIPTION_PREFIX + nstr))
rd = src_engine.execute(qd).fetchall()
qs = (select([
server_stored_var_table_defunct.columns.valueText
]).select_from(server_stored_var_table_defunct).where(
server_stored_var_table_defunct.columns.name ==
ServerStoredVarNamesDefunct.ID_SHORT_DESCRIPTION_PREFIX +
nstr))
rs = src_engine.execute(qs).fetchall()
iddefs[which_idnum] = IdNumDefinition(
which_idnum=which_idnum,
description=rd[0][0] if rd else None,
short_description=rs[0][0] if rs else None,
)
else:
log.warning("No information available on source ID number "
"descriptions")
return iddefs
def dump_table_as_insert_sql(engine: Engine,
table_name: str,
fileobj: TextIO,
wheredict: Dict[str, Any] = None,
include_ddl: bool = False,
multirow: bool = False) -> None:
# http://stackoverflow.com/questions/5631078/sqlalchemy-print-the-actual-query # noqa
# http://docs.sqlalchemy.org/en/latest/faq/sqlexpressions.html
# http://www.tylerlesmann.com/2009/apr/27/copying-databases-across-platforms-sqlalchemy/ # noqa
# https://github.com/plq/scripts/blob/master/pg_dump.py
log.info("dump_data_as_insert_sql: table_name={}".format(table_name))
writelines_nl(fileobj, [
sql_comment("Data for table: {}".format(table_name)),
sql_comment("Filters: {}".format(wheredict)),
])
dialect = engine.dialect
if not dialect.supports_multivalues_insert:
multirow = False
if multirow:
log.warning("dump_data_as_insert_sql: multirow parameter substitution "
"not working yet")
multirow = False
# literal_query = make_literal_query_fn(dialect)
meta = MetaData(bind=engine)
log.debug("... retrieving schema")
table = Table(table_name, meta, autoload=True)
if include_ddl:
log.debug("... producing DDL")
dump_ddl(table.metadata, dialect_name=engine.dialect.name,
fileobj=fileobj)
# NewRecord = quick_mapper(table)
# columns = table.columns.keys()
log.debug("... fetching records")
# log.debug("meta: {}".format(meta)) # obscures password
# log.debug("table: {}".format(table))
# log.debug("table.columns: {}".format(repr(table.columns)))
# log.debug("multirow: {}".format(multirow))
query = sql.select(table.columns)
if wheredict:
for k, v in wheredict.items():
col = table.columns.get(k)
query = query.where(col == v)
# log.debug("query: {}".format(query))
cursor = engine.execute(query)
if multirow:
row_dict_list = []
for r in cursor:
row_dict_list.append(dict(r))
# log.debug("row_dict_list: {}".format(row_dict_list))
statement = table.insert().values(row_dict_list)
# log.debug("statement: {}".format(repr(statement)))
# insert_str = literal_query(statement)
insert_str = get_literal_query(statement, bind=engine)
# NOT WORKING FOR MULTIROW INSERTS. ONLY SUBSTITUTES FIRST ROW.
writeline_nl(fileobj, insert_str)
else:
for r in cursor:
row_dict = dict(r)
statement = table.insert(values=row_dict)
# insert_str = literal_query(statement)
insert_str = get_literal_query(statement, bind=engine)
# log.debug("row_dict: {}".format(row_dict))
# log.debug("insert_str: {}".format(insert_str))
writeline_nl(fileobj, insert_str)
log.debug("... done")
def dump_table_as_insert_sql(engine: Engine,
table_name: str,
fileobj: TextIO,
wheredict: Dict[str, Any] = None,
include_ddl: bool = False,
multirow: bool = False) -> None:
"""
Reads a table from the database, and writes SQL to replicate the table's
data to the output ``fileobj``.
Args:
engine: SQLAlchemy :class:`Engine`
table_name: name of the table
fileobj: file-like object to write to
wheredict: optional dictionary of ``{column_name: value}`` to use as
``WHERE`` filters
include_ddl: if ``True``, include the DDL to create the table as well
multirow: write multi-row ``INSERT`` statements
"""
# http://stackoverflow.com/questions/5631078/sqlalchemy-print-the-actual-query # noqa
# http://docs.sqlalchemy.org/en/latest/faq/sqlexpressions.html
# http://www.tylerlesmann.com/2009/apr/27/copying-databases-across-platforms-sqlalchemy/ # noqa
# https://github.com/plq/scripts/blob/master/pg_dump.py
log.info("dump_data_as_insert_sql: table_name={}", table_name)
writelines_nl(fileobj, [
SEP1,
sql_comment(f"Data for table: {table_name}"),
SEP2,
sql_comment(f"Filters: {wheredict}"),
])
dialect = engine.dialect
if not dialect.supports_multivalues_insert:
multirow = False
if multirow:
log.warning("dump_data_as_insert_sql: multirow parameter substitution "
"not working yet")
multirow = False
# literal_query = make_literal_query_fn(dialect)
meta = MetaData(bind=engine)
log.debug("... retrieving schema")
table = Table(table_name, meta, autoload=True)
if include_ddl:
log.debug("... producing DDL")
dump_ddl(table.metadata,
dialect_name=engine.dialect.name,
fileobj=fileobj)
# NewRecord = quick_mapper(table)
# columns = table.columns.keys()
log.debug("... fetching records")
# log.debug("meta: {}", meta) # obscures password
# log.debug("table: {}", table)
# log.debug("table.columns: {!r}", table.columns)
# log.debug("multirow: {}", multirow)
query = select(table.columns)
if wheredict:
for k, v in wheredict.items():
col = table.columns.get(k)
query = query.where(col == v)
# log.debug("query: {}", query)
cursor = engine.execute(query)
if multirow:
row_dict_list = []
for r in cursor:
row_dict_list.append(dict(r))
# log.debug("row_dict_list: {}", row_dict_list)
if row_dict_list:
statement = table.insert().values(row_dict_list)
# log.debug("statement: {!r}", statement)
# insert_str = literal_query(statement)
insert_str = get_literal_query(statement, bind=engine)
# NOT WORKING FOR MULTIROW INSERTS. ONLY SUBSTITUTES FIRST ROW.
writeline_nl(fileobj, insert_str)
else:
writeline_nl(fileobj, sql_comment("No data!"))
else:
found_one = False
for r in cursor:
found_one = True
row_dict = dict(r)
statement = table.insert(values=row_dict)
# insert_str = literal_query(statement)
insert_str = get_literal_query(statement, bind=engine)
# log.debug("row_dict: {}", row_dict)
# log.debug("insert_str: {}", insert_str)
writeline_nl(fileobj, insert_str)
if not found_one:
writeline_nl(fileobj, sql_comment("No data!"))
writeline_nl(fileobj, SEP2)
log.debug("... done")
def dump_table_as_insert_sql(engine: Engine,
table_name: str,
fileobj: TextIO,
wheredict: Dict[str, Any] = None,
include_ddl: bool = False,
multirow: bool = False) -> None:
# http://stackoverflow.com/questions/5631078/sqlalchemy-print-the-actual-query # noqa
# http://docs.sqlalchemy.org/en/latest/faq/sqlexpressions.html
# http://www.tylerlesmann.com/2009/apr/27/copying-databases-across-platforms-sqlalchemy/ # noqa
# https://github.com/plq/scripts/blob/master/pg_dump.py
log.info("dump_data_as_insert_sql: table_name={}".format(table_name))
writelines_nl(fileobj, [
sql_comment("Data for table: {}".format(table_name)),
sql_comment("Filters: {}".format(wheredict)),
])
dialect = engine.dialect
if not dialect.supports_multivalues_insert:
multirow = False
if multirow:
log.warning("dump_data_as_insert_sql: multirow parameter substitution "
"not working yet")
multirow = False
# literal_query = make_literal_query_fn(dialect)
meta = MetaData(bind=engine)
log.debug("... retrieving schema")
table = Table(table_name, meta, autoload=True)
if include_ddl:
log.debug("... producing DDL")
dump_ddl(table.metadata,
dialect_name=engine.dialect.name,
fileobj=fileobj)
# NewRecord = quick_mapper(table)
# columns = table.columns.keys()
log.debug("... fetching records")
# log.debug("meta: {}".format(meta)) # obscures password
# log.debug("table: {}".format(table))
# log.debug("table.columns: {}".format(repr(table.columns)))
# log.debug("multirow: {}".format(multirow))
query = sql.select(table.columns)
if wheredict:
for k, v in wheredict.items():
col = table.columns.get(k)
query = query.where(col == v)
# log.debug("query: {}".format(query))
cursor = engine.execute(query)
if multirow:
row_dict_list = []
for r in cursor:
row_dict_list.append(dict(r))
# log.debug("row_dict_list: {}".format(row_dict_list))
statement = table.insert().values(row_dict_list)
# log.debug("statement: {}".format(repr(statement)))
# insert_str = literal_query(statement)
insert_str = get_literal_query(statement, bind=engine)
# NOT WORKING FOR MULTIROW INSERTS. ONLY SUBSTITUTES FIRST ROW.
writeline_nl(fileobj, insert_str)
else:
for r in cursor:
row_dict = dict(r)
statement = table.insert(values=row_dict)
# insert_str = literal_query(statement)
insert_str = get_literal_query(statement, bind=engine)
# log.debug("row_dict: {}".format(row_dict))
# log.debug("insert_str: {}".format(insert_str))
writeline_nl(fileobj, insert_str)
log.debug("... done")
