How to fetch rows from postgresql database by specifying schema?

Question

I am new to Postgresql & Sqlalchemy. I have below file layout.py. In this, I have created two table name "layout" & "layout default" under "col" schemas.

import json, decimal
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy import create_engine
from sqlalchemy import Column, String, Integer, TIMESTAMP, Sequence, text, types
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import datetime, uuid


db_string = "postgres://postgres:PWDd@10.**.**.***:1111/d_demo"

Base = declarative_base()
db = create_engine(db_string)

class Layout(Base):
    __tablename__ = "col.layout"
    layout_id = Column(UUID(as_uuid=True), nullable=False, primary_key=True)
    name = Column(String(1000), nullable=False)
    layout = Column(String(10000), nullable=False)
    grid_id = Column(Integer, nullable=False)
    user_id = Column(Integer, nullable=False)
    issystemlayout = Column(Integer, default=0, nullable=False)
    ispublic = Column(Integer, default=0, nullable=False)
    isactive = Column(Integer, default=0, nullable=False)
    createdby = Column(Integer, default=1, nullable=False)
    createdat = Column(TIMESTAMP, default=(datetime.datetime.now()), nullable=False)
    modifiedat = Column(TIMESTAMP, default=(datetime.datetime.now()), nullable=False)
    modifiedby = Column(Integer, default=1, nullable=False)

Insert datas :

INSERT INTO col.layout(layout_id,name,layout,grid_id,user_id,ispublic,issystemlayout,isactive,createdby,createdat, modifiedat,modifiedby) VALUES('ba0233d7-d917-4303-b4bf-c2544a617d33','Layout1','{"Name":"Manish","Place":"Pune"}',1,12345,'1','0','1','201819','2015/05/20','2015/05/16',123);

Fetching data :

Session = sessionmaker(bind=db)  
session = Session()
Base.metadata.create_all(db)
session.query("SET search_path TO col;") 

result = []
selected_columns = Layout.__table__.columns
print("Selected columns {}".format(selected_columns))
record = session.query(Layout).with_entities(*selected_columns).all()
for row in record:
    print(row)
    result.append(row)

print(json.dumps(result))

session.close()

But it is not showing data under "col" schemas. Please suggest, how should I do?

Try with the attributes; __table_args__ = {"schema": "col"} and __tablename__ = "layout" — Nihal Sangeeth
– Nihal Sangeeth, Commented May 14, 2019 at 10:14

Nihal Sangeeth · Accepted Answer · 2019-05-14 11:21:08Z

1

https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/table_config.html#table-configuration

Schema for postgres database can be passed through table_args attribute either as a tuple or a dict.

So for your problem your class definition should have this extra attribute:

class Layout(Base):
    __tablename__ = "layout"
    __table_args__ = {"schema": "col"}
    layout_id = Column(UUID(as_uuid=True), nullable=False, primary_key=True)
    name = Column(String(1000), nullable=False)
    layout = Column(String(10000), nullable=False)
    grid_id = Column(Integer, nullable=False)
    user_id = Column(Integer, nullable=False)
    issystemlayout = Column(Integer, default=0, nullable=False)
    ispublic = Column(Integer, default=0, nullable=False)
    isactive = Column(Integer, default=0, nullable=False)
    createdby = Column(Integer, default=1, nullable=False)
    createdat = Column(TIMESTAMP, default=(datetime.datetime.now()), nullable=False)
    modifiedat = Column(TIMESTAMP, default=(datetime.datetime.now()), nullable=False)
    modifiedby = Column(Integer, default=1, nullable=False)

answered May 14, 2019 at 11:21

Nihal Sangeeth

5,5652 gold badges21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Manish Pal Over a year ago

one more small help needed. when I try to run above scripts, it saying "Object of type UUID is not JSON serializable". If you have any idea about this, please let me know.

Nihal Sangeeth Over a year ago

What does print(row) return? What does print(result) return?

Manish Pal Over a year ago

this line : "print(json.dumps(result))"

Nihal Sangeeth Over a year ago

Your layout_id column is a UUID object. This is not json serialisable. Perhaps you want the string value of the UUID object. You can use __str__() or just str()

Collectives™ on Stack Overflow

How to fetch rows from postgresql database by specifying schema?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related