SQL Server query results compressed into single column when using pyodbc

Data and Databases
1

I’m working with pyodbc to fetch data from a SQL Server database and convert it to a pandas dataframe. However, I’m running into a strange issue where multiple columns from my query get squashed into just one column.

The error message I keep seeing is:

ValueError: Shape of passed values is (18,1) indices imply (18,2)

This is weird because the same approach worked fine when I used it with Teradata connections before. It seems like the query recognizes that there should be two columns but only returns one row of data.

Here’s my current code:

connection = pyodbc.connect(connection_string)
cur = connection.cursor()
result = cur.execute("Select [Name], [Surname] from [Users] where [UserID] > 100")
users_df = pd.DataFrame(result, columns = [col[0] for col in cur.description])

When I take out the columns parameter, the dataframe shows something like:

             0
0 [Mike, Johnson]

Has anyone encountered this before? What could be causing the columns to merge together like this?

2

that’s really strange! I dont think I’ve seen pyodbc do that before. are u using diff driver versions for teradata and sql server? also, have you tried fetchall() instead of passing the cursor directly to pandas?

3

Looks like a driver issue. Try calling fetchall() first, then pass that to DataFrame instead of the cursor object. Also check if your SQL Server ODBC driver is current - older versions have weird column handling bugs.

4

I hit this same issue migrating from Oracle to SQL Server. It’s because pyodbc handles result sets differently between database engines. Don’t pass the cursor directly to pandas - fetch the data first with result.fetchall() then pass that to pandas. Also double-check your connection string has the right SQL Server driver. What happens is the cursor hasn’t been consumed properly before pandas tries to create the DataFrame, so pandas treats the whole result set as one serialized column instead of separate fields.