Python读取config.ini配置无法连接SQL Server，硬编码可正常连接

无法正常连接的代码 #

import pyodbc
import configparser

# 读取INI文件
config = configparser.ConfigParser()
config.read('config.ini')
server = config['SQL']['Server']
database = config['SQL']['Database']
username = config['SQL']['Username']
password = config['SQL']['Password']

# 建立SQL Server连接
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

# 开启自动提交
cnxn.autocommit = True

# 创建数据库
cursor = cnxn.cursor()
cursor.execute('CREATE DATABASE [DB Test TCPOS]')

# 创建表
cursor.execute('USE [DB Test TCPOS 2]')

# 表1
cursor.execute('CREATE TABLE [SHOP] ([SHOP_ID] INT NOT NULL, [SHOP_NAME] NVARCHAR(50), [TIMESTAMP] DATETIME)')
# 表2
cursor.execute('CREATE TABLE [OPERATOR] ([OPERATOR_ID] INT NOT NULL, [OPERATOR_NAME] NVARCHAR(50), [TIMESTAMP] DATETIME)')

# 关闭连接
cnxn.close()

print('数据库和表创建成功。')

config.ini文件内容 #

[SQL]
Server = 'ZDEVKWS0148\\SQLEXPRESS'
Database = 'master'
Username = 'test_user'
Password = 'test_1'

错误信息 #

第13行，在
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBMSLPCN]SQL Server不存在或访问被拒绝。(17) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionOpen (Connect()). (67)')

可正常运行的代码 #

import pyodbc

# 建立SQL Server连接
server = 'ZDEVKWS0148\\SQLEXPRESS'
database = 'master'
username = 'test_user'
password = 'test_1'
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

# 开启自动提交
cnxn.autocommit = True

# 创建数据库
cursor = cnxn.cursor()
cursor.execute('CREATE DATABASE [DB Test TCPOS 2]')

# 创建表
cursor.execute('USE [DB Test TCPOS 2]')

# 表1
cursor.execute('CREATE TABLE [SHOP] ([SHOP_ID] INT NOT NULL, [SHOP_NAME] NVARCHAR(50), [TIMESTAMP] DATETIME)')
# 表2
cursor.execute('CREATE TABLE [OPERATOR] ([OPERATOR_ID] INT NOT NULL, [OPERATOR_NAME] NVARCHAR(50), [TIMESTAMP] DATETIME)')

# 关闭连接
cnxn.close()

print('数据库和表创建成功。')

问题原因及解决方法 #

问题出在config.ini文件里的参数值带了单引号。比如Server = 'ZDEVKWS0148\\SQLEXPRESS'，用configparser读取后，server变量的值会包含前后单引号，而硬编码时server的值没有单引号，这导致连接字符串里多了不必要的单引号，从而触发连接错误。

解决方法有两种：

1. 修改config.ini，去掉所有参数值的单引号：

[SQL]
Server = ZDEVKWS0148\\SQLEXPRESS
Database = master
Username = test_user
Password = test_1

2. 保持config.ini不变，读取后去除参数值的单引号：

server = config['SQL']['Server'].strip("'")
database = config['SQL']['Database'].strip("'")
username = config['SQL']['Username'].strip("'")
password = config['SQL']['Password'].strip("'")

内容的提问来源于stack exchange，提问作者DarkKnight22