python 使用pymssql连接sqlserver

python 使用pymssql连接sqlserver

---

---

前言

提示：python连接sqlserver数据库。

---

# 一、安装

#官网 http://www.pymssql.org/en/latest/intro.html
pip install pymssql

二、使用步骤

1.使用

class Sqls:
    def __init__(self, host, user, password, database):
        self.conn = pymssql.connect(host=host, user=user, password=password, database=database,charset='cp936')
        self.cursor = self.conn.cursor()

    def execute(self,sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()

class sqlsToMysql:
    def main(self):
        host = "host"
        user = "user"
        password = "password"
        database = "database"
        sqls = Sqls(host,user,password,database)
        r = sqls.execute("select * from a")
        for i in r:
            print(i)
        print(r)

if __name__ == '__main__':
    sqlsToMysql().main()

2.官方例子

host = "host"
user = "user"
password = "password"
database = "RealTimeData" #库名

conn = pymssql.connect(host=host, user=user, password=password, database=database)
cursor = conn.cursor()
cursor.execute("""
IF OBJECT_ID('persons', 'U') IS NOT NULL
    DROP TABLE persons
CREATE TABLE persons (
    id INT NOT NULL,
    name VARCHAR(100),
    salesrep VARCHAR(100),
    PRIMARY KEY(id)
)
""")
cursor.executemany(
    "INSERT INTO persons VALUES (%d, %s, %s)",
    [(1, 'John Smith', 'John Doe'),
     (2, 'Jane Doe', 'Joe Dog'),
     (3, 'Mike T.', 'Sarah H.')])
# you must call commit() to persist your data if you don't set autocommit to True
conn.commit()

cursor.execute('SELECT * FROM data_current')
row = cursor.fetchone()
while row:
    # print(row["ID"], row["Name"].encode('latin-1').decode('gbk'))
    site_name = row[0].encode('latin-1').decode('gbk')
    print(row[1])
    print(f"ID={site_name}, Name={row[1]}")
    row = cursor.fetchone()

conn.close()

三.遇到的问题

UnicodeEncodeError: 'gbk' codec can't encode character '\xb8' in position 4: illegal multibyte sequence

解决

s.encode('latin-1').decode('gbk') 转换一下

或者

创建 connect的时候charset='cp936' 

---

总结