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

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

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.0DRIVERS............: /usr/local/etc/odbcinst.iniSYSTEM DATA SOURCES: /usr/local/etc/odbc.iniFILE DATA SOURCES..: /usr/local/etc/ODBCDataSourcesUSER DATA SOURCES..: /usr/local/etc/odbc.iniSQLULEN Size.......: 8SQLLEN Size........: 8SQLSETPOSIROW 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.

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

$cat /home/ec2-user/.odbc.ini[DSNNAME]Driver =FreeTDSDescription=descriptionServer =serverpathPort =1433Database =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 passwordchecking 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 ... OKchecking odbc.ini files    reading /home/ec2-user/.odbc.ini[DSNNAME] found in /home/ec2-user/.odbc.inifound this section:    [DSNNAME]    Driver =FreeTDS    Description=description    Server =serverpath    Port =1433    Database =dbnamelooking for driver for DSN [DSNNAME] in /home/ec2-user/.odbc.ini  no driver mentioned for [DSNNAME] in .odbc.inilooking for driver for DSN [default] in /home/ec2-user/.odbc.iniosql: error: no driver found for [DSNNAME] in .odbc.ini

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