如何利用Python实现SQL自动化？

   import pyodbc

　　from datetime import datetime

　　classSql:

　　def__init__(self,  database,server="XXVIR00012,55000"):

　　# here we are  tellingpythonwhat to connect to (our SQL Server)

　　self.cnxn = pyodbc.connect("Driver={SQL  Server Native Client 11.0};"

　　"Server="+server+";"

　　"Database="+database+";"

　　"Trusted_Connection=yes;")

　　# initialise  query attribute

　　self.query ="--  {}\n\n-- Made in Python".format(datetime.now()

　　.strftime("%d/%m/%Y"))

   self.cnxn =pyodbc.connect("Driver={SQL Server Native Client 11.0};"

　　"Server="+self.server+";"

　　"Database="+self.database+";"

　　"Trusted_Connection=yes;")

   self.query = "-- {}\n\n--Made in Python".format(datetime.now()

　　.strftime("%d/%m/%Y"))

   import sys

　　sys.path.insert(0, r'C:\\User\medium\pysqlplus\lib')

　　import os

　　from data importSql

　　sql =Sql('database123')  # initialise the Sql object

　　directory =r'C:\\User\medium\data\\'  # this is where our generic data is  stored

　　file_list = os.listdir(directory)  # get a list of all files

　　for file in  file_list:  # loop to import  files to sql

　　df = pd.read_csv(directory+file)  # read file to dataframe

　　sql.push_dataframe(df, file[:-4])

　　# now we  convert our file_list names into the table names we have imported to SQL

　　table_names = [x[:-4] for x in file_list]

　　sql.union(table_names, 'generic_jan')  # union our files into one new table  called 'generic_jan'

　　sql.drop(table_names)  # drop our original tables as we now  have full table

　　# get list of  categories in colX, eg ['hr', 'finance', 'tech', 'c_suite']

　　sets =list(sql.manual("SELECT  colX AS 'category' FROM generic_jan GROUP BY colX", response=True)['category'])

　　for category in sets:

　　sql.manual("SELECT *  INTO generic_jan_"+category+" FROM  generic_jan WHERE colX = '"+category+"'")

   defpush_dataframe(self, data,  table="raw_data", batchsize=500):

　　# create execution cursor

　　cursor = self.cnxn.cursor()

　　# activate fast execute

　　cursor.fast_executemany =True

　　# create create table statement

　　query ="CREATE  TABLE ["+ table +"] (\n"

　　# iterate through each column to be  included in create table statement

　　for i inrange(len(list(data))):

　　query +="\t[{}]  varchar(255)".format(list(data)[i])  # add column (everything is varchar  for now)

　　# append correct  connection/end statement code

　　if i !=len(list(data))-1:

　　query +=",\n"

　　else:

　　query +="\n);"

　　cursor.execute(query)  # execute the create table statement

　　self.cnxn.commit()  # commit changes

　　# append query to our SQL code logger

　　self.query += ("\n\n--  create table\n"+ query)

　　# insert the data in batches

　　query = ("INSERT  INTO [{}] ({})\n".format(table,

　　'['+'], ['  # get columns

　　.join(list(data)) +']') +

　　"VALUES\n(?{})".format(",  ?"*(len(list(data))-1)))

　　# insert data into target table in  batches of 'batchsize'

　　for i inrange(0, len(data), batchsize):

　　if i+batchsize >len(data):

　　batch = data[i: len(data)].values.tolist()

　　else:

　　batch = data[i: i+batchsize].values.tolist()

　　# execute batch  insert

　　cursor.executemany(query, batch)

　　# commit insert  to SQL Server

　　self.cnxn.commit()

   defmanual(self, query,  response=False):

　　cursor = self.cnxn.cursor()  # create execution cursor

　　if response:

　　returnread_sql(query,  self.cnxn)  # get sql query  output to dataframe

　　try:

　　cursor.execute(query)  # execute

　　except pyodbc.ProgrammingErroras error:

　　print("Warning:\n{}".format(error))  # print error as a warning

　　self.cnxn.commit()  # commit query to SQL Server

　　return"Query  complete."

   defunion(self,  table_list, name="union", join="UNION"):

　　# initialise the query

　　query ="SELECT *  INTO ["+name+"] FROM (\n"

　　# build the SQL query

　　query +=f'\n{join}\n'.join(

　　[f'SELECT [{x}].* FROM [{x}]'for x in table_list]

　　)

　　query +=")  x"  # add end of  query

　　self.manual(query, fast=True)  # fast execute

   defdrop(self,  tables):

　　# check if single or list

　　ifisinstance(tables, str):

　　# if single  string, convert to single item in list for for-loop

　　tables = [tables]

　　for table in tables:

　　# check for  pre-existing table and delete if present

　　query = ("IF  OBJECT_ID ('["+table+"]', 'U')  IS NOT NULL "

　　"DROP TABLE  ["+table+"]")

　　self.manual(query)  # execute