使用pyodbc库在SQL连接字符串中混合字符串和输入变量-Python [英] Mixing strings and input variable in sql connection string using pyodbc library - Python

问题描述

我环顾四周，找不到这个特定问题.

I've had a look around SO and couldn't find this particular issue.

因此，我有一个ext config.txt文件，该文件用于获取存储在python程序中的变量中的值.我在python中有一个以字典形式存储key:values的变量.(其想法是采用配置设置并从程序执行sql服务器查询)

So I have an ext config.txt file which is used to obtain values which are stored in variables in the python program. I have a variable in the python that stores the key:values in dictionary form. (the idea is it takes the config settings and performs a sql server query from the program)

我的代码如下所示(还显示了打印语句的输出):

my code looks like this (also showing output of the print statements):

driver = config['DRIVER']
server = config['SERVER']
database = config['DATABASE']
trusted = config['Trusted_Connection']

print(driver) # = {ODBC Driver 17 for SQL Server};
print(server) # = server1;
print(database) # = db1;
print(trusted) # = yes

#1. working code
sql_conn = odbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; SERVER=server1; DATABASE=db1;   Trusted_Connection=yes')

#2. non working code
sql_conn = odbc.connect('\''DRIVER='+ str(driver) +  ' SERVER=' + str(server) + ' DATABASE=' + str(database)  +  ' Trusted_Connection='+ str(trusted)+'\'') 

当我尝试运行第一行时，一切都会按预期进行.但是，当我尝试第二行时，我得到:

When I try to run the first line, everything works as expected. However when I try with the second line I get:

pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

这与将dict转换为字符串有关吗?或也许与pyodbc?

Is this something to do with the conversion of dict to strings? or perhaps with pyodbc?

推荐答案

所以我设法找到了修复程序，并且看起来像是解析的字符串出错:

So I managed to find the fix and looks like it was an error with the parsed string:

不正确:

odbc.connect('\''DRIVER='+ str(driver) +  ' SERVER=' + str(server) + ' DATABASE=' + str(database)  +  ' Trusted_Connection='+ str(trusted)+'\'')

正确:

odbc.connect('DRIVER='+driver+';SERVER='+server+';DATABASE='+database+';Trusted_Connection='+trusted) 

这篇关于使用pyodbc库在SQL连接字符串中混合字符串和输入变量-Python的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！