[DEBUG] python3连接sql server时遇到的问题

2021/9/2更新

Traceback (most recent call last):
  File "src/pymssql/_pymssql.pyx", line 646, in pymssql._pymssql.connect
  File "src/pymssql/_mssql.pyx", line 2126, in pymssql._mssql.connect
  File "src/pymssql/_mssql.pyx", line 716, in pymssql._mssql.MSSQLConnection.__init__
  File "src/pymssql/_mssql.pyx", line 1835, in pymssql._mssql.maybe_raise_MSSQLDatabaseException
  File "src/pymssql/_mssql.pyx", line 1852, in pymssql._mssql.raise_MSSQLDatabaseException
pymssql._mssql.MSSQLDatabaseException: (17892, b"Logon failed for login 'XXX' due to trigger execution.DB-Lib error message 20018, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (X.X.X.X)\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (X.X.X.X)\n")

一直正常登录的语句忽然产生这个报错。it说是因为：

EVENTDATA function有可能会造成python登录出错

暂时记录，之后研究下这个。

---

首先，请使用pymssql！！！！不要用pymysql！！！我真是个大傻*！！！

安装

pip install pymssql

使用

db = pymssql.connect(server='这是ip地址',port='1433',user='用户名',password='密码',database='数据库名字', charset='utf8')
cursor = db.cursor()
sql = "insert into 表名(Uid, Num, Status, Created) values(NEWID(), \'%s\', \'%s\',GETDATE());" % (oid, ch)
cursor.execute(sql)
db.commit()

执行方法跟pymysql是一样的，但是sql server有更严格的格式，比如语句必须是双引号包围。

另外，我看到有中文时需要encode，不过我插入的时候不编码也可以正确插入。

sql2 = sql.encode("utf8")
cursor.execute(sql2)
db.commit()