Python - AttributeError: 'NoneType' object has no attribute 'cursor'

Question

I have the following code:

import pyodbc

conn = pyodbc.connect('DSN=QueryBuilder')
cursor = conn.cursor()
stringA = "SELECT GrantInformation.Call FROM GrantInformation"
cursor.execute(stringA)
rows = cursor.fetchall()

Its worked fine for years, but all of a sudden it has stopped working today with the following traceback:

Traceback (most recent call last):
  File "C:/Users/nicholas/Desktop/test.py", line 6, in <module>
    cursor = conn.cursor()
AttributeError: 'NoneType' object has no attribute 'cursor'

The code still works in Jupyter Notebook, but no longer works outside i.e. in Pycharm/IDLE

If I run in Pycharm:

print(pyodbc.dataSources())
print(pyodbc.drivers())

I get:

none
none

If I do it in Jupyter Notebook, I get:

{'Visio Database Samples': 'Microsoft Access Driver (*.mdb, *.accdb)', 'dBASE Files': 'Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)', 'Excel Files': 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)', 'MS Access Database': 'Microsoft Access Driver (*.mdb, *.accdb)', 'QueryBuilder': 'SQL Server', 'QueryBuilder_Beta': 'SQL Server', 'MIDAS': 'SQL Server', 'Querybuilder_YearEnd': 'SQL Server', 'ResearchFish': 'SQL Server', 'ESRCContacts': 'SQL Server', 'ESRCContactsDEV': 'SQL Server', 'Sample Amazon Redshift DSN': 'Amazon Redshift (x64)'}

['SQL Server', 'SQL Server Native Client 10.0', 'Amazon Redshift (x64)', 'SQL Server Native Client 11.0', 'PostgreSQL ANSI(x64)', 'PostgreSQL Unicode(x64)', 'ODBC Driver 11 for SQL Server', 'ODBC Driver 13 for SQL Server']

I am losing the plot, as I rely on this code a lot for doing all my work, but nothing I am doing is fixing the issue i.e. reinstalling Python, pyodbc etc

Answer 1

conn is None, so you must have failed to acquire a connection. Presumably the notebook where this is working has "QueryBuilder" set up correctly, and wherever else you have tried to use this does not. I'm not familiar with that name specifically, but you may need to set up the data source, or you may need to install the appropriate ODBC driver.

You should be able to look at pyodbc.dataSources() and/or pyodbc.drivers() to get more details about what data sources are configured and what drivers are installed, and see what the difference is between your systems.

Answer 2

Surprised no one has noticed this, but it appears that there are issues with Python 3.6 and pyodbc... I have gone back to 3.5 and it works fine.