def schema_from_db(database, table):
"""Return a schema object for the given table in the given database """
schema = Schema(table)
db = SQLiteDatabase.get_instance()
for _, col, col_type, _, _, is_primary in db.get_table_info(database, table):
schema.add_column(col, col_type, is_primary)
return schema
def init_listings_table(database):
"""Initialize a table for storing listings data, using the given table name """
db = SQLiteDatabase.get_instance()
cols = ",".join(["%s %s" % (key, val) for key, val in conf.LISTINGS_SCHEMA.iteritems()])
init_query = "CREATE TABLE %s (%s)" % (conf.LISTINGS_TABLE, cols)
try:
db.execute_query(database, init_query)
except sqlite3.OperationalError, e:
# update_listings_rows(database)
print("Database already exists. Skipping.")
def insert_database_rows(rows, db_name):
"""Insert any meaningful data from the given list of DatabaseRow
objects into the database """
db = SQLiteDatabase.get_instance()
for row in rows:
values = ",".join([row.get_ad_id(), "'%s'" % row.get_ad_link(),
"'%s'" % row.get_origin_page(),
"'%s'" % row.get_oven_type(),
"'%s'" % row.get_lat(), "'%s'" % row.get_lng()])
query = "REPLACE INTO %s VALUES(%s)" % (conf.LISTINGS_TABLE, values)
db.execute_query(db_name, query)
def remove_columns(self, database, table, remove_cols):
"""Remove the given columns from the given table in the given database """
db = SQLiteDatabase.get_instance()
for col in remove_cols:
db.remove_column(database, table, col)
def drop_table(self, database, table):
"""Remove the given table from the given database completely """
db = SQLiteDatabase.get_instance()
db.drop_table(database, table)
