def select(table, select_value="*", where=None, conn=None, cursor=None):
if where is None:
where = []
if conn is not None: # if user wants to specify connection himself
table_data = Table(table)
if len(where) > 0:
q = Query.from_(table_data).select(select_value).where(
table_data.field(where[0]) == where[1]).get_sql().replace(
'"', "")
else:
q = Query.from_(table_data).select(select_value).get_sql().replace(
'"', "")
value = cursor.execute(q)
return value
else: # if he doesn't
conn, cursor = connect()
table_data = Table(table)
if len(where) > 0:
q = Query.from_(table_data).select(select_value).where(
table_data.field(where[0]) == where[1]).get_sql().replace(
'"', "")
else:
q = Query.from_(table_data).select(select_value)
value = cursor.execute(q)
disconnect(conn, cursor)
return value
def put(table, field_value=None, where=None, conn=None, cursor=None):
if where is None:
where = []
if field_value is None:
field_value = {}
try:
if conn is not None:
table_data = Table(table)
q = Query.update(table_data).set(
field_value[0], field_value[1]).where(
table_data.field(where[0]) == where[1]).get_sql().replace(
'"', "")
select(table, conn=conn, cursor=cursor) # fetch cursor size
before_put_rowcount = cursor.rowcount
cursor.execute(q)
select(table, conn=conn,
cursor=cursor) # fetch cursor size after insert
if before_put_rowcount + 1 == cursor.rowcount:
return "Success"
else:
return "Failed"
else:
conn, cursor = connect()
table_data = Table(table)
q = Query.update(table_data).set(
field_value[0], field_value[1]).where(
table_data.field(where[0]) == where[1]).get_sql().replace(
'"', "")
select(table, conn=conn, cursor=cursor) # fetch cursor size
before_put_rowcount = cursor.rowcount
cursor.execute(q)
select(table, conn=conn,
cursor=cursor) # fetch cursor size after insert
if before_put_rowcount + 1 == cursor.rowcount:
disconnect(conn, cursor)
return "Success"
else:
disconnect(conn, cursor)
return "Failed"
except pymysql.IntegrityError as err:
return "Failed"
def get_column_definitions(self, schema, table, connection=None):
table_columns = Table("columns")
table_query = (VerticaQuery.from_(
table_columns, immutable=False).select(
table_columns.column_name, table_columns.data_type).where(
(table_columns.table_schema == schema)
& (table_columns.field("table_name") == table)).distinct())
return self.fetch(str(table_query), connection=connection)
def get_column_definitions(self, schema, table, connection=None):
columns = Table('COLUMNS', schema='INFORMATION_SCHEMA')
columns_query = (MSSQLQuery.from_(columns, immutable=False).select(
columns.COLUMN_NAME, columns.DATA_TYPE).where(
columns.TABLE_SCHEMA == Parameter('%(schema)s')).where(
columns.field('TABLE_NAME') == Parameter(
'%(table)s')).distinct().orderby(columns.column_name))
return self.fetch(str(columns_query),
connection=connection,
parameters=dict(schema=schema, table=table))
def get_column_definitions(self, schema, table, connection=None):
columns = Table('columns', schema='INFORMATION_SCHEMA')
columns_query = MySQLQuery \
.from_(columns) \
.select(columns.column_name, columns.column_type) \
.where(columns.table_schema == schema) \
.where(columns.field('table_name') == table) \
.distinct() \
.orderby(columns.column_name)
return self.fetch(str(columns_query), connection=connection)
def get_column_definitions(self, schema, table, connection=None):
columns = Table("columns", schema="INFORMATION_SCHEMA")
columns_query = (
PostgreSQLQuery.from_(columns, immutable=False)
.select(columns.column_name, columns.data_type)
.where(columns.table_schema == schema)
.where(columns.field("table_name") == table)
.distinct()
.orderby(columns.column_name)
)
return self.fetch(str(columns_query), connection=connection)
def get_column_definitions(self, schema, table, connection=None):
columns = Table('columns', schema='INFORMATION_SCHEMA')
columns_query = (
MySQLQuery.from_(columns)
.select(columns.column_name, columns.column_type)
.where(columns.table_schema == Parameter('%(schema)s'))
.where(columns.field('table_name') == Parameter('%(table)s'))
.distinct()
.orderby(columns.column_name)
)
return self.fetch(str(columns_query), parameters=dict(schema=schema, table=table), connection=connection)
def delete(table, where, conn=None, cursor=None):
if where is None:
where = []
if conn is not None:
table_data = Table(table)
q = Query.from_(table_data).delete().where(
table_data.field(where[0]) == where[1]).get_sql().replace('"', "")
select(table, conn=conn, cursor=cursor) # fetch cursor size
before_put_rowcount = cursor.rowcount
cursor.execute(q)
select(table, conn=conn,
cursor=cursor) # fetch cursor size after insert
print(before_put_rowcount, cursor.rowcount)
if before_put_rowcount - 1 == cursor.rowcount:
return "Success"
else:
return "Failed"
else:
conn, cursor = connect()
table_data = Table(table)
q = Query.from_(table_data).delete().where(
table_data.field(where[0]) == where[1]).get_sql().replace('"', "")
select(table, conn=conn, cursor=cursor) # fetch cursor size
before_put_rowcount = cursor.rowcount
cursor.execute(q)
select(table, conn=conn,
cursor=cursor) # fetch cursor size after insert
if before_put_rowcount - 1 == cursor.rowcount:
disconnect(conn, cursor)
return "Success"
else:
disconnect(conn, cursor)
return "Failed"
def get_column_definitions(self, schema, table, connection=None):
columns = Table("columns", schema="information_schema")
columns_query = (PostgreSQLQuery.from_(
columns, immutable=False).select(
columns.column_name, columns.data_type).where(
columns.table_schema == Parameter('%(schema)s')).where(
columns.field("table_name") == Parameter(
'%(table)s')).distinct().orderby(
columns.column_name))
return self.fetch(str(columns_query),
parameters=dict(schema=schema, table=table),
connection=connection)
def get_impact_categories(
conn,
method_name=None,
category_name=None,
methods_columns=['ID', 'REF_ID', 'NAME'],
categories_columns=['ID', 'REF_ID', 'NAME', 'REFERENCE_UNIT']):
"""
Get impact categories from sqlite openLCA database. Each category is part of a method but it uniquely
defines the coefficients for each elementary flow.
:param sqlite3.Connection conn: database connection
:param list method_name: partial method names on which to filter
:param list category_name: partial category names on which to filter
:param list methods_columns: table columns to return
:param list categories_columns: table columns to return
:return: Dataframe
"""
categories = Table('TBL_IMPACT_CATEGORIES')
methods = Table('TBL_IMPACT_METHODS')
methods_fields = [
methods.field(c).as_('methods_' + c) for c in methods_columns
]
categories_fields = [
categories.field(c).as_('categories_' + c) for c in categories_columns
]
q = Query \
.from_(categories) \
.left_join(methods).on(categories.F_IMPACT_METHOD == methods.ID) \
.select(*methods_fields, *categories_fields)
if method_name:
q = q.where(Criterion.any([methods.name.like(p) for p in method_name]))
if category_name:
q = q.where(
Criterion.any([categories.name.like(p) for p in category_name]))
return get_df(conn, q)
def execute_select(self, model, *args, **kwargs) -> list:
instance_list = []
db_table = Table(model.model_meta.db_table)
query = Query.from_(db_table)
for table_name in model.model_meta.fk_fields:
table = Table(table_name)
query = query.join(table).on(PField(table_name + '_id'))
query = query.select(*args)
for k, v in kwargs.items():
query = query.where(db_table.field(k) == v)
raw_results = self._run_query(query.get_sql())
# for row in raw_results:
# instance = self.model(**row)
# if custom_fields:
# for field in custom_fields:
# setattr(instance, field, row[field])
# instance_list.append(instance)
return instance_list
def make_fields(table: Table, column_names: List[str]) -> List[Field]:
return [table.field(name) for name in column_names]