Pyodbc connection to SQL Server failed - neither DSN nor SERVER keyword supplied

Question

I am trying to make an insertion to my SQL Server database by using pyodbc. But I can never achieve it. Please help me.

By the way; I defined index_pred_as_int in previous code snippet.

My code:

import pyodbc 

server = 'DESKTOP-T7OFQV6\SQLEXPRESS1'
database = 'VidgaEmotionRecognition'

#defining connection string
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; \\\
                       SERVER=' + server + '; \\\
                       DATABASE=' + database +';\\\
                       Trusted_Connection=yes;')

#creating the connection cursor
cursor = cnxn.cursor()

#defining insert query
insert_query='''INSERT INTO FaceEmotion (emotion)
                VALUES (?);'''

#defining insertion value
values = index_pred_as_int

#insert the data
cursor.execute(insert_query, values)

#commit the insertion
cnxn.commit()

#grab the database table values
cursor.execute('SELECT * FROM FaceEmotion')

#printing the results
for values in cursor:
    print(values)

And here's the error:

OperationalError

Traceback (most recent call last)

in
18 #defining connection string
---> 19 cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; \\
20 SERVER=' + server + '; \\
21 DATABASE=' + database +';\\

OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Neither DSN nor SERVER keyword supplied (0) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0)')

scotscotmcc scotscotmcc · Accepted Answer · 2021-05-08T16:14:25

I'm not sure I understand how the \\\ are supposed to work, but I think they are breaking it. I set up a similar connection on my computer and used your same connection string and got the same error. When I put the whole connection string on one line, it seems to work just fine.

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server + ';DATABASE=' + database +';Trusted_Connection=yes')

Edit: it also appears to work just fine if you use just one \ to divide your line, instead of that triple.

Pyodbc connection to SQL Server failed - neither DSN nor SERVER keyword supplied

1 Answers