Could not locate column in row for column

[Igal Kreimer]

Hello sqlalchemy team. 

im a new user to the alchemy, and doing the tutorial on ur site. 

ran ur tutorial on adding information to databases and querying against a virtual db (sqlite in memory)

after i thought i got that, i tried testing against a real db - mysql db. 

im using the driver of pymysql version 0.4 (i know its kinda old, but when was using newer version i had other bugs, not related to this topic).

my code is as follows:

import sqlalchemy

from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

from CpuTable import CpuTable

def read_from_DB():

    engine = create_engine('mysql+pymysql://***@***/test', echo=True)

    Session = sessionmaker(bind=engine)

    session=Session()

#    insert = CpuTable(id='15',name= 'igal')

#    session.add(insert)

#    session.commit()

    print()

    print(str(session.query(CpuTable.id.label('id')).all()))

    for cpu_id,cpu_name in session.query(CpuTable).all():

        print(cpu_id,cpu_name)

my stack trace is as follows:

C:\Python33\python.exe J:/working_dir/TLM/lib/DB_Wrapper/Lab_DB/DB_Reader.py

2014-02-12 11:23:57,193 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()

2014-02-12 11:23:57,193 INFO sqlalchemy.engine.base.Engine ()

2014-02-12 11:23:57,196 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'character_set%%'

2014-02-12 11:23:57,196 INFO sqlalchemy.engine.base.Engine ()

2014-02-12 11:23:57,198 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'

2014-02-12 11:23:57,198 INFO sqlalchemy.engine.base.Engine ()

2014-02-12 11:23:57,199 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2014-02-12 11:23:57,199 INFO sqlalchemy.engine.base.Engine SELECT cpu.id AS id 

FROM cpu

2014-02-12 11:23:57,199 INFO sqlalchemy.engine.base.Engine ()

Traceback (most recent call last):

  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py", line 69, in __getitem__

KeyError: <sqlalchemy.sql.elements.Label object at 0x0000000003C6FDA0>

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

  File "J:/working_dir/TLM/lib/DB_Wrapper/Lab_DB/DB_Reader.py", line 39, in <module>

    read_from_DB()

  File "J:/working_dir/TLM/lib/DB_Wrapper/Lab_DB/DB_Reader.py", line 26, in read_from_DB

    print(str(session.query(CpuTable.id.label('id')).all()))

  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\query.py", line 2264, in all

  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py", line 75, in instances

  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py", line 75, in <listcomp>

  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py", line 74, in <listcomp>

  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\query.py", line 3440, in proc

  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py", line 71, in __getitem__

  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py", line 317, in _key_fallback

sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'cpu.id'"

when debugging this issue, i managed to find out that i do get the result from the db, only if the quesry was "select table.id" it returns as "table_id".   (i talked with some friends using this, and they confirmed it happens on their hand as well, only their sqlalchemy wrapper knows how to handle with this, while mine throws this error) . the commented out insert lines are to check i had connection to the db, and also to check it inserts the data, and it does, the query part is the problem. also while testing this against the sqlite in memory db it does work. 

hence my question is what can be the problem when working against mysql db with pymysql driver? 

tried searching the web for 1 day for similar problems couldnt find even 1. my sqlalchemy version was 0.9.1 and i downgraded it to 0.9 and still the problem persists. 

thx

[Michael Bayer]

On Feb 12, 2014, at 4:49 AM, Igal Kreimer <igal...@gmail.com> wrote:

> Hello sqlalchemy team.
>
> im a new user to the alchemy, and doing the tutorial on ur site.
> ran ur tutorial on adding information to databases and querying against a virtual db (sqlite in memory)
>
> after i thought i got that, i tried testing against a real db - mysql db.
> im using the driver of pymysql version 0.4 (i know its kinda old, but when was using newer version i had other bugs, not related to this topic).

OK well definitely get on the latest pymysql, that driver is not super mature so you want to be on the latest. I tried 0.6.1 here and can’t reproduce your issue.

>
> Traceback (most recent call last):
> File "J:/working_dir/TLM/lib/DB_Wrapper/Lab_DB/DB_Reader.py", line 39, in <module>
> read_from_DB()
> File "J:/working_dir/TLM/lib/DB_Wrapper/Lab_DB/DB_Reader.py", line 26, in read_from_DB
> print(str(session.query(CpuTable.id.label('id')).all()))
> File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\query.py", line 2264, in all
> File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py", line 75, in instances
> File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py", line 75, in <listcomp>
> File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py", line 74, in <listcomp>
> File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\query.py", line 3440, in proc
> File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py", line 71, in __getitem__
> File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py", line 317, in _key_fallback

> sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'cpu.id’"

yes that’s a totally strange issue that I cannot reproduce, running SQLA 0.9.0 / py3.3 / pymysql.

one thing that *may* be going wrong is that I see you’re on windows, and there might be case sensitivity issues happening. Though I’m not really sure how, I can use any name for the label and of course it works.

here’s the test case I’m using, you get the same results with this?

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
__tablename__ = 'a'

id = Column(Integer, primary_key=True)

e = create_engine("mysql+pymysql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

sess = Session(e)
sess.add_all([A(), A(), A()])

print(sess.query(A.id.label('id')).all())
print(sess.query(A.id.label('foobar')).all())

[Igal Kreimer]

yes it does. exactly the same problem, copy pasted ur code and received:

  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\query.py", line 2264, in all

  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py", line 75, in instances

  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py", line 75, in <listcomp>

  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py", line 74, in <listcomp>

  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\query.py", line 3440, in proc

  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py", line 71, in __getitem__

  File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py", line 317, in _key_fallback

sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'a.id'"

[Michael Bayer]

And you updated pymysql?  Or no?

Sent from my iPhone

--
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 post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[Igal Kreimer]

updated pymysql to version 0.6.1 and it worked for me. thx alot

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/Csp5gF0NBxw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.