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 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 insertData(table: str, df, conn) -> None:
cur = conn.cursor()
li = df.values.tolist()
cols = list(df)
for row in li:
try:
sql = i_sql_lite(table, col_lite(cols), value_lite(row))
execute_lite(cur, sql)
except sqlite3.IntegrityError as e:
logger.warn(e)
conn.commit()
except Exception as e:
logger.error(e)
conn.commit()
raise type(e)(e)
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 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_lite(conn: conn_lite, table: str, df: pd.DataFrame) -> None:
cur = conn.cursor()
li = df.values.tolist()
cols = list(df)
for row in li:
try:
sql = i_sql_lite(table, col_lite(cols), value_lite(row))
execute_lite(cur, sql)
except sqlite3.IntegrityError as e:
conn.commit()
if 'UNIQUE constraint failed:' in str(e):
logger.warn(e)
else:
logger.error(e)
raise type(e)(e)
except Exception as e:
logger.error(e)
conn.commit()
raise type(e)(e)
conn.commit()