Why was the column information stripped away in SA 1.4.20 that existed in 1.3.20?

[Terrence-Monroe: Brannon]

Creating a pandas dataframe that contained descriptive column names formerly was as easy as:

result_set = session.query(cls.column_1)

df = pandas.Dataframe(result_set)

print df.column_1

but while this works in 1.3.20, in later versions of SA such as 1.4.19,  there is not enough column info supplied by SA to form the same symbolic column names. Thus, the above code would fail when attempting to access df.column_1 in SA 1.4.19 but not fail in SA 1.3.20.

Anyway to supply a flag to query() to provide the extra column that existed in older SA versions?

[Gord Thompson]

One option would be to replace

result_set = session.query(cls.column_1)

with

result_set = session.execute(select(cls.column_1)).mappings().all()

[Mike Bayer]

On Tue, Aug 31, 2021, at 3:10 PM, Terrence-Monroe: Brannon wrote:

Creating a pandas dataframe that contained descriptive column names formerly was as easy as:

result_set = session.query(cls.column_1)

df = pandas.Dataframe(result_set)

print df.column_1

but while this works in 1.3.20, in later versions of SA such as 1.4.19,  there is not enough column info supplied by SA to form the same symbolic column names. Thus, the above code would fail when attempting to access df.column_1 in SA 1.4.19 but not fail in SA 1.3.20.

not familiar with this change, nothing should have changed with how session.query() returns rows and this may be some incompatibility with pandas.  Upgrade to the latest Pandas and make sure the behavior remains, then please provide an MCVE, thanks

Anyway to supply a flag to query() to provide the extra column that existed in older SA versions?

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/2664002d-25cc-450d-bc51-8652a7df3d53n%40googlegroups.com.

[Gord Thompson]

With version 1.3:

Base = declarative_base()


class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True)
name = Column(String)


Base.metadata.create_all(engine)

print(pd.__version__) # 1.3.2
print(sa.__version__) # 1.3.24

session = Session(engine)
session.add_all([User(name="Gord"),User(name="Bob"),])
session.commit()
result_set = session.query(User.id, User.name)
df = pd.DataFrame(result_set)
print(df)
"""
id name
0 1 Gord
1 2 Bob
"""

With version 1.4:

print(pd.__version__) # 1.3.2
print(sa.__version__) # 1.4.23

session = Session(engine)
session.add_all([User(name="Gord"),User(name="Bob"),])
session.commit()
result_set = session.query(User.id, User.name)
df = pd.DataFrame(result_set)
print(df)
"""
0 1
0 1 Gord
1 2 Bob
"""

[Federico Caselli]

Hi,

This is a pandas bug, not an sqlalchemy ones. It was already reported here https://github.com/pandas-dev/pandas/issues/40682

[Terrence-Monroe: Brannon]

Just for completeness I'm linking to source code to reproduce the problem:

play/sqlalchemy-and-pandas at main · metaperl/play (github.com)

Looks like the 1.4 release of pandas will remedy this problem.

[Gord Thompson]

Another workaround would be …

df = pd.read_sql_query(select(User.id, User.name), engine)

print(df)
"""
  id  name
0  1  Gord
1  2   Bob
"""

… although it does produce a couple of RemovedIn20Warning messages (that will probably be resolved once pandas does SQLA 1.4 better).

[Mike Bayer]

this is then the diffeence between tuples and dicts

call pandas like this:

DataFrame(result.mappings())

that will give it a series of dicts rather than tuples for rows

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/07354ac2-9e1c-4460-a3f9-3d376a99ea03n%40googlegroups.com.