import pyodbc
import traceback
import config_json
import logging
class Server():
def __init__(self):
SERVER = '10.0.100.100'
USERNAME = 'ma'
PASSWORD = '123456'
DATABASE = 'caiji'
# self.connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};ENCRYPT=yes;TrustServerCertificate=yes;UID={USERNAME};PWD={PASSWORD}'
self.connectionString = 'DRIVER=ODBC Driver 18 for SQL Server;SERVER=%s;DATABASE=%s;ENCRYPT=yes;TrustServerCertificate=yes;UID=%s;PWD=%s' %(SERVER,DATABASE,USERNAME,PASSWORD)
def updateDb(self):
conn = pyodbc.connect(self.connectionString)
cursor = conn.cursor()
cmds=codecs.open("caiji.sql","r",'utf-8').read().split(";")
for cmd in cmds:
print(cmd)
cursor.execute(cmd)
conn.commit()
cursor.close()
conn.close()
先使用的pymssql,没成功.
后来又来试pymssql 2.3.0,找到了错误原因:
conn = pymssql.connect(SERVER, USERNAME,PASSWORD, DATABASE,tds_version="7.0")
关键就是要加入tds_version这一参数。
pymssql对比pyodbc优点是不需安装驱动,而微软的驱动在旧版本系统上安装不上,这时pymssql就可派上用场。用pymssql遇到的一个问题是varchar类型字段中保存中文时乱码,要用nvarchar类型。pyodbc就没遇到这个问题。