[sqlalchemy] why func.sum(some_table.c.bigintfield) returns Decimal ?
Hello.
Why func.sum(some_table.c.bigintfield) returns Decimal ?
documents_table.c.sum has BigInteger type (postgresql table field has
bigint type)
the same issue I got with postgresql date_part func, when I extract
year or month I got Decimal result, not int
s = select([func.sum(documents_table.c.sum).label('payments_sum'),
func.sum(documents_table.c.payments).label('payments_count')])
session.execute(s).fetchall()
2011-09-26 13:48:41,543 INFO sqlalchemy.engine.base.Engine SELECT
sum(documents.sum) AS payments_sum, sum(documents.payments) AS
payments_count
FROM documents
2011-09-26 13:48:41,543 INFO sqlalchemy.engine.base.Engine {}
[(Decimal('51788997139'), 8853396L)]
s = select([cast(func.sum(documents_table.c.sum),
BigInteger).label('payments_sum'),
func.sum(documents_table.c.payments).label('payments_count')])
session.execute(s).fetchall()
2011-09-26 13:48:56,243 INFO sqlalchemy.engine.base.Engine SELECT
CAST(sum(documents.sum) AS BIGINT) AS payments_sum,
sum(documents.payments) AS payments_count
FROM documents
2011-09-26 13:48:56,244 INFO sqlalchemy.engine.base.Engine {}
[(51788997139L, 8853396L)]
--
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.
[sqlalchemy] Incorrect SQL generated for INSERT into PostgreSQL
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
[sqlalchemy] Rpclib: A Transport and Protocol Agnostic Rpc Library
Hello, Rpclib aims to save the protocol implementers the hassle of implementing their own remote procedure call api and the application programmers the hassle of jumping through hoops just to expose their services using multiple protocols and transports. It currently supports XmlSchema and SOAP protocols, over either HTTP or ZeroMQ. It's easy to add your own protocols and transports, see the documentation. It also integrates with SQLAlchemy's table objects, and partially with declarative objects. The following is an example on how you'd write a simple crud wrapper around a table mapped to the User object: class UserServices(ServiceBase) @rpc(Mandatory.Integer, _returns=User) def del_user(ctx, user_id): return ctx.udc.session.query(User).filter_by(user_id=user_id).delete() @rpc(Mandatory.Integer, _returns=User) def get_user(ctx, user_id): return ctx.udc.session.query(User).filter_by(user_id=user_id).one() @rpc(User) def set_user(ctx, user): ctx.udc.session.merge(user) Look at the documentation for more details: http://arskom.github.com/rpclib. , there's a section about sqlalchemy integration here: http://arskom.github.com/rpclib/manual/sqlalchemy.html Best Regards, Burak -- 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.
Re: [sqlalchemy] why func.sum(some_table.c.bigintfield) returns Decimal ?
func.sum(), being a known generic function in sql/functions.py, by default
will denote the return value as having the same SQL type as the value that was
passed in. The BigInteger type object though doesn't do any python-side
processing right now, its assumed DBAPIs can handle sending an int. In this
case psycopg2 DBAPI isn't doing that for us. You can force it to int using a
custom type:
class CoerceToInt(TypeDecorator):
impl = BigInteger
def process_result_value(self, value, dialect):
if value is not None:
value = int(value)
return value
func.sum(somefield, type_=CoerceToInt)
Or you could just call int() on the result.
For the date functions, SQLAlchemy doesn't know anything about those so you're
getting what psycopg2 returns. If you think psycopg2 should do something
different you can ask on their list.
On Sep 26, 2011, at 7:04 AM, sector119 wrote:
Hello.
Why func.sum(some_table.c.bigintfield) returns Decimal ?
documents_table.c.sum has BigInteger type (postgresql table field has
bigint type)
the same issue I got with postgresql date_part func, when I extract
year or month I got Decimal result, not int
s = select([func.sum(documents_table.c.sum).label('payments_sum'),
func.sum(documents_table.c.payments).label('payments_count')])
session.execute(s).fetchall()
2011-09-26 13:48:41,543 INFO sqlalchemy.engine.base.Engine SELECT
sum(documents.sum) AS payments_sum, sum(documents.payments) AS
payments_count
FROM documents
2011-09-26 13:48:41,543 INFO sqlalchemy.engine.base.Engine {}
[(Decimal('51788997139'), 8853396L)]
s = select([cast(func.sum(documents_table.c.sum),
BigInteger).label('payments_sum'),
func.sum(documents_table.c.payments).label('payments_count')])
session.execute(s).fetchall()
2011-09-26 13:48:56,243 INFO sqlalchemy.engine.base.Engine SELECT
CAST(sum(documents.sum) AS BIGINT) AS payments_sum,
sum(documents.payments) AS payments_count
FROM documents
2011-09-26 13:48:56,244 INFO sqlalchemy.engine.base.Engine {}
[(51788997139L, 8853396L)]
--
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.
--
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.
Re: [sqlalchemy] Incorrect SQL generated for INSERT into PostgreSQL
On Sep 26, 2011, at 2:50 AM, Nathan Robertson wrote:
Column('custid', Integer, Sequence('test.customer_custid_seq'),
primary_key=True),
for the Sequence, as with all schema items, you need to specify the schema
portion separately so that SQLAlchemy knows where each token starts and ends:
Sequence(customer_custid_seq, schema=test)
--
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.
[sqlalchemy] Re: attributes.get_history() seems inconsistent when changing from NULL versus to NULL
Oh, yes, I had already imagined these types of scenarios, so I
appreciate how the change from () to [None] could break several other
things (which is why I didn't touch it).
We'll certainly move to 0.7, just under much pressure currently to get
functionality in place and I already know 0.7 breaks some of the
things we were using. (We've used 'from sqlalchemy import topological'
for a couple purposes because it nicely sorts dependencies of any
type. I know you were planning to make the metadata immutable... that
would be bad for us as I've written database migration for our project
dependent upon being able to mutate metadata... sqlalchemy works great
for this)
Thanks again,
Kent
On Sep 23, 10:51 pm, Michael Bayer mike...@zzzcomputing.com wrote:
yup, so what I can say is that this is one of the many fruits that await you
when you get onto 0.7 :). attribute stuff is very difficult to change in a
maintenance release as every minute behavior affects all kinds of things.
The history of the attribute system is this: the current idea came online
around version 0.4, and in version 0.5 received a major overhaul. The
system was built strictly for usage by the unit of work system as an internal
system. Around the end of 0.5, or maybe in 0.6, the idea of making first
the events provided by AttributeExtension public came along, and soon after
that the idea of making the history system semi-public so that handy
versioning extensions could be built. The theme here is that the attribute
history system as a public API is 100% an afterthought and was never its
original purpose. The mechanics of the UOW, lazy loading, etc. are all
extremely intertwined with the smallest details of how the attribute system
works, so it's hard to change. 0.7 made the system a lot stronger (as did
0.6, 0.5, etc.) and it behaves much more nicely, but there's still lots of
weird cases where it doesn't. There's proposals in 0.8 to further refine
its public behavior: seehttp://www.sqlalchemy.org/trac/ticket/2128. If
you'd like to add suggestions to that, feel free. It's a pretty complicated
feature (as we have conditions like: attribute had no value at all, attribute
might have had a value but we didn't load it, etc.)
On Sep 23, 2011, at 1:27 PM, Kent wrote:
Sorry for not checking in the first place. From the looks of this 0.7
code and specifically from the comment, it seems you've already
changed this:
@classmethod
def from_scalar_attribute(cls, attribute, state, current):
original = state.committed_state.get(attribute.key,
_NO_HISTORY)
if original is _NO_HISTORY:
if current is NO_VALUE:
return cls((), (), ())
else:
return cls((), [current], ())
# dont let ClauseElement expressions here trip things up
elif attribute.is_equal(current, original) is True:
return cls((), [current], ())
else:
# current convention on native scalars is to not
# include information
# about missing previous value in deleted, but
# we do include None, which helps in some primary
# key situations
if id(original) in _NO_STATE_SYMBOLS:
deleted = ()
else:
deleted = [original]
if current is NO_VALUE:
return cls((), (), deleted)
else:
return cls([current], (), deleted)
On Sep 23, 1:12 pm, Michael Bayer mike...@zzzcomputing.com wrote:
I can look later today, but what does 0.7 do?
Sent from my iPhone
On Sep 23, 2011, at 11:17 AM, Kent jkentbo...@gmail.com wrote:
I see the code specifically treats going from None as deleted = ():
1417 else:
1418 - if original is not None:
1419 deleted = [original]
1420 else:
1421 deleted = ()
1422 return cls([current], (), deleted)
So I guess the question is why and is that inconsistent with going to
None?
On Sep 23, 10:39 am, Kent jkentbo...@gmail.com wrote:
I have two scalar columns in this example. (This is SQLAlchemy-0.6.4)
= To NULL ==
print l.percentofsale1
100
l.percentofsale1=None
attributes.get_history(l,'percentofsale1')
([None], (), [Decimal('100')])
= From NULL ==
print l.discount
None
l.discount=Decimal(100)
attributes.get_history(l,'discount')
([Decimal('100')], (), ())
I expected to be able to see the history moving from None to
Decimal('100') like this:
([Decimal('100')], (), [None])
Which would seem consistent with moving To NULL. Why is the
history.deleted () instead of [None]?
Thanks,
Kent
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to
Re: [sqlalchemy] Re: attributes.get_history() seems inconsistent when changing from NULL versus to NULL
On Sep 26, 2011, at 9:13 AM, Kent wrote: Oh, yes, I had already imagined these types of scenarios, so I appreciate how the change from () to [None] could break several other things (which is why I didn't touch it). We'll certainly move to 0.7, just under much pressure currently to get functionality in place and I already know 0.7 breaks some of the things we were using. (We've used 'from sqlalchemy import topological' for a couple purposes because it nicely sorts dependencies of any type. I know you were planning to make the metadata immutable... that would be bad for us as I've written database migration for our project dependent upon being able to mutate metadata... sqlalchemy works great for this) topological is under sqlalchemy.util now, you can do a version neutral import like this: from sqlalchemy import __version__ is_sa_07 = __version__ = (0, 7) if is_sa_07: from sqlalchemy.util import topological else: from sqlalchemy import topological for the mutate metadata use case, metadata still has remove() in 0.7its no problem at all for SQLA to go down the path of having metadata/Table be fully mutable except that there's a lot of coding/testing/bugs to be dealt with for that...and I'm going pretty slow through just whats up for 0.7.3 and 0.7.4 right now.And really its mainly looking for foreign keys when tables are removed and removing the Table reference from those somehow...or otherwise figuring out how that should work when you remove a Table that is referenced by a ForeignKey (raise an error? that would be easy. but a decision to be made). Thanks again, Kent On Sep 23, 10:51 pm, Michael Bayer mike...@zzzcomputing.com wrote: yup, so what I can say is that this is one of the many fruits that await you when you get onto 0.7 :).attribute stuff is very difficult to change in a maintenance release as every minute behavior affects all kinds of things. The history of the attribute system is this: the current idea came online around version 0.4, and in version 0.5 received a major overhaul.The system was built strictly for usage by the unit of work system as an internal system. Around the end of 0.5, or maybe in 0.6, the idea of making first the events provided by AttributeExtension public came along, and soon after that the idea of making the history system semi-public so that handy versioning extensions could be built. The theme here is that the attribute history system as a public API is 100% an afterthought and was never its original purpose. The mechanics of the UOW, lazy loading, etc. are all extremely intertwined with the smallest details of how the attribute system works, so it's hard to change. 0.7 made the system a lot stronger (as did 0.6, 0.5, etc.) and it behaves much more nicely, but there's still lots of weird cases where it doesn't. There's proposals in 0.8 to further refine its public behavior: seehttp://www.sqlalchemy.org/trac/ticket/2128. If you'd like to add suggestions to that, feel free. It's a pretty complicated feature (as we have conditions like: attribute had no value at all, attribute might have had a value but we didn't load it, etc.) On Sep 23, 2011, at 1:27 PM, Kent wrote: Sorry for not checking in the first place. From the looks of this 0.7 code and specifically from the comment, it seems you've already changed this: @classmethod def from_scalar_attribute(cls, attribute, state, current): original = state.committed_state.get(attribute.key, _NO_HISTORY) if original is _NO_HISTORY: if current is NO_VALUE: return cls((), (), ()) else: return cls((), [current], ()) # dont let ClauseElement expressions here trip things up elif attribute.is_equal(current, original) is True: return cls((), [current], ()) else: # current convention on native scalars is to not # include information # about missing previous value in deleted, but # we do include None, which helps in some primary # key situations if id(original) in _NO_STATE_SYMBOLS: deleted = () else: deleted = [original] if current is NO_VALUE: return cls((), (), deleted) else: return cls([current], (), deleted) On Sep 23, 1:12 pm, Michael Bayer mike...@zzzcomputing.com wrote: I can look later today, but what does 0.7 do? Sent from my iPhone On Sep 23, 2011, at 11:17 AM, Kent jkentbo...@gmail.com wrote: I see the code specifically treats going from None as deleted = (): 1417else: 1418 - if original is not None: 1419deleted = [original] 1420else: 1421deleted = () 1422return
Re: [sqlalchemy] Incorrect SQL generated for INSERT into PostgreSQL
On Mon, Sep 26, 2011 at 11:05 PM, Michael Bayer
mike...@zzzcomputing.com wrote:
On Sep 26, 2011, at 2:50 AM, Nathan Robertson wrote:
Column('custid', Integer, Sequence('test.customer_custid_seq'),
primary_key=True),
for the Sequence, as with all schema items, you need to specify the schema
portion separately so that SQLAlchemy knows where each token starts and ends:
Sequence(customer_custid_seq, schema=test)
Yep, that's it. Solved the problem. Thanks a lot for your help.
--
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.
Re: [sqlalchemy] Incorrect SQL generated for INSERT into PostgreSQL
On Mon, Sep 26, 2011 at 11:05 PM, Michael Bayer wrote:
On Sep 26, 2011, at 2:50 AM, Nathan Robertson wrote:
Column('custid', Integer, Sequence('test.customer_custid_seq'),
primary_key=True),
for the Sequence, as with all schema items, you need to specify the schema
portion separately so that SQLAlchemy knows where each token starts and ends:
Sequence(customer_custid_seq, schema=test)
Actually, I just noticed that the same thing doesn't apply to foreign
keys. I've actually got code in production which does something like:
Column('custid', BigInteger, ForeignKey('test.customer.custid'),
primary_key=True)
What's the reason for the distinction requiring the schema to be split
out in a Sequence, but not in the case of a ForeignKey?
--
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.
