2

I'm new to SQLAlchemy (but not ORM's in general) and running into an issue where I literally cannot do anything useful. All I'm able to do is hard code my query, which is what I want to do.

Here is exactly what I'm attempting to do (see Python comments to see what does/doesn't work - 

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy import MetaData, Table
import pypyodbc

connection_string = 'mssql+pyodbc://U:P@SERVER/DB'

Base = declarative_base()


class Item(Base):
    __tablename__ = 'Items'

    id = Column('Id', Integer, primary_key=True)

engine = create_engine(connection_string, echo=True, module=pypyodbc)
sm = sessionmaker(engine)
session = sm()

# WORKS
for row in engine.execute('select top 1 Id from Items'):
    print(row)

# DOES NOT WORK
print(session.query(Item).get(111))

The error I'm getting is sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'Items.Id'".

The generated SQL is correct - 

SELECT [Items].[Id] AS [Items_Id]
FROM [Items]
WHERE [Items].[Id] = 111

I know I'm pointing to the right database, because if I change __tablename to something non-existent I get an invalid object name error. Why can't SQLAlchemy find my columns?

self.
  • 1,612
  • 4
  • 18
  • 35
  • Try `engine.execute` with the generated SQL and inspecting each row object, do you get the correct columns and column names? – univerio Aug 15 '14 at 18:23
  • @univerio: The dictionary key, which I'm assuming is supposed to be the column name, is `items_id`. – self. Aug 15 '14 at 18:35
  • Lower case? What happens if you change `__tablename__` to `'items'` and `id` to `'id'`? – univerio Aug 15 '14 at 18:40

0 Answers0