BUG:

[SkeptiCali]

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 pymysql
import openpyxl
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.utils.dataframe import dataframe_to_rows

import imports_local.mysql_connector_ygrene as mc

conn = pymysql.connect(
        host='localhost',
        user='user1', 
        password = "password1",
        db='cali',
        cursorclass=pymysql.cursors.DictCursor
        )

df=pd.read_sql_query('SELECT * FROM sample_data LIMIT 2',conn)

print(df)

Issue Description

When using DictCursor the data returned from read_sql_query is just a dataframe of column names. If you change the cursorclass to cursor all is well. I also validated that the issues was not there in 1.5.3. The issue was first noticed in v. 2.0.0 and is there in 2.0.1

Example data output:

id value state_id county_id district_id zipcode plus4
0 id value state_id county_id district_id zipcode plus4
1 id value state_id county_id district_id zipcode plus4

Expected Behavior

id   value  state_id  county_id  district_id zipcode plus4

0 117 ABCDEF 5 12 12 95823 6312
1 163 DEFRDC 5 12 12 95814 4713

Installed Versions

INSTALLED VERSIONS

commit : 37ea63d
python : 3.11.3.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.22621
machine : AMD64
processor : Intel64 Family 6 Model 140 Stepping 1, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : English_United States.1252

pandas : 2.0.1
numpy : 1.24.2
pytz : 2023.3
dateutil : 2.8.2
setuptools : 65.5.0
pip : 23.1.2
Cython : None
pytest : None
hypothesis : None
...

[asishm]

Can you try with a sqlalchemy connectable as mentioned in the docs? Using a DBAPI2 connection for anything other than sqlite is not supported untested per the docs. Your code should also throw a UserWarning

[SkeptiCali]

I did and it worked, and it was against SQL Server. If memory serves me correct it didn't have an equivalent dictionary cursor though. I believe I got that error message with 2.0.1 and wanted to be sure that the error was due to a deprecation of functionality. The fix is to not use the dictionary cursor, and to be honest I don't know why the original author of the offending script used it and not just a regular cursor. Based on what you said I'm good with your answer. Wanted to be sure I wasn't missing something. Thanks for the very prompt reply It is greatly appreciated. Matthew Matthew

…

________________________________ From: Asish Mahapatra ***@***.***> Sent: Monday, May 1, 2023 6:40:23 PM To: pandas-dev/pandas ***@***.***> Cc: SkeptiCali ***@***.***>; Author ***@***.***> Subject: Re: [pandas-dev/pandas] BUG: (Issue #53028) Can you try with a sqlalchemy connectable as mentioned in the docs? Using a DBAPI2 connection for anything other than sqlite is not supported per the docs. Your code should also throw a UserWarning — Reply to this email directly, view it on GitHub<#53028 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ALXO6Z7AU7GDQF3GQ5MPWBDXEBQYPANCNFSM6AAAAAAXSI7QKE>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[asishm]

So this reproduces with a sqlite3 conn as well (which doesn't have a dictcursor built in - so using https://stackoverflow.com/questions/3300464/how-can-i-get-dict-from-sqlite-query)

reproducer:

import sqlite3
conn = sqlite3.connect(':memory:')

df = pd.DataFrame({'a': [1,2], 'b': [2,3]})
df.to_sql('foo', conn, index=False)

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

conn.row_factory = dict_factory

out = pd.read_sql('select * from foo', conn)

output:

In [27]: out
Out[27]:
   a  b
0  a  b
1  a  b

Issue stems from #50048
which tries to call

In [32]: lib.to_object_array_tuples([{'a': 1, 'b': 2}, {'a': 2, 'b': 3}])
Out[32]:
array([['a', 'b'],
       ['a', 'b']], dtype=object)

[SkeptiCali]

So based on your results should I just consider this deprecated behavior or a defect that needs to be fixed? Regardless, I have "work-arounds" so either way I am good.

[asishm-wk]

This is a regression