list2SQL.py

import pymssql
import logging
import math
import datetime
from DataCleaning.library.functions.processBar import *

def list2SQL(cursor, list0, tempTableName, titleList, typeList, appendOnly = False):
    # require list in the format: [
    # [row 1],
    # [row 2],
    # ...
    # ]
    startTime = datetime.datetime.now()
    processBar(context="inserting data...")
    logging.info("inserting table: " + tempTableName)
    sqlDrop = "DROP TABLE IF EXISTS " + tempTableName
    sqlCreate, tempTitle2 = generateSQLCreate(titleList, typeList, tempTableName)
    # run corresponding queries to drop and create tempTableName
    if appendOnly == False:
        cursor.execute(sqlDrop)
        # logging.info("successfully DROPPED table: " + tempTableName)
        # logging.info("creating table: " + tempTableName + " by using SQL:\n" + sqlCreate)
        cursor.execute(sqlCreate)
        logging.info("successfully CREATED table: " + tempTableName)
    rounds = math.ceil(len(list0)/1000)
    for i in range(rounds):
        start = i * 1000
        if i != rounds - 1:
            end = (i + 1) * 1000
        else:
            end = len(list0)
        # tempList = [""] * (end - start)
        # for j in range(len(tempList)):
        #     tempList[j] = "('" + "','".join(list0[start + j]) + "')"
        tempList = []
        for j in range(start, end):
            tempString = "('" + "','".join(list0[j]) + "')"
            tempList.append(tempString.replace("'NULL'", "NULL"))

        tempValues = ",".join(tempList)

        sqlExecute = generateInsert(tempTableName, tempTitle2, tempValues)

        # try:
        #     cursor.execute(sqlExecute)
        #     logging.info("successfully inserted " + str((i + 1) * 1000) + " lines.")
        # except Exception as error:
        #     print(sqlExecute)
        #     print(error)
        #     logging.info(sqlExecute)

        cursor.execute(sqlExecute)
        processBar(i, rounds)
        # logging.info("successfully inserted " + str((i + 1) * 1000) + " lines.")

        # initialize sqls after execution
        sqlExecute = ""
    logging.info("successfully inserted " + str(len(list0)) + " lines to table " + tempTableName)
    endTime = datetime.datetime.now()
    logging.info("time spent on inserting " + tempTableName + ": " + str(endTime - startTime))


# generate the sql which creates an empty table with col properties with name tempTableName
def generateSQLCreate(titleList, typeList, tempTableName):
    # for creation purpose, with format: "col1 varchar(255), ..., colN varchar(255)"
    tempTitle = ""
    # for insertion purpose, with format: "col1 , ..., colN"
    tempTitle2 = ""
    # fill tempTitle and tempTitle2
    for i in range(len(titleList)):
        tempTitle += titleList[i] + ' ' + typeList[i] + ', '
        tempTitle2 += titleList[i] + ','
    tempTitle = tempTitle.strip().strip(",")
    tempTitle2 = tempTitle2.strip(",")
    sqlCreate = "CREATE TABLE " + tempTableName + "(" + tempTitle + ")"
    return sqlCreate, tempTitle2


# generate SQL to INSERT
def generateInsert(tempTableName, tempTitle, sqlTemp):
    return("insert into " + tempTableName + "(" + tempTitle + ") values " + sqlTemp)