pyodbc 访问 ms sqlserver

2024-06-01 08:04
sqlserver, 数据库
32人已看

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 = self.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,没成功.

ms sqlserver odbc driver下载地址

后来又来试pymssql 2.3.0,找到了错误原因：

conn = pymssql.connect(SERVER, USERNAME,PASSWORD, DATABASE,tds_version="7.0")

关键就是要加入tds_version这一参数。

pymssql对比pyodbc优点是不需安装驱动，而微软的驱动在旧版本系统上安装不上，这时pymssql就可派上用场。用pymssql遇到的一个问题是varchar类型字段中保存中文时乱码，要用nvarchar类型。pyodbc就没遇到这个问题。