Python Exception No Description with Pandas read_sql

Question

Using Python, I'm trying to read a table from SQL Server and then insert the data into a table in Access. The best way I've found to do this is using the pandas dataframe. I wrote up a program that reads a SQL Server table into a dataframe like so:

dataframe = pandas.read_sql(selectSql, srcConn)

And it works great on a ~209MB table. When I try it on a ~1,116MB table it throws an exception with no description. I'm guessing it has to do with the size of the table it's reading in (it would be nice if it said that). I know Access can only hold 2GB but there is plenty of room left in it and it doesn't even get to the part where it writes to Access before throwing the error.

Is there any way to fix this for larger tables? Is there a better way I should be copying tables from SQL Server 2008 R2 to Access 2016 using Python? I have 16GB of RAM on Win10 64-bit so that shouldn't be a problem. I've tried 32-bit Python 3.7 and 64-bit Python 3.6 to no avail. I tried SSIS first but it crashes my entire Visual Studio whenever I try to open a package with a connection to Access.

UPDATE:

I followed Gord's advice below and now my code looks like this:

access_cnxn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=' + access_db + ';'
)
sqls_cnxn_str = (
    r'DRIVER=ODBC Driver 13 for SQL Server;'
    r'SERVER=' + sqls_server + ';'
    r'DATABASE=' + sqls_db + ';'
    r'UID=' + sqls_username + ';'
    r'PWD=' + sqls_password + ';'
)

This connection works by itself:

sqls_cnxn = pyodbc.connect(sqls_cnxn_str)

And this connection works by itself:

pyodbc.pooling = False
access_cnxn = pyodbc.connect(access_cnxn_str, autocommit = True)

But this is throwing an error:

access_cnxn.execute(f"SELECT * INTO {access_table} FROM [ODBC;{sqls_cnxn_str}].{sqls_table}")

The error thrown:

Message=('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'ODBC Driver 13 for SQL ServerSERVERNAME' failed. (-2001) (SQLExecDirectW)")
Source=C:\Users\bruescm\source\repos\DB_Test\DB_Test\SyncAllTests.py
StackTrace: File "C:\Users\bruescm\source\repos\DB_Test\DB_Test\SyncAllTests.py", line 57, in sync_table dest_cnxn.execute(f"SELECT * INTO {access_table} FROM [ODBC;{sqls_cnxn_str}].{sqls_table}") File "C:\Users\bruescm\source\repos\DB_Test\DB_Test\SyncAllTests.py", line 121, in main sync_table('', sqls_table, get_access_cnxn(), access_table) File "C:\Users\bruescm\source\repos\DB_Test\DB_Test\SyncAllTests.py", line 124, in main()

SERVERNAME in the error is the name of the server on which SQL Server resides. Not sure why it jammed it up against the driver name in the error.

Any ideas?

UPDATE 2:

It turns out my Access is 32-bit. This still doesn't explain why it won't connect as I was originally using Python 3.7 32-bit.

Thanks.

have you tried doing this in batches as opposed to blowing up your memory in one shot — gold_cy
– gold_cy, Commented Jan 29, 2019 at 18:53
@aws_apprentice I hadn't tried batches yet but will give that a shot if I can't get Gord Thompson's answer to work. Thanks. — Michael Bruesch
– Michael Bruesch, Commented Jan 29, 2019 at 20:03
Double-check that you are calling pyodbc.pooling = False before opening the Access connection. — Gord Thompson
– Gord Thompson, Commented Jan 30, 2019 at 16:38
@GordThompson Yes I am setting pyodbc.pooling = False before establishing connection to Access. My Access is 32-bit (even though on a 64-bit Win10 machine) and I'm using Python 3.7 32-bit. — Michael Bruesch
– Michael Bruesch, Commented Jan 30, 2019 at 21:44

Gord Thompson · Accepted Answer · 2019-01-29 23:09:21Z

1

I was able to get the Access Database Engine to pull a table from SQL Server and create a copy in the Access database by simply doing

pyodbc.pooling = False  # required
cnxn = pyodbc.connect("DSN=myAccessDb", autocommit=True)
cnxn.execute("SELECT * INTO access_tbl FROM [ODBC;DSN=SQLmyDb].sql_server_tbl")

where SQLmyDb is the ODBC DSN for my SQL Server instance.

Update

Just tested to confirm that DSN-less connection strings also work:

pyodbc.pooling = False  # required
access_cnxn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\__tmp\test.accdb;'
)
cnxn = pyodbc.connect(access_cnxn_str, autocommit=True)
sql_cnxn_str = (
    r'DRIVER=ODBC Driver 17 for SQL Server;'
    r'SERVER=(local)\SQLEXPRESS;'
    r'DATABASE=myDb;'
    r'Trusted_Connection=Yes;'
)
cnxn.execute(f"SELECT * INTO access_tbl FROM [ODBC;{sql_cnxn_str}].sql_server_tbl")

edited Jan 29, 2019 at 23:09

answered Jan 29, 2019 at 19:30

Gord Thompson

125k39 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Michael Bruesch Over a year ago

I am initializing my connection like so: conn = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + accessDb). Should I create a DSN instead? Do you need to put the full path to the Access DB in the connect string? Does the DSN need a full path also?

Michael Bruesch Over a year ago

Sorry, I'm also connecting to SQL Server without a DSN like so: 'DRIVER=SQL Server;SERVER=' + server + ';UID=' + username + ';PWD=' + password + ';DATABASE=' + database

Gord Thompson Over a year ago

DSNs take almost no time to set up, so I'd suggest using them to see if the general approach will work for you. If so, then you can investigate whether you can use DSN-less connection strings. (I'm sure you can for the Access connection string, not so sure about the embedded SQL Server connection string.)

Michael Bruesch Over a year ago

Thanks so much @Gord Thompson! I’ll try your updated solution when I’m back at work tomorrow.

Michael Bruesch Over a year ago

I tried your new solution with DSN-less connection and the SELECT INTO query fails to connect to SQL Server. I updated my question above. Any help would be greatly appreciated!

Collectives™ on Stack Overflow

Python Exception No Description with Pandas read_sql

1 Answer 1

Update

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related