tzu tzu - 29 days ago 8
Python Question

SQLAlchemy query/filter does not work

I have created a database called

websites2014
and three tables
cand_elec
,
sites
, and
pages
under this database through Postgresql. I am now trying to query the table
pages
and filter by one of its columns
uuid
but it doesn't work. Below is the code:

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy import Column, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import inspect

db_string = "postgres://usr:passwd@localhost:5432/websites2014"

db = create_engine(db_string)
Session = sessionmaker(db)
session = Session()

pages = session.query('pages')


The codes above work fine until the next line:

test = pages.filter_by(uuid="1234").first()


It doesn't work if I change the code to:

test = pages.filter_by("uuid"="1234").first()


I am new to Postgresql and SQLAlchemy. Can anyone help? Thanks!




EDIT 1



Below are the new codes I revised:

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy import Column, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import inspect

db_string = "postgres://usr:passwd@localhost:5432/websites2014"

db = create_engine(db_string)
Session = sessionmaker(db)
session = Session()

Base = declarative_base()

class Page(Base):
__tablename__ = 'polls_question'

uuid = Column(String, primary_key=True)

pages = session.query(Page)
test = pages.filter_by(uuid=1234).first()


However, it gives me an error message as:
InternalError: (psycopg2.InternalError) current transaction is aborted, commands ignored until end of transaction block
[SQL: 'SELECT polls_question.uuid AS polls_question_uuid \nFROM polls_question \nWHERE polls_question.uuid = %(uuid_1)s \n LIMIT %(param_1)s'] [parameters: {'uuid_1': 1234, 'param_1': 1}]


If I change the last line to:
session.execute("""SELECT * from page WHERE uuid = '1234'""").first()


It shows another error message as :
InternalError: (psycopg2.InternalError) current transaction is aborted, commands ignored until end of transaction block
[SQL: "SELECT * from page WHERE uuid = '1234'"]


Thanks for the help again!




EDIT 2



As the comments below the answer, if I rewrite the codes as:

class Pages(Base):
__tablename__ = 'pages'

uuid = Column(String, primary_key=True)


so the class' name and the tablename are defined as I defined when creating the table. It should work and the error message will disappear.

Answer Source

You are trying to use a query object, but it seems you didn't create a mapped class to instantiate it.

Query documentation

Mapping documentation

You should declare a class Page like the following to use this tool :

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
Base = declarative_base()

class Page(Base):
    __tablename__ = 'pages'

    uuid =  Column(String, primary_key=True) 

It doesn't need all columns defined in your table, at least the primary key and the columns you will use after in the filter, or in the output you want to create.

Then you can use it to build the session.query :

pages = session.query(Page)  
test = pages.filter_by(uuid="1234").first()

Maybe it can be easier to create a simple SELECT query statement ?

session.execute("""SELECT * from pages WHERE uuid = '1234'""").first()