Pyodbc - "Data source name not found, and no default driver specified"

Asked 29/4, 2013 at 13:47 Answered 23/4 at 13:37

Solved python postgresql python-2.7 pyodbc unixodbc

I have trouble getting pyodbc work. I have unixodbc , unixodbc-dev, odbc-postgresql, pyodbc packages installed on my Linux Mint 14. I am losing hope to find solution on my own, any help appreciated. See details below:

Running:

>>> import pyodbc
>>> conn = pyodbc.connect("DRIVER={PostgreSQL};SERVER=localhost;DATABASE=test;USER=openerp;OPTION=3;")

Gives me:

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

# odbcinst -j gives:

unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/atman/.odbc.ini
SQLULEN Size.......: 4
SQLLEN Size........: 4
SQLSETPOSIROW Size.: 2

Which makes me think there is a unixodbc configuration problem. Here are my unixodbc config file contents:

File /etc/odbcinst.ini:

[PostgreSQL ANSI]
Description     = PostgreSQL ODBC driver (ANSI version)
Driver      = psqlodbca.so
Setup       = libodbcpsqlS.so
Debug       = 0
CommLog     = 1
UsageCount      = 2

[PostgreSQL Unicode]
Description     = PostgreSQL ODBC driver (Unicode version)
Driver      = psqlodbcw.so
Setup       = libodbcpsqlS.so
Debug       = 0
CommLog     = 1
UsageCount      = 2

File /etc/odbc.ini :

[PostgreSQL test]
Description         = PostgreSQL 
Driver              = PostgreSQL ANSI
Trace               = No
TraceFile           = /tmp/psqlodbc.log
Database            = template1
Servername          = localhost
UserName            =
Password            =
Port                =
ReadOnly            = Yes
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No
ConnSettings        =

File ~/.odbc.ini:

[DEFAULT]
Driver = PostgreSQL

[PostgreSQL]
Description         = Test to Postgres
Driver              = PostgreSQL
Trace               = Yes
TraceFile           = sql.log
Database            = nick
Servername          = localhost
UserName            =
Password            =
Port                = 5432
Protocol            = 6.4
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No
ConnSettings        =

Astri answered 29/4, 2013 at 13:47 Comment(1)

You have try this: …you might have to copy /usr/local/etc/odbc.ini from /etc/odbc.ini and /usr/local/etc/odbcinst.ini from /etc/odbcinst.ini (at least this was my experience, possibly because unixODBC was already installed with my linux distribution) – Lauter 27/12, 2016 at 9:53

I believe the answer to your problem is that in your ~/.odbc.ini file you are saying to use driver PostgreSQL - but you have not defined that driver in your /etc/odbcinst.ini file. Try changing PostgreSQL to PostgreSQL ANSI or PostgreSQL Unicode (both of which are defined in /etc/odbcinst.ini).

Radiometeorograph answered 29/4, 2013 at 14:16 Comment(6)

Could you please type an example here? Should I literally replace "PostgreSQL" with "PostgreSQL Unicode" or replace with "psqlodbcw.so"? – Astri 29/4, 2013 at 14:36

Try literally replacing PostgreSQL with PostgreSQL Unicode or PostgreSQL ANSI. – Radiometeorograph 29/4, 2013 at 14:51

Tried that, and also added full paths to driver files in /etc/odbcinst.ini, and no effect, still the same... – Astri 29/4, 2013 at 14:56

Get rid of your ~/.odbc.ini and change your connection string to conn = pyodbc.connect("DRIVER={PostgreSQL test};SERVER=localhost;DATABASE=test;USER=openerp;OPTION=3;") – Radiometeorograph 29/4, 2013 at 15:1

You was completely right before, my mistake was executing the same Python statement (had to change DRIVER={PostgreSQL} to DRIVER={PostgreSQL Unicode}), and everything works fine. Thank you very much. – Astri 29/4, 2013 at 15:8

Thanks, @BennyHill. Your conversation helps me solve my issue. – Thromboembolism 31/10, 2018 at 11:11

For me, the issue was the actual location of my odbc.ini and odbcinst.ini files.

On many systems, the install location of these files is in /etc/

However, in my case, these files were located under /usr/local/etc/

The could be determined by typing
odbcinst -j

Which yielded:

unixODBC 2.3.0
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /usr/local/etc/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

My odbc.ini files already exists in /etc, so the solution was to copy them over over from /etc/ to /usr/local/etc/

cp /etc/odbc.ini /etc/odbcinst.ini /usr/local/etc/

Edit: It's also worth noting that the path outputted by the odbcinst -j command can change depending on using sudo or not.

Eme answered 12/2, 2015 at 21:57 Comment(1)

Thanks for this, but I have a question. This fix work for me, even though I was using os.environ["ODBCINI"]="/etc/odbc.ini" os.environ["ODBCSYSINI"]="/etc/odbcinst.ini" in my code. It seem like somehow, since /etc/<odbc files> were owned by root (as this was the location shown when running **sudo** odbcinst -j), the files were being ignored by the program (not running as root). This seems weird to me since the odbc files have public read access. Do you know what is going on with that? – Peba 14/2, 2018 at 19:9

For me, it was all down to a single whitespace character.

$cat /home/ec2-user/.odbc.ini
[DSNNAME]
Driver =FreeTDS
Description=description
Server =serverpath
Port =1433
Database =dbname

Gave me the “Data source name not found, and no default driver specified” error.

Removing all the whitespaces before the '=' character made it work.

On a secondary note, using osql for DSN connection testing gives you a much more verbose description of any errors. It helped me a lot in the process.

$ osql -S DSNNAME -U username -P password
checking shared odbc libraries linked to isql for default directories...
    trying /txM ... no
    trying /tmp/sql ... no
    trying /tmp/sql ... no
    trying /w}H ... no
    trying /usr/loc ... no
    trying /tmp/sql.log ... no
    trying /home ... no
    trying /.odbc.ini ... no
    trying /usr/local/etc ... OK
checking odbc.ini files
    reading /home/ec2-user/.odbc.ini
[DSNNAME] found in /home/ec2-user/.odbc.ini
found this section:
    [DSNNAME]
    Driver =FreeTDS
    Description=description
    Server =serverpath
    Port =1433
    Database =dbname

looking for driver for DSN [DSNNAME] in /home/ec2-user/.odbc.ini
  no driver mentioned for [DSNNAME] in .odbc.ini
looking for driver for DSN [default] in /home/ec2-user/.odbc.ini
osql: error: no driver found for [DSNNAME] in .odbc.ini

Comparing the error message against my ini file made triaging the issue a lot easier.

Lassie answered 15/11, 2015 at 18:27 Comment(0)

For others troubleshooting this same generic error, ensure that you didn't accidentally add extraneous characters at the top of your odbc.ini file - which causes it to be invalid and all data-source declarations to be silently ignored (the error giving no indication of the specific problem).

Inattention answered 22/9, 2016 at 18:19 Comment(0)

I have the same problem. It turns out that the data source file missed: /etc/ODBCDataSources. Just touch that file and it works.

Apprehend answered 12/12, 2017 at 9:49 Comment(1)

what file should be in this dictionary? – Teazel 21/6, 2022 at 15:11

For me, I found this problem was due to mixing 32-bit and 64-bit binaries. All I can say in regards to fixing that is "Good luck, and try to keep it all 32 or all 64." 😓

Explanation, since it was asked for:

Basically, we bundled the app with a 32 bit binary that we were using. We had no way to get a 64 bit version of that proprietary binary (we used and distributed it with their permission ofc). The binary file was a .dll file.

If a 64 bit binary (such as an .exe) imports a .dll it assumes things about where the runnable code and other assets (like strings) are inside of that binary. When a 64 bit imports a 32 bit, and the 64 bit hasn't been properly configured, it will assume the wrong location as an instruction takes up a different amount of space, and is therefore a different number of bits into the file.

It can also be problematic if the 32 bit binary shares any memory with the 64 bit (which it does, for sure) as even basic types like numbers can vary in size (in bits) between the two.

I'd go into more detail about my specific scenario, but it was closed-source software, I'm not on the team anymore, and it seems sortof trade-secret-ish.

Olympic answered 26/2, 2021 at 16:23 Comment(0)

In my scenario, I needed to establish a connection with MSSQL on my CentOS server.

My connection string:

cnxn_str = ("Driver={SQL Server xxx};"
            "Server=xx.xxx.xx.xx;"
            "Database=xxxxxx;"
            "UID=yyyyyy;"
            "PWD=yyyyy;")

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

Here's how I fixed it:

Installed curl, unixODBC, unixODBC-devel, and msodbc:

yum install -y curl unixODBC unixODBC-devel
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
ACCEPT_EULA=Y yum install -y msodbcsql17

Updated the connection string driver name in my Python code:

Driver={ODBC Driver 17 for SQL Server}

Cauliflower answered 23/4 at 13:37 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Explanation, since it was asked for:

Recommended topics

Hot tags