def data_generator(conn: DBConnection): cursor = conn.get_named_cursor("compute_sentiment_cursor") cursor.itersize = args.batch_size # Get existing id_item from texts to avoid checking them again query = """ SELECT id, title, text FROM {table_items} WHERE NOT EXISTS ( SELECT FROM {table_texts} WHERE id = {table_texts}.id_item ) ORDER BY id; """ # query = "SELECT id, title, text FROM {table} ORDER BY id ASC;" query_sql = sql.SQL(query).format( table_items=sql.Identifier(TABLE_NAME_ITEMS), table_texts=sql.Identifier(TABLE_NAME_TEXTS), ) cursor.execute(query_sql) while True: rows = cursor.fetchmany(args.batch_size) if not rows: break for row in rows: yield row
def get_column_values( conn: DBConnection, table_name: str, column_name: str, limit: Optional[int] = None, fetch_size: int = 10000, cursor_name: str = str(datetime.now()), ) -> Optional[Iterable[Any]]: """ Get all values in a given column in a given table :param conn: DBConnection object :param table_name: name of the table :param column_name: name of the column :param limit: maximum number of values to return :param fetch_size: number of rows to fetch in one batch :param cursor_name: optional name of the cursor :return: generator of values (if any) in the column """ logging.info("getting all values for column: %s", column_name) cursor = conn.get_named_cursor(str(cursor_name)) cursor.itersize = fetch_size if limit: query = "SELECT {column} FROM {table} LIMIT %s;" else: query = "SELECT {column} FROM {table};" query_sql = sql.SQL(query).format(column=sql.Identifier(column_name), table=sql.Identifier(table_name)) cursor.execute(query_sql, [limit] if limit else None) while True: rows = cursor.fetchmany(fetch_size) if not rows: break for row in rows: yield row[0]