Neither DSN nor SERVER keyword supplied

Question

I am trying to connect to SQL database slightly different ways: with and without use of parameter. Why without use of parameters works fine, but with use of parameters - gives me an error. Did I make a syntax error? I went through each letters and couldn't see anything.

import pandas as pd
import pyodbc

#parameters:
server = 'SQLDEV'
db = 'MEJAMES'

#Create the connection
conn = pyodbc.connect('DRIVER={SQL Server};server =' + server + ';DATABASE = ' + db + ';Trusted_Connection=yes;')
# query db
sql = """

select top 10 PolicyNumber, QuoteID, ProducerName from tblQuotes

"""
df = pd.read_sql(sql,conn)
df

The statement above gives me an error

But if I do the same but without use of parameters then it works fine:

import pandas as pd
import pyodbc

#parameters:
#server = 'SQLDEV'
#db = 'MEJAMES'

#Create the connection
conn = pyodbc.connect("DRIVER={SQL Server};server=SQLDEV;database=MEJAMES;Trusted_Connection=yes;")
# query db
sql = """

select top 10 PolicyNumber, QuoteID, ProducerName from tblQuotes

"""
df = pd.read_sql(sql,conn)
df

Try removing the space after the `server` keyword, i.e., `... ;server=' + server + ...` instead of `... ;server =' + server + ...`. — Gord Thompson, Sep 05 '17 at 20:27
It worked. I cant believe python is sooo picky language. I wouldn't even think about that. Can you answer it and I'll accept as correct. Thank you very much for your help. — Serdia, Sep 05 '17 at 20:35
That would be SQLServer that's picky in this case, not Python, not that it matters.. — thebjorn, Sep 05 '17 at 20:39
@thebjorn - Actually, it's the Windows ODBC Driver Manager that's the culprit, not the SQL Server ODBC driver. The same thing will happen with any other ODBC driver. — Gord Thompson, Sep 05 '17 at 20:50
should we make a /etc/odbcinst.ini too? https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Linux — Nikhil VJ, Mar 18 '20 at 11:07

score 10 · Accepted Answer · answered Sep 05 '17 at 20:48

10

The Windows ODBC Driver Manager is quite fussy about keywords in connection strings. They must be immediately followed by the equal sign, so SERVER=... will work, but SERVER =... will not.

answered Sep 05 '17 at 20:48

Gord Thompson

116,920
32
215
418

score 2 · Answer 2 · answered Jan 11 '19 at 11:03

Its crazy but I managed to solve it by actually passing an option: extra_params: server=WHEREYOURSERVERLIVES\DBSERVER

I am using it pyodbc on django BTW.

Must be some bug.

something like this

    'ENGINE': 'sql_server.pyodbc',
    'NAME': 'YOURGREATESTDATABASE',
    'USER': 'YOURGREATESTUSERNAME',
    'PASSWORD': 'YOURGREATESTPASSWORD',
    'HOST': 'WHEREYOURSERVERLIVES\DBSERVER',
    'PORT': '',

    'OPTIONS': {
        'driver': 'ODBC Driver 17 for SQL Server',
        'extra_params': "Persist Security Info=False;server=WHEREYOURSERVERLIVES\\DBSERVER"

score 2 · Answer 3 · edited Jan 09 '20 at 12:46

2

Try this format:

This should work

connection = pyodbc.connect("DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s"
                            % (server, database, username, password))

edited Jan 09 '20 at 12:46

Lucifer

1,594
2
18
32

answered Jan 09 '20 at 12:28

Vigneshwar Ilango

21
2

I think the main misunderstanding is confusing ODBC connection strings with whatever you're used to using, thanks for this. – adudley Jul 01 '20 at 13:29

Neither DSN nor SERVER keyword supplied

3 Answers3