def execute(_execute):
'''standard SQL API for executing SQL script line'''
# check SQL connection status
if session_connection is None or session_connection.open is False:
e = SQLConnectionError()
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
if isinstance(_execute, bytes):
_execute.decode('utf8')
if not isinstance(_execute, str):
raise SQLValueError('execution', 'invalid executed script')
cursor = session_connection.cursor()
try:
cursor.execute(_execute)
cursor.close()
session_connection.commit()
except pymysql.err.Error as e:
raise e
return cursor
def expr_insert(tbl, vals=None, **kwargs):
'''standard SQL API for database insert operation using expressions'''
if session_connection is None or session_connection.open is False:
e = SQLConnectionError()
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
insert_values = insert_columns = ''
if isinstance(tbl, bytes):
tbl = tbl.decode('utf8')
if not isinstance(tbl, (str, tuple, list)):
e = SQLValueError('expression insert', 'invalid table name')
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
if isinstance(tbl, (tuple, list)):
if len(tbl) > 1:
db_logger.warning('expression insert multiple tables: %s',
','.join(tbl))
table = str(tbl[0]) if not isinstance(tbl[0], str) else tbl[0].strip()
else:
table = tbl.strip('\'" ')
if not table:
e = SQLValueError('expression insert', 'null table')
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
if vals is not None:
if not isinstance(vals, (dict, list, tuple)) or len(vals) == 0:
e = SQLValueError('expression insert', 'invalid values')
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode,
e.emsg)
raise e
if isinstance(vals, dict):
columns = [k.strip('\'"') for k in vals.keys()]
insert_columns = f"({','.join(columns)})"
values = [
v.decode('utf8') if isinstance(v, bytes) else v
for v in vals.values()
]
else:
values = [
v.decode('utf8') if isinstance(v, bytes) else v for v in vals
]
insert_values = f'{tuple(values)}'
kwargs = None
if kwargs is not None:
columns = [k.strip('\'"') for k in kwargs.keys()]
insert_columns = f"({','.join(columns)})"
values = [
v.decode('utf8') if isinstance(v, bytes) else v
for v in kwargs.values()
]
insert_values = f'{tuple(values)}'
# do insert
if insert_columns:
_insert = f'insert into {table} {insert_columns} values {insert_values}'
else:
_insert = f'insert into {table} values {insert_values}'
print('expression insert: ', _insert)
db_logger.info('SQL expression inserting: %s', _insert)
cursor = session_connection.cursor()
try:
result = cursor.execute(_insert)
cursor.close()
session_connection.commit()
except pymysql.err.Error as e:
db_logger.error('SQL expression insert execution error: (%d, %s)',
e.args[0], e.args[1])
raise e
return result
def expr_update(tbl, pairs, clauses=None, **kwargs):
'''standard SQL API for database update operation using expressions'''
if session_connection is None or session_connection.open is False:
e = SQLConnectionError()
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
where_clause = ''
update_exprs = []
if isinstance(tbl, bytes):
tbl = tbl.decode('utf8')
if not isinstance(tbl, (str, tuple, list)):
e = SQLValueError('expression update', 'invalid table name')
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
if isinstance(tbl, (tuple, list)):
if len(tbl) > 1:
db_logger.warning('expression update multiple tables: %s',
','.join(tbl))
table = str(tbl[0]) if not isinstance(tbl[0], str) else tbl[0].strip()
else:
table = tbl.strip('\'" ')
if not table:
e = SQLValueError('expression update', 'null table')
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
if not isinstance(pairs, dict) or len(pairs) == 0:
e = SQLValueError('expression update', 'invalid new pairs')
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
# parse clause, including kwargs clause & dictionary clause
# function arguement clause
if clauses is None and not kwargs:
e = SQLValueError('expression update', 'need to specify where clause')
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
if clauses is not None:
where_clause = join_exprs_clause(clauses)
kwargs = None
# **kwargs arguement clause
typeinfo = query_tbl_fields_datatype(table, '*')
if kwargs is not None:
where_clause = parse_kwargs_clause(tbls=tbl, **kwargs)
# parse update pairs
for k, v in pairs.items():
k = k.strip('\'" ') if isinstance(k, str) else k
if isinstance(v, bytes):
v = v.decode('utf8')
# add quotes for words
if isinstance(v, str) and v.strip()[0] not in '\'"':
print('value %s not quoted' % (v))
if typeinfo[k] in SQL_QUOTED_TYPES:
v = f"'{v.strip()}'"
else:
v = v.strip()
update_exprs += [f'{k}={v}']
print('update_exprs:', update_exprs)
update_pairs = ','.join(update_exprs)
# do update
if where_clause:
_update = f'update {table} set {update_pairs} where {where_clause}'
else:
_update = f'update {table} set {update_pairs}'
print('expression update: ', _update)
db_logger.info('SQL expression updating: %s', _update)
cursor = session_connection.cursor()
try:
result = cursor.execute(_update)
cursor.close()
session_connection.commit()
except pymysql.err.Error as e:
db_logger.error('SQL expression update execution error: (%d, %s)',
e.args[0], e.args[1])
raise e
return result
def expr_query(tbls, fields='*', clauses=None, **kwargs):
'''standard SQL API for database query operation using expressions'''
if session_connection is None or session_connection.open is False:
e = SQLConnectionError()
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
# default expr connector is 'and', expr operator is '=' for **kwargs
where_clause = ''
# concatenate target tables
if isinstance(tbls, bytes):
tbls = tbls.decode('utf8')
if not isinstance(tbls, (str, tuple, list)):
e = SQLValueError('expression query', 'invalid table names')
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
if isinstance(tbls, (tuple, list)):
tables = ','.join([
str(tbl) if not isinstance(tbl, str) else tbl.strip()
for tbl in tbls
])
table_count = len(tbls)
else:
tables = tbls.strip('\'" ')
table_count = 1
if not tables:
e = SQLValueError('expression query', 'null table')
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
# special case, no querying * from multiple tables
if table_count > 1 and fields == '*':
e = SQLValueError('expression query', 'select * from multiple tables')
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
# concatenate query columns
if isinstance(fields, bytes):
fields = fields.decode('utf8')
if not isinstance(fields, (str, tuple, list)):
e = SQLValueError('expression query', 'invalid field names')
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
if fields == '*':
fields = query_tbl_fields(tbls)
columns = ','.join(fields)
elif isinstance(fields, str):
columns = fields.strip('\'" ')
else:
fields = [
str(c) if not isinstance(c, str) else c.strip('\'" ')
for c in fields
]
columns = ','.join(fields)
# parse clause, including kwargs clause & dictionary clause
# function arguement clause
if clauses is not None:
where_clause = join_exprs_clause(clauses)
kwargs = None
# **kwargs arguement clause
if kwargs is not None:
where_clause = parse_kwargs_clause(tbls=tbls, fields=fields, **kwargs)
# do query
if where_clause:
_query = f"select {columns} from {tables} where {where_clause}"
else:
_query = f"select {columns} from {tables}"
print('expression query: ', _query)
db_logger.info('SQL expression querying: %s', _query)
cursor = session_connection.cursor()
try:
cursor.execute(_query)
cursor.close()
session_connection.commit()
except pymysql.err.Error as e:
db_logger.error('SQL expression query execution error: (%d, %s)',
e.args[0], e.args[1])
raise e
# orgnize fetched records
records = cursor.fetchall()
row_count = cursor.rowcount
if row_count == 0:
db_logger.warning('Empty query record, SQL: %s', _query)
return None
elif row_count == 1:
new_records = [formatter(x) for x in records[0]]
data = dict(zip(
(columns, ), new_records)) if isinstance(fields, str) else dict(
zip(fields, new_records))
return (data, )
else:
new_records = [[formatter(x) for x in row] for row in records]
data = [
dict(zip((columns, ), record))
if isinstance(fields, str) else dict(zip(fields, record))
for record in new_records
]
return tuple(data)
def query_tbl_fields_datatype(tbl, fields='*'):
'''query fields\' datatypes of one table, default is all *'''
if session_connection is None or session_connection.open is False:
e = SQLConnectionError()
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
if isinstance(tbl, bytes):
tbl = tbl.decode('utf8')
if not isinstance(tbl, (str, tuple, list)):
e = SQLValueError('query fields datatype', 'invalid table name')
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
if isinstance(tbl, (tuple, list)):
if len(tbl) > 1:
db_logger.warning('query multiple tables fields datatype: %s',
','.join(tbl))
table = str(tbl[0]) if not isinstance(tbl[0], str) else tbl[0].strip()
else:
table = tbl.strip('\'" ')
if not table:
e = SQLValueError('query fields datatype', 'null table')
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
if isinstance(fields, bytes):
fields = fields.decode('utf8')
if not isinstance(fields, (str, tuple, list)):
e = SQLValueError('query fields datatype',
'invalid query fields names')
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.ecode, e.emsg)
raise e
if fields == '*':
fields = query_tbl_fields(tbl)
columns = fields
opcode = 'IN'
elif isinstance(fields, str):
fields = fields.strip()
columns = fields if fields[0] in '\'"' else f"'{fields}'"
opcode = '='
else:
columns = tuple(
[c.strip('\'"') if isinstance(c, str) else str(c) for c in fields])
opcode = 'IN'
_query = f"SELECT `COLUMN_NAME`,`DATA_TYPE` FROM `INFORMATION_SCHEMA`.`COLUMNS` \
WHERE `TABLE_NAME` = '{table}' AND `COLUMN_NAME` {opcode} {columns}"
db_logger.info('SQL fields datatype querying: %s', _query)
cursor = session_connection.cursor()
try:
cursor.execute(_query)
cursor.close()
session_connection.commit()
except pymysql.err.Error as e:
db_logger.error('%s: (%d, %s)', e.__class__.__name__, e.args[0],
e.args[1])
raise e
records = cursor.fetchall()
db_fields = [
str(row[0]) if not isinstance(row[0], str) else row[0]
for row in records
]
db_typeinfo = [
str(row[1]) if not isinstance(row[1], str) else row[1]
for row in records
]
return dict(zip(db_fields, db_typeinfo))