Hi,
I've come across a bug (hopefully in my configuration) where
SQLAlchemy will generate an INSERT statement for a table with a SERIAL
primary key which PostgreSQL errors on. I'm running EnterpriseDB's
Postgres Plus 8.4 on openSUSE 11.4 x64, with Python 2.7, SQLAlchemy
0.7.2 and psycopg2 2.4.2. From the PostgreSQL logs:
2011-09-26 15:38:52 ESTLOG: statement: INSERT INTO test.customer
(custid, name) VALUES (nextval('"test.customer_custid_seq"'), E'Test')
RETURNING test.customer.custid
2011-09-26 15:38:52 ESTERROR: relation "test.customer_custid_seq"
does not exist at character 58
Running this query in psql from the command line reproduces the error
accurately. But if I remove the double quotes that are inside the
single quotes that are around the sequence name in the query (ie.
change the custid to be nextval('test.customer_custid_seq')) in the
command line psql the INSERT works fine. So, SQLAlchemy for some
reason is adding the "" which PostgreSQL doesn't like.
Could somebody help me work out why SQLAlchemy is adding in the extra
"" around the sequence name?
The test database I'm running this against looks like this:
create schema test;
create table test.customer (
custid bigserial not null,
name varchar(60) not null,
primary key (custid)
);
The code looks like this:
from sqlalchemy import create_engine, Table, Column, Sequence,
MetaData, ForeignKey, BigInteger, CHAR, Date, Integer, NUMERIC,
SmallInteger, String, TIMESTAMP
from sqlalchemy.orm import mapper, relationship, backref,
sessionmaker, scoped_session
from sqlalchemy.sql import and_
# Definitions
metadata = MetaData()
customer_table = Table('customer', metadata,
Column('custid', Integer, Sequence('test.customer_custid_seq'),
primary_key=True),
Column('name', String(60)),
schema='test')
class Customer(object):
pass
mapper(Customer, customer_table, properties={})
# Database connection
conn_args = {
'host':'/tmp/',
'database':'test',
}
engine = create_engine('postgresql+psycopg2://', connect_args =
conn_args)
Session = scoped_session(sessionmaker(bind=engine))
cust = Customer()
cust.name = 'Test'
Session.merge(cust)
Session.commit()
The output from running that all that looks like this:
nathanr@coopers:~/Desktop> createdb test
nathanr@coopers:~/Desktop> psql -f test.sql test
CREATE SCHEMA
psql:test.sql:7: NOTICE: CREATE TABLE will create implicit sequence
"customer_custid_seq" for serial column "customer.custid"
psql:test.sql:7: NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "customer_pkey" for table "customer"
CREATE TABLE
nathanr@coopers:~/Desktop> python test.py
Traceback (most recent call last):
File "test.py", line 28, in <module>
Session.commit()
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
scoping.py", line 113, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
session.py", line 617, in commit
self.transaction.commit()
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
session.py", line 293, in commit
self._prepare_impl()
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
session.py", line 277, in _prepare_impl
self.session.flush()
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
session.py", line 1493, in flush
self._flush(objects)
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
session.py", line 1562, in _flush
flush_context.execute()
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
unitofwork.py", line 327, in execute
rec.execute(self)
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
unitofwork.py", line 471, in execute
uow
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/orm/
mapper.py", line 2174, in _save_obj
execute(statement, params)
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/engine/
base.py", line 1399, in execute
params)
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/engine/
base.py", line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/engine/
base.py", line 1640, in _execute_context
context)
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/engine/
base.py", line 1633, in _execute_context
context)
File "/usr/local/lib64/python2.7/site-packages/sqlalchemy/engine/
default.py", line 325, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation
"test.customer_custid_seq" does not exist
LINE 1: ...INTO test.customer (custid, name) VALUES
(nextval('"test.cus...
^
'INSERT INTO test.customer (custid, name) VALUES
(nextval(\'"test.customer_custid_seq"\'), %(name)s) RETURNING
test.customer.custid' {'name': 'Test'}
nathanr@coopers:~/Desktop>
PostgreSQL Logs:
2011-09-26 15:38:52 ESTLOG: statement: BEGIN
2011-09-26 15:38:52 ESTLOG: statement: INSERT INTO test.customer
(custid, name) VALUES (nextval('"test.customer_custid_seq"'), E'Test')
RETURNING test.customer.custid
2011-09-26 15:38:52 ESTERROR: relation "test.customer_custid_seq"
does not exist at character 58
2011-09-26 15:38:52 ESTSTATEMENT: INSERT INTO test.customer (custid,
name) VALUES (nextval('"test.customer_custid_seq"'), E'Test')
RETURNING test.customer.custid
2011-09-26 15:38:52 ESTLOG: statement: ROLLBACK
2011-09-26 15:38:52 ESTLOG: unexpected EOF on client connection
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
