Mixing strings and input variable in sql connection string using pyodbc library - Python

Question

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

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)')

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

dragonfury2 · Accepted Answer · 2019-07-16 15:22:10Z

4

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

incorrect:

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

correct:

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

answered Jul 16, 2019 at 15:22

dragonfury2

4281 gold badge7 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gord Thompson Over a year ago

You could also use an f-string: f'DRIVER={driver};SERVER={server}; ...'

Collectives™ on Stack Overflow

Mixing strings and input variable in sql connection string using pyodbc library - Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest