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")