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()