def createTable(table: str, columns, primary_keys, conn, identifier='`'):
columns_join = quote_join_comma(identifier, columns)
primary_keys_join = quote_join_comma(identifier, primary_keys)
sql = 'create table "{}" ({}, PRIMARY KEY({}))'.format(
table, columns_join, primary_keys_join)
print(sql)
try:
conn.execute(sql)
except Exception as e:
logger.error(e)
pass
conn.commit()
def insertDataMySql(table: str, df, conn, identifier='`', identifier1="'"):
global errorList
errorList = []
cur = conn.cursor()
l = df.values.tolist()
for row in l:
try:
sql = 'insert into {0}{1}{0}({2}) values({3})'.format(
identifier, table, quote_join_comma(identifier, list(df)),
quote_join_comma(identifier1, row))
sql = sql.replace("'nan'", 'NULL')
print(sql)
cur.execute(sql) # do not commit every time because it's very slow
except Exception as e:
logger.error(e)
errorList.append(row)
conn.commit()
conn.commit()
def insertDataMany(table: str, df, conn, identifier='`', identifier1='?'):
cur = conn.cursor()
sql = 'insert into {0}{1}{0}({2}) values({3})'.format(
identifier, table, quote_join_comma(identifier, list(df)),
','.join([identifier1] * len(list(df))))
print(sql)
try:
cur.executemany(sql, df.values.tolist())
except Exception as e:
logger.error(e)
conn.commit()
def createTablePostgreIfNotExists(table: str,
columns,
field_types,
primary_keys,
conn,
identifier='"'):
cur = conn.cursor()
columnsQuote = quote(identifier, columns)
columns_join = ', '.join(cols_types(columnsQuote, field_types))
primary_keys_join = quote_join_comma(identifier, primary_keys)
sql = 'create table IF NOT EXISTS "{}" ({}, PRIMARY KEY({}))'.format(
table, columns_join, primary_keys_join)
print(sql)
try:
cur.execute(sql)
except Exception as e:
logger.error(e)
pass
conn.commit()
def i_sql(identifier: str, table: str, cols: str, values: str) -> str:
return 'insert into {0}{1}{0}({2}) values({3})'.format(
identifier, table, cols, values)
@toolz.curry
def i_sql_lite(table: str, cols: str, values: str) -> str:
return i_sql('`', table, cols, values)
@toolz.curry
def i_sql_pg(table: str, cols: str, values: str) -> str:
return i_sql('"', table, cols, values)
col_lite = quote_join_comma('`')
value_lite = quote_join_comma("'")
col_pg = quote_join_comma('"')
value_pg = quote_join_comma("'")
# @toolz.curry
# def i_lite(conn: conn_lite, table: str, df: pd.DataFrame) -> List[dict]:
# exceptions = []
# cur = conn.cursor()
# li = df.values.tolist()
# for row in li:
# try:
# sql = i_sql_lite(table, col_lite(list(df)), value_lite(row))
def selectLast(columns, table: str, conn, identifier = '`'):
sql = "SELECT * from {2}{1}{2} ORDER BY {0} DESC LIMIT 1;".format(quote_join_comma(identifier, columns), table, identifier)
print(sql)
return pd.read_sql_query(sql, conn)
def selectDistinct(columns, table: str, conn, identifier = '`'):
sql = "SELECT DISTINCT {0} from {2}{1}{2} ;".format(quote_join_comma(identifier, columns), table, identifier)
print(sql)
return pd.read_sql_query(sql, conn)
def s_dist_pg(conn: conn_pg, table: str, cols: Iterable) -> pd.DataFrame:
sql = 'SELECT distinct {} FROM "{}"'.format(quote_join_comma('"', cols), table)
return pd.read_sql_query(sql, conn)