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

Issue #4152 closed
Shikari Shambhu
created an issue

Hi Michael, trying to troubleshoot this . Can you pls help ?

Versions:

WIN 10, 64-bit

Python 2.7.12 |Anaconda 4.2.0 (64-bit)| (default, Jun 29 2016, 11:07:13) [MSC v.1500 64 bit (AMD64)] on win32

sqlite3.version : '2.6.0' sqlite3.sqlite_version : '3.8.11' sqlalchemy.version : '1.1.5'

Code>

import pandas as pd
import sqlalchemy as sa
import time

host = 'redshift.amazonaws.com'
port = '1234'
user = 'user'
password = 'password'
database = 'db'
url =  "postgresql://%s:%s@%s:%s/%s" % (user,password,host,port,database)
engine = sa.create_engine(url)

query = 'select * from table limit 10'

df = pd.read_sql_query(query, engine)

engine.connect().close()

Error > Also attached as TXT

Traceback (most recent call last):
  File "avg_store_sales.py", line 38, in <module>
    df = pd.read_sql_query(query, engine)
  File "C:\Program Files\Anaconda2\lib\site-packages\pandas\io\sql.py", line 332, in read_sql_query
    parse_dates=parse_dates, chunksize=chunksize)
  File "C:\Program Files\Anaconda2\lib\site-packages\pandas\io\sql.py", line 1092, in read_query
    result = self.execute(*args)
  File "C:\Program Files\Anaconda2\lib\site-packages\pandas\io\sql.py", line 983, in execute
    return self.connectable.execute(*args, **kwargs)
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py", line 2054, in execute
    connection = self.contextual_connect(close_with_result=True)
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py", line 2103, in contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py", line 2138, in _wrap_pool_connect
    return fn()
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 387, in connect
    return _ConnectionFairy._checkout(self)
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 766, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 516, in checkout
    rec = pool._do_get()
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 1138, in _do_get
    self._dec_overflow()
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 1135, in _do_get
    return self._create_connection()
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 333, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 461, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 661, in __connect
    exec_once(self.connection, self)
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py", line 246, in exec_once
    self(*args, **kw)
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py", line 256, in __call__
    fn(*args, **kw)
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", line 1321, in go
    return once_fn(*arg, **kw)
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\engine\strategies.py", line 181, in first_connect
    dialect.initialize(c)
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\postgresql\psycopg2.py", line 538, in initialize
    super(PGDialect_psycopg2, self).initialize(connection)
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\postgresql\base.py", line 2063, in initialize
    super(PGDialect, self).initialize(connection)
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\engine\default.py", line 245, in initialize
    self._get_server_version_info(connection)
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\postgresql\base.py", line 2271, in _get_server_version_info
    v = connection.execute("select version()").scalar()
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\engine\result.py", line 1224, in scalar
    return row[0]
  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\engine\result.py", line 563, in _key_fallback
    expression._string_or_unprintable(key))
sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column '0'"

Comments (5)

  1. Michael Bayer repo owner

    so this should have been posted on the mailing list since there's not any reproduction case for me here. please post there next time.

    Starting off, you give me the version of "sqlite" on your machine, but your test case uses Postgresql.

    Next, your test case uses postgresql, but then your hostname is suspiciously Amazon redshift.

    Are you using amazon redshift? If so, please use the correct dialect which is https://github.com/sqlalchemy-redshift/sqlalchemy-redshift and additionally make sure you are using latest psycopg2 (not sure if this is an upstream anaconda bug).

  2. Shikari Shambhu reporter

    Thanks, I added a while try.. except loop and it seems to work with the same config as above. It connects on the second try, not really sure why.

    while True: try: engine.connect() print "Connected to database" break except sa.exc.NoSuchColumnError: print "Error connecting to db..trying again" time.sleep(2) continue

  3. Log in to comment