[sqlalchemy] no server side cursors in SA 0.4.6?
Using Postgres 8.3.1
Consider the following script. In SA 0.4.3 it works as intended. In SA
0.4.6, it does not. In particular, the time to get the resultset in 0.4.3
is sub-second. The time in 0.4.6 is about 20 seconds. Also, when running
on 0.4.3 the memory consumption of the script is constant under 10MB. When
running on 0.4.6, it grows to hundreds of MB and is dependent on the size of
the result set. Seems to me that 0.4.3 used a cursor like it was configured
to and 0.4.6 ignored the server_side_cursors=True parameter to the
create_engine call. How do I make 0.4.6 use server side cursors?
#!/usr/bin/python
from time import time
from sqlalchemy import text
from sqlalchemy import create_engine
stime = time()
engine = create_engine('postgres://[EMAIL PROTECTED]/postgres',
server_side_cursors=True, encoding='utf-8')
conn = engine.connect()
trans = conn.begin()
print have engine, connection, transaction after about %.4f seconds %
(time() - stime)
stime = time()
rs = conn.execute(text(select * from generate_series(1,1000) s0,
generate_series(1,1) s1))
print have resultset after about %.4f seconds % (time() - stime)
count = 0
stime = time()
for r in rs:
count += 1
print counted %s rows after about %.4f seconds % (count, time() - stime)
stime = time()
rs.close()
print closed resultset after about %.4f seconds % (time() - stime)
stime = time()
trans.commit()
print commited after about %.4f seconds % (time() - stime)
stime = time()
conn.close()
print closed connection after about %.4f seconds % (time() - stime)
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] transactional sessions not transactional?
The following test case of mine fails on PG 8.3 and SA 0.4.3 Basically,
create two sessions, make some changes in the first and obverse they are
visible before commit/rollback in the second (and via connectionless
execution directly on the engine), but become unvisible after rollback. The
first two print statements both show a row returned (that should only be
visible from s0), but after the rollback the print statements show there are
no values. It's almost like a threadlocal strategy is being used when it
was never configured. Ideas/thoughts/comments?
#!/usr/bin/python
from sqlalchemy.sql import text
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('postgres://[EMAIL PROTECTED]/testsatransaction')
new_session = sessionmaker(bind=engine, transactional=True)
engine.execute(text(drop table if exists foo))
engine.execute(text(create table foo(c1 int)))
s0 = new_session()
s1 = new_session()
s0.execute(text(insert into foo values(1)))
(one,) = s0.execute(text(select * from foo)).fetchone()
assert one == 1
print engine.execute(text(select * from foo)).fetchone()
print s1.execute(text(select * from foo)).fetchone()
s0.rollback()
print engine.execute(text(select * from foo)).fetchone()
print s1.execute(text(select * from foo)).fetchone()
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] SQLAlchemy, Postgres and ENum?
I didn't see anything in the doc, and google wasn't much help in this case so I'm guessing that SA doesn't support a ENum type with PG? If not, are there standard/best practices for working around it in SA? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] SA confusing timestamp with interval?
I'm using SA 0.4.3 and PostgreSQL 8.3.1
I'm new to SA, so perhaps I'm doing something wrong or just not
understanding something, but I think SA is trying to treat my timestamps as
intervals in some cases. If I run the equivalent (select c0 from t0 where
c0 current_timestamp - interval '1 hour') via psql, it works as expected.
However,
If I run:
#!/usr/bin/python
from datetime import datetime
from sqlalchemy.sql import text
from sqlalchemy import create_engine, MetaData
engine = create_engine('postgres://[EMAIL PROTECTED]/testdb',
encoding='utf-8')
metadata = MetaData(bind=engine)
engine.execute(text(drop table if exists t0))
engine.execute(text(create table t0(c0 timestamp(0) with time zone)))
engine.execute(text(insert into t0 values(current_timestamp)))
engine.execute(text(select c0 from t0 where c0 :bindArg - interval '1
hour'), bindArg=datetime.utcnow())
I get:
Traceback (most recent call last):
File ./saerr.py, line 14, in module
engine.execute(text(select c0 from t0 where c0 :bindArg - interval '1
hour'), bindArg=datetime.utcnow())
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py, line
1214, in execute
return connection.execute(statement, *multiparams, **params)
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py, line
846, in execute
return Connection.executors[c](self, object, multiparams, params)
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py, line
897, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) 1), distilled_params=params)
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py, line
909, in _execute_compiled
self.__execute_raw(context)
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py, line
918, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py, line
962, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor)
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py, line
944, in _handle_dbapi_exception
raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) invalid input
syntax for type interval: 2008-04-18T15:37:02.235955
select c0 from t0 where c0 %(bindArg)s - interval '1 hour' {'bindArg':
datetime.datetime(2008, 4, 18, 15, 37, 2, 235955)}
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SA confusing timestamp with interval?
I get a similar result if I use psycopg2 directly:
#!/usr/bin/python
import psycopg2
from datetime import datetime
conn = psycopg2.connect('''dbname=testdb user=postgres host=localhost''')
cur = conn.cursor()
cur.execute(drop table if exists t0)
cur.execute(create table t0(c0 timestamp(0) with time zone))
cur.execute(insert into t0 values(current_timestamp))
cur.execute(select c0 from t0 where c0 %(bindArg)s - interval '1 hour',
{'bindArg':datetime.utcnow()})
On Fri, Apr 18, 2008 at 6:26 PM, Michael Bayer [EMAIL PROTECTED]
wrote:
On Apr 18, 2008, at 4:42 PM, Matthew Dennis wrote:
I'm using SA 0.4.3 and PostgreSQL 8.3.1
I'm new to SA, so perhaps I'm doing something wrong or just not
understanding something, but I think SA is trying to treat my
timestamps as intervals in some cases. If I run the equivalent
(select c0 from t0 where c0 current_timestamp - interval '1 hour')
via psql, it works as expected. However,
If I run:
#!/usr/bin/python
from datetime import datetime
from sqlalchemy.sql import text
from sqlalchemy import create_engine, MetaData
engine = create_engine('postgres://[EMAIL PROTECTED]/testdb',
encoding='utf-8')
metadata = MetaData(bind=engine)
engine.execute(text(drop table if exists t0))
engine.execute(text(create table t0(c0 timestamp(0) with time
zone)))
engine.execute(text(insert into t0 values(current_timestamp)))
engine.execute(text(select c0 from t0 where c0 :bindArg -
interval '1 hour'), bindArg=datetime.utcnow())
the text() above sends through the bind argument to psycopg2 directly,
which knows how to handle datetime objects. What happens if you test
with raw psycopg2 ?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SA confusing timestamp with interval?
I posted it on the psycopg list at
http://lists.initd.org/pipermail/psycopg/2008-April/006026.html, but it
mangled my link to this discussion (by eating a space after the URL and
appending the first word of the next sentence)
On Fri, Apr 18, 2008 at 9:34 PM, Michael Bayer [EMAIL PROTECTED]
wrote:
On Apr 18, 2008, at 10:20 PM, Matthew Dennis wrote:
I get a similar result if I use psycopg2 directly:
#!/usr/bin/python
import psycopg2
from datetime import datetime
conn = psycopg2.connect('''dbname=testdb user=postgres
host=localhost''')
cur = conn.cursor()
cur.execute(drop table if exists t0)
cur.execute(create table t0(c0 timestamp(0) with time zone))
cur.execute(insert into t0 values(current_timestamp))
cur.execute(select c0 from t0 where c0 %(bindArg)s - interval '1
hour', {'bindArg':datetime.utcnow()})
great. lets let them know on the psycopg2 list.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
