list2SQL.py
2021/10/6 2:11:19
本文主要是介绍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)
这篇关于list2SQL.py的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-09-19环境变量处理课程:新手入门教程
- 2024-09-19接口模块封装课程:新手入门指南
- 2024-09-19请求动作封装课程:新手入门教程
- 2024-09-19拖拽表格课程:新手入门指南
- 2024-09-19页面权限课程:新手必学的权限管理入门教程
- 2024-09-19如何正确主动登出课程:新手必读教程
- 2024-09-19Element-Plus课程:新手入门与初级教程
- 2024-09-19Token处理入门教程:新手必看指南
- 2024-09-19如何应对被动登出课程的情况:新手必读指南
- 2024-09-19打包优化课程:初学者的必备指南