BUG: read_sql_query duplicates column names in cells in pandas v2.0.0

[raj-patra]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
import pymssql

conn = pymssql.connect(user=user, password=password, host=host, database=db, as_dict=True, autocommit=True)

result_set = pd.read_sql_query("select * from table", conn)
print(result_set.head())

Issue Description

In the latest version of pandas (v2.0.0), the read_sql_query function does not seem to work.

I am using a pymssql connection and when executing a query, the result set is fetched and the number of rows are intact but the column names are duplicated in the cell values.

The functionality works as expected in the v1.5.3 version

The result set looks like this with v2.0.0 (Unexpected Behavior)

Column 1	Column 2	Column 3
Column 1	Column 2	Column 3
Column 1	Column 2	Column 3
Column 1	Column 2	Column 3

Expected Behavior

Expected Behavior

Column 1	Column 2	Column 3
1	4	7
2	5	8
3	6	9

Installed Versions

INSTALLED VERSIONS

commit : 478d340
python : 3.10.5.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.22000
machine : AMD64
processor : Intel64 Family 6 Model 142 Stepping 12, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : English_India.1252

pandas : 2.0.0
numpy : 1.24.2
pytz : 2022.7.1
dateutil : 2.8.2
setuptools : 58.1.0
pip : 23.0.1
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : 3.0.9
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : None
pandas_datareader: None
bs4 : 4.12.0
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : 3.1.2
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : None
snappy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : 2.0.1
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None

[mroeschke]

Could you give a reproducible example using sqllite ":memory:" database?

[raj-patra]

Looks like the bug is not reproducible in sqllite connections.
I tested with sqllite memory database, and the result was an expected behavior.

My guess is that any DBAPI2 connection other than sqllite will have the same unexpected behavior.

import sqlite3
import pandas as pd

conn = sqlite3.connect('file:cachedb?mode=memory&cache=shared')

df = pd.DataFrame([{"A":1,"B":2}, {"A":3,"B":4}, {"A":1,"B":2}, {"A":3,"B":4}])
print(df)

df.to_sql("test", conn, if_exists="replace")

resp = pd.read_sql("select * from test", conn)
print(resp)

[phofl]

You can also provide a reproducible example for mysql/postgress

[raj-patra]

Okay, so after an eternity of digging in, I found out that the issue is with the as_dict=True parameter of the package pymssql

With the following connection, pandas v2.0.0 gives unexpected results as described in the description.

conn = pymssql.connect(user=user, password=password, host=host, database=db, as_dict=True, autocommit=True)

With the following connection, pandas v2.0.0 gives expected results as described in the description.

conn = pymssql.connect(user=user, password=password, host=host, database=db, autocommit=True)

I have no clue as to why that might affect the result set but now we know what exactly is the issue.

I will leave it up to the maintainers to decide if we have to make any changes to the pandas code or mention it somewhere in the documentation that the as_dict=True will result in unexpected results.

I rest my case. ✌️

[raj-patra]

I can add few more examples for what works and what does not...

WORKS

conn = pymssql.connect(user=user, password=password, host=host, database=db, as_dict=True, autocommit=True)
with conn.cursor() as cursor():
    cursor.execute(QUERY)
    res = pd.DataFrame(cursor.fetchall())

conn = pymssql.connect(user=user, password=password, host=host, database=db, autocommit=True)
res = pd.read_sql_query(QUERY, conn)

DOESN'T WORK

conn = pymssql.connect(user=user, password=password, host=host, database=db, autocommit=True)
with conn.cursor() as cursor():
    cursor.execute(QUERY)
    res = pd.DataFrame(cursor.fetchall())

conn = pymssql.connect(user=user, password=password, host=host, database=db, as_dict=True, autocommit=True)
res = pd.read_sql_query(QUERY, conn)

[raj-patra]

@phofl @mroeschke
Any updates on this?

Is the team gonna look at the issue?
Let me know if there is any more clarification needed.

[phofl]

Investigations are welcome

[raj-patra]

@phofl

Upon some investigation, I found out that the bug might be in the following function:

pandas/pandas/io/sql.py

Line 166 in bd5ed2f

def _wrap_result(

In version v1.5.x, the _wrap_result function directly wrapped list of dicts into a dataframe with the from_records function,

pandas/pandas/io/sql.py

Line 146 in 2e218d1

frame = DataFrame.from_records(data, columns=columns, coerce_float=coerce_float)

But with version v2.0.0 it calls a newly added function, _convert_arrays_to_dataframe in the following line:

pandas/pandas/io/sql.py

Line 176 in bd5ed2f

frame = _convert_arrays_to_dataframe(data, columns, coerce_float, dtype_backend)

This function uses lib.to_object_array_tuples function which according to its documentation does the following
"Convert a list of tuples into an object array. Any subclass of tuple in rows will be casted to tuple."

pandas/pandas/io/sql.py

Line 148 in bd5ed2f

content = lib.to_object_array_tuples(data)

What it fails to consider is the data that this lib function receives might not be a list of tuples but instead it could be list of dicts. Therefore, it fails to return correct data when a list of dicts is passed to it that comes from the cursor.fetchall() here:

pandas/pandas/io/sql.py

Line 2303 in bd5ed2f

result = cur.fetchall()

I am almost certain this occurs in the SQLiteDatabase class which is also a fallback when SQL Alchemy connector is missing. (for example, DBAPI2 connections with result sets as lists of dicts)

Let me know if this paints a correct picture because I have never done this before. I hope this helps. 🙂