Unable to access tables created with Python program using sqlalchemy

Question

I have created some tables using python script using sqlalchemy in the DB postgres. when I log into db using shell for that uname:password combination and execute command to show tables \dt it gives me following output

 Schema |       Name       | Type  |     Owner      
--------+------------------+-------+----------------
 public | CLASS_DESCRIPTOR | table | prefix_manager
 public | test             | table | prefix_manager
 public | user             | table | prefix_manager
 public | user_prefs       | table | prefix_manager
(6 rows)

The tables test was created using normal python script, and user,user_prefs were created using simple sqlalchemy code given below,

from sqlalchemy import *
engine = create_engine('postgresql://prefix_manager:password1@localhost:5432/prefix_manager')

metadata = MetaData()

user = Table('user', metadata,
    Column('user_id', Integer, primary_key = True),
    Column('user_name', String(16), nullable = False),
    Column('email_address', String(60), key='email'),
    Column('password', String(20), nullable = False)
)

user_prefs = Table('user_prefs', metadata,
    Column('pref_id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),
    Column('pref_name', String(40), nullable=False),
    Column('pref_value', String(100))
)

metadata.create_all(engine)

But the table CLASS_DESCRIPTOR was created by another python script with sqlalchemy. So when I do SELECT * from CLASS_DESCRIPTOR it gives me following error

ERROR:  relation "class_descriptor" does not exist.

SingleNegationElimination · Accepted Answer · 2014-09-10 04:43:08Z

2

postgresql distinguishes case in identifiers when they are specified using double quotes, as in

create table "FOO" (...);

but normalizes case (to all lowercase) when quotes are not present; in order to use that table, you must give postgresql a correctly cased (and therefore quoted) identifier. that is:

select * from "CLASS_DESCRIPTOR"

answered Sep 10, 2014 at 4:43

SingleNegationElimination

157k35 gold badges270 silver badges306 bronze badges

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

4 Comments

Dan Lenski Over a year ago

Spot on. Because of PostgreSQL's case conventions, I find that it's easier to use all lowercase names or names_like_this with PostgreSQL.

SingleNegationElimination Over a year ago

you can use whatever casing you like, so long as you skip the quotes, its the quotes that do surprising things, not the casing in general.

Dan Lenski Over a year ago

Certainly, but PG's own management tools (like pgadmin) show unquoted names in all lowercase regardless of how you specify them in your code. If you want to easily distinguish words within long names in pgadmin, underscores are an easy way to do it.

Ishan Bhatt Over a year ago

Bang on.. Working properly.

Collectives™ on Stack Overflow

Unable to access tables created with Python program using sqlalchemy

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