pyodbc connect to sql server

Install:

https://github.com/mkleehammer/pyodbc/wiki/Install

code:
import pyodbc
#connect to db
conn = pyodbc.connect(
    r'DRIVER={ODBC Driver 13 for SQL Server};'
    r'SERVER=127.0.0.1;'
    r'DATABASE=DB_table;'
    r'UID=yasam;'
    r'PWD=password'
    )
cursor = conn.cursor()
sqlInsert="INSERT INTO [dbo].[test_table](RecordID,Model,SubmitDate,CountryCode,Score,Comment) VALUES "

for i, d in enumerate(df):
    print(i)
    #truncate to 1024 for db size
    if len(d[5])>1024:
        d[5]=d[5][:1024]
    if len(d[1])>32:
        d[2]=d[1][:32]
    d[5]=d[5].replace("'","''") # Comment, replace ' for sql
    d[2]=d[2].replace("'","''") #Model
    d[4]=str(d[4]) # float to str
    temp="("+",".join(["N'"+dd+"'" for dd in d])+")" #N for encoding
    tList.append(temp)
    if i == len(df)-1:
        text=','.join(tList)
        cursor.execute(sqlInsert+text)    #last insert
    elif i % 10 == 9:
        text=','.join(tList)
        print(text)
        cursor.execute(sqlInsert+text)   #batch insert
        tList=[]
        temp=''
conn.commit()

reference:

pyodbc 用法

https://my.oschina.net/zhengyijie/blog/35587

Inserting multiple rows in a single SQL query

https://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query

Pyodbc query string quote escaping

使用兩個''避免或用?方式

unicode 問題(沒遇到)

http://blog.csdn.net/samed/article/details/50539742