CODEWITHSUNDEEP
pythonsqlpostgresqlsqlalchemy
user3248533
user3248533

Reputation: 67

sqlalchemy column named user, inconsistency

I am intefacing a postgresql database via sqlalchemy (python).
In there I have a talbe like this: 

SELECT * from mytable;

 id | user | password |      host       | port 
----+------+----------+-----------------+------
  1 | abc  | def      |    127.0.0.1    |   21

But now if I try to do a equivalent (I thought ...) select from out of my code like so: 

s = sql.select(columns=['user', 'password', ...]).select_from('mytable')

Instead of the entries from the user column (abc), I get name of the the database user.

Also I noticed that the psql client renames the user field to 'current_user', if the column names are explicitly specified in the select statement:

SELECT user, password, host, port FROM mytable;

 current_user | password |      host       | port 
--------------+----------+-----------------+------
 abc          | def      |    127.0.0.1    |   21

Do I have to rename all my table columns, in order to avoid the mixups?
Or does sqlalchemy provide a solution for this problem?

Here is the table spec: 

\dS mytable;
                                Table "public.mytable"
  Column  |            Type             |                            Modifiers                             
----------+-----------------------------+--------------------------------------------------------------------
 id       | integer                     | not null default nextval('mytable_id_seq'::regclass)
 user     | character varying           | not null
 password | character varying           | not null
 host     | character varying           | not null
 port     | integer                     | not null

Indexes:
    "mytable_pkey" PRIMARY KEY, btree (id)

Upvotes: 1

Views: 264

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324375

Common mistake.

user is an alias for current_user, the keyword for an SQL standard pseudo function.

Use "user" (i.e. double-quote the identifier) if you want a table or column of that name. Or choose your table/column names not to conflict with reserved keywords.

ORMs and query generators should be automatically double quoting all keywords (or all identifiers, since there's no harm in it). Most don't, unfortunately.

Upvotes: 3

Related Questions