[sqlalchemy] execute() and commit() and commit
Hi there,
I'm having a bit of trouble with session.execute() and session.commit()
Here's a simple example that demonstrates what I'm trying to do:
import os
import sys
import sqlalchemy.orm as orm
import sqlalchemy as sa
Session = orm.sessionmaker()
session = None
def setup():
global session
Session.configure(bind=sa.create_engine('sqlite:///test.sqlite'))
session = Session(autocommit=False)
try:
os.unlink(test.sqlite)
except OSError:
pass
session.execute(create table test (a, b, c))
session.commit()
def test1():
session.execute(insert into test values (1, 2, 3))
session.commit()
def test2():
session.execute(insert into test values (4, 5, 6))
def test3():
session.execute(insert into test values (7, 8, 9))
session.execute(commit)
if __name__ == __main__:
dispatch = {'test1': test1,
'test2': test2,
'test3': test3}
if len(sys.argv) 1 and sys.argv[1] in dispatch:
setup()
dispatch[sys.argv[1]]()
And here's the output from running each test:
{. b...@mote: ~/dev/sasqlconsole .]
£ python test.py test1 sqlite3 test.sqlite select * from test;
1|2|3
{. b...@mote: ~/dev/sasqlconsole .]
£ python test.py test2 sqlite3 test.sqlite select * from test;
{. b...@mote: ~/dev/sasqlconsole .]
£ python test.py test3 sqlite3 test.sqlite select * from test;
Traceback (most recent call last):
File test.py, line 41, in module
dispatch[sys.argv[1]]()
File test.py, line 33, in test3
session.execute(commit)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/session.py,
line 753, in execute
clause, params or {})
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 874, in _execute_clauseelement
return self.__execute_context(context)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor, context)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.OperationalError: (OperationalError) cannot commit - no
transaction is active u'commit' []
Tests 1 2 behave as I'd expect, but it's when I try to pass in a commit as
a string to execute() that I see explosions.
This is for a SQL repl I wrote some time ago and I decided to add transactions
to it so I need a sane way to deal with commit being typed. I'd like to make
this behaviour optional so that it can mimic other sql repls such as sqlplus.
Any suggestions on the best way to go about doing this are welcome.
Thanks,
--
Bob Farrell
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Reflected Tables and Session [and how to bind]
Hi,
I desperately hope that someone can help with this!! I am connecting
to a remote machine (the connection works fine) but am having problems
understanding how to use/instantiate reflected tables.
My class would read as follows:
Base = declarative_base()
class ConnectLog(Base):
__table__ = Table('external_connection_log', Base.metadata,
autoload=True, autoload=engine)
However, importing [or trying to instantiate this class] will fail
until the connection is in place ie the class as it stands cannot be
called until a valid engine is bound. I'm guessing that something
will need to be passed to an __init__ in the class above.
Is there a strategy for dealing with this?
Many thanks,
Rob
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] obtaining pid of forked process
On Feb 11, 2010, at 18:58 , Faheem Mitha wrote: sqlalchemy forks a process when it calls the db No, it does not. The reason for this is that I want to plot a memory graph of the postgresql process, so it is handy to have the pid for this. PostgreSQL forks a new backend process when a connection is established, however. It sounds like that's what you want. Do SELECT pg_backend_pid() to get the PID of the backend process serving your connection. That and other stat functions are documented here: http://www.postgresql.org/docs/current/static/monitoring-stats.html -- Alex Brasetvik -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: execute() and commit() and commit
Sorry, I forgot to mention that if I run my select * from test;
*after* I get the error for test3, it shows that insert did in fact
get committed to the database:
{. b...@mote: ~/dev/sasqlconsole .]
£ sqlite3 test.sqlite select * from test
7|8|9
So it seems that the commit is getting sent to the database and it's
processing correctly, but SQLAlchemy is stepping in somewhere on the
way back before the execute() returns and causing some mischief.
On Feb 12, 12:19 pm, Bob Farrell robertanthonyfarr...@googlemail.com
wrote:
Hi there,
I'm having a bit of trouble with session.execute() and session.commit()
Here's a simple example that demonstrates what I'm trying to do:
import os
import sys
import sqlalchemy.orm as orm
import sqlalchemy as sa
Session = orm.sessionmaker()
session = None
def setup():
global session
Session.configure(bind=sa.create_engine('sqlite:///test.sqlite'))
session = Session(autocommit=False)
try:
os.unlink(test.sqlite)
except OSError:
pass
session.execute(create table test (a, b, c))
session.commit()
def test1():
session.execute(insert into test values (1, 2, 3))
session.commit()
def test2():
session.execute(insert into test values (4, 5, 6))
def test3():
session.execute(insert into test values (7, 8, 9))
session.execute(commit)
if __name__ == __main__:
dispatch = {'test1': test1,
'test2': test2,
'test3': test3}
if len(sys.argv) 1 and sys.argv[1] in dispatch:
setup()
dispatch[sys.argv[1]]()
And here's the output from running each test:
{. b...@mote: ~/dev/sasqlconsole .]
python test.py test1 sqlite3 test.sqlite select * from test;
1|2|3
{. b...@mote: ~/dev/sasqlconsole .]
python test.py test2 sqlite3 test.sqlite select * from test;
{. b...@mote: ~/dev/sasqlconsole .]
python test.py test3 sqlite3 test.sqlite select * from test;
Traceback (most recent call last):
File test.py, line 41, in module
dispatch[sys.argv[1]]()
File test.py, line 33, in test3
session.execute(commit)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/session.py,
line 753, in execute
clause, params or {})
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 874, in _execute_clauseelement
return self.__execute_context(context)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor, context)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.OperationalError: (OperationalError) cannot commit - no
transaction is active u'commit' []
Tests 1 2 behave as I'd expect, but it's when I try to pass in a commit as
a string to execute() that I see explosions.
This is for a SQL repl I wrote some time ago and I decided to add transactions
to it so I need a sane way to deal with commit being typed. I'd like to make
this behaviour optional so that it can mimic other sql repls such as sqlplus.
Any suggestions on the best way to go about doing this are welcome.
Thanks,
--
Bob Farrell
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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: execute() and commit() and commit
I tried this against Oracle and it works without a hitch, so it looks
like it's a problem with sqlite - we've got some ideas on how to fix
it so we'll carry on looking. So this thread can be ignored now, as
it's not a sqlalchemy issue (unless sqlalchemy planned to special case
commit as text for sqlite).
Thanks,
On Feb 12, 12:33 pm, Bob Farrell robertanthonyfarr...@googlemail.com
wrote:
Sorry, I forgot to mention that if I run my select * from test;
*after* I get the error for test3, it shows that insert did in fact
get committed to the database:
{. b...@mote: ~/dev/sasqlconsole .]
£ sqlite3 test.sqlite select * from test
7|8|9
So it seems that the commit is getting sent to the database and it's
processing correctly, but SQLAlchemy is stepping in somewhere on the
way back before the execute() returns and causing some mischief.
On Feb 12, 12:19 pm, Bob Farrell robertanthonyfarr...@googlemail.com
wrote:
Hi there,
I'm having a bit of trouble with session.execute() and session.commit()
Here's a simple example that demonstrates what I'm trying to do:
import os
import sys
import sqlalchemy.orm as orm
import sqlalchemy as sa
Session = orm.sessionmaker()
session = None
def setup():
global session
Session.configure(bind=sa.create_engine('sqlite:///test.sqlite'))
session = Session(autocommit=False)
try:
os.unlink(test.sqlite)
except OSError:
pass
session.execute(create table test (a, b, c))
session.commit()
def test1():
session.execute(insert into test values (1, 2, 3))
session.commit()
def test2():
session.execute(insert into test values (4, 5, 6))
def test3():
session.execute(insert into test values (7, 8, 9))
session.execute(commit)
if __name__ == __main__:
dispatch = {'test1': test1,
'test2': test2,
'test3': test3}
if len(sys.argv) 1 and sys.argv[1] in dispatch:
setup()
dispatch[sys.argv[1]]()
And here's the output from running each test:
{. b...@mote: ~/dev/sasqlconsole .]
python test.py test1 sqlite3 test.sqlite select * from test;
1|2|3
{. b...@mote: ~/dev/sasqlconsole .]
python test.py test2 sqlite3 test.sqlite select * from test;
{. b...@mote: ~/dev/sasqlconsole .]
python test.py test3 sqlite3 test.sqlite select * from test;
Traceback (most recent call last):
File test.py, line 41, in module
dispatch[sys.argv[1]]()
File test.py, line 33, in test3
session.execute(commit)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/session.py,
line 753, in execute
clause, params or {})
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 874, in _execute_clauseelement
return self.__execute_context(context)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor, context)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.OperationalError: (OperationalError) cannot commit - no
transaction is active u'commit' []
Tests 1 2 behave as I'd expect, but it's when I try to pass in a commit
as
a string to execute() that I see explosions.
This is for a SQL repl I wrote some time ago and I decided to add
transactions
to it so I need a sane way to deal with commit being typed. I'd like to
make
this behaviour optional so that it can mimic other sql repls such as
sqlplus.
Any suggestions on the best way to go about doing this are welcome.
Thanks,
--
Bob Farrell
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Oracle in clause limit
Hello all, I'm relatively new to SQLAlchemy - is there any elegant workaround that SQLAlchemy provides for queries with in clause that contains more than 1000 items? I have, say, date, item, value table that I'd like to query for arbitrary set of dates and items (date and item list is provided by the user or generated by the program. (I'd like to avoid creating temp tables with arguments and perfoming a join). Many thanks, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 rowcount with Sqlite
Hello there.
(first post to group)
I've been having a problem with an incorrect rowcount for ResultProxies
using Sqlite databases. Regardless of how many rows in the resultset, it
gives me a rowcount of -1. Best demonstrated with an example:
from sqlalchemy import create_engine, __version__
from sqlalchemy.orm import create_session
print Testing sqlite rowcount with SqlAlchemy v%s % __version__
engine = create_engine('sqlite:///:memory:')
session = create_session(engine)
session.execute(CREATE TABLE IF NOT EXISTS users (
id INT(8) NOT NULL,
status INT NOT NULL,
name VARCHAR(100) NULL,
PRIMARY KEY (id)
);)
session.execute(INSERT INTO users (id, status, name) VALUES(1, 'Dan', 2))
session.execute(INSERT INTO users (id, status, name) VALUES(2, 'Dave', 1))
session.execute(INSERT INTO users (id, status, name) VALUES(3, 'Donald',
5))
result = session.execute(SELECT * FROM users)
print result.rowcount
allrows = result.fetchall()
print len(allrows)
session.close()
Output is:
Testing sqlite rowcount with SqlAlchemy v0.5.8
*-1*
3
Is this a bug? Am I doing something wrong?
Regards,
Adam
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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: Incorrect rowcount with Sqlite
Hello,
I have discovered that this is a limitation of pysqlite. From a comment in
one of the test cases:
pysqlite does not know the rowcount of SELECT statements, because we
don't fetch all rows after executing the select statement. The rowcount
has thus to be -1.
http://code.google.com/p/pysqlite/source/browse/lib/test/dbapi.py#300
It would be nice if SqlAlchemy could somehow give the same rowcount
regardless of the database engine. For example:
from sqlalchemy import create_engine, __version__
from sqlalchemy.orm import create_session
print Testing mysql sqlite rowcount with SqlAlchemy v%s % __version__
mysql_engine = create_engine('mysql://user:p...@localhost/test')
sqlite_engine = create_engine('sqlite:///:memory:')
mysql_session = create_session(mysql_engine)
sqlite_session = create_session(sqlite_engine)
sql_drop = DROP TABLE IF EXISTS users;
sql_create = CREATE TABLE IF NOT EXISTS users (id INT(8) NOT NULL, status
\
INT NOT NULL, name VARCHAR(100) NULL, PRIMARY KEY (id));
sql_ins_1 = INSERT INTO users (id, status, name) VALUES(1, 2, 'Dan')
sql_ins_2 = INSERT INTO users (id, status, name) VALUES(2, 1, 'Dave')
sql_ins_3 = INSERT INTO users (id, status, name) VALUES(3, 5, 'Donald')
print Creating schemas
mysql_session.execute(sql_drop)
mysql_session.execute(sql_create)
sqlite_session.execute(sql_drop)
sqlite_session.execute(sql_create)
print Inserting some data
mysql_session.execute(sql_ins_1)
mysql_session.execute(sql_ins_2)
mysql_session.execute(sql_ins_3)
sqlite_session.execute(sql_ins_1)
sqlite_session.execute(sql_ins_2)
sqlite_session.execute(sql_ins_3)
all_mysql_rows = mysql_session.execute(SELECT * FROM users)
all_sqlite_rows = sqlite_session.execute(SELECT * FROM users)
print all_mysql_rows.rowcount: %d % all_mysql_rows.rowcount
print len(all_mysql_rows.fetchall()): %d % len(all_mysql_rows.fetchall())
print all_sqlite_rows.rowcount: %d % all_sqlite_rows.rowcount
print len(all_sqlite_rows.fetchall()): %d %
len(all_sqlite_rows.fetchall())
mysql_session.close()
sqlite_session.close()
Output is:
Testing mysql sqlite rowcount with SqlAlchemy v0.5.8
Creating schemas
Inserting some data
all_mysql_rows.rowcount: *3*
len(all_mysql_rows.fetchall()): *3*
all_sqlite_rows.rowcount: *-1*
len(all_sqlite_rows.fetchall()): *3*
~Adam
P.s., yes, I noticed that in my last post, I was erroneously inserting
strings into an integer field!
On 12 February 2010 12:26, Adam Hayward a...@happy.cat wrote:
Hello there.
(first post to group)
I've been having a problem with an incorrect rowcount for ResultProxies
using Sqlite databases. Regardless of how many rows in the resultset, it
gives me a rowcount of -1. Best demonstrated with an example:
from sqlalchemy import create_engine, __version__
from sqlalchemy.orm import create_session
print Testing sqlite rowcount with SqlAlchemy v%s % __version__
engine = create_engine('sqlite:///:memory:')
session = create_session(engine)
session.execute(CREATE TABLE IF NOT EXISTS users (
id INT(8) NOT NULL,
status INT NOT NULL,
name VARCHAR(100) NULL,
PRIMARY KEY (id)
);)
session.execute(INSERT INTO users (id, status, name) VALUES(1, 'Dan', 2))
session.execute(INSERT INTO users (id, status, name) VALUES(2, 'Dave',
1))
session.execute(INSERT INTO users (id, status, name) VALUES(3, 'Donald',
5))
result = session.execute(SELECT * FROM users)
print result.rowcount
allrows = result.fetchall()
print len(allrows)
session.close()
Output is:
Testing sqlite rowcount with SqlAlchemy v0.5.8
*-1*
3
Is this a bug? Am I doing something wrong?
Regards,
Adam
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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 rowcount with Sqlite
Adam Hayward wrote: Hello there. (first post to group) I've been having a problem with an incorrect rowcount for ResultProxies using Sqlite databases. Regardless of how many rows in the resultset, it gives me a rowcount of -1. Best demonstrated with an example: Is this a bug? Am I doing something wrong? the general purpose of cursor.rowcount in DBAPI is to return the number of rows affected by an INSERT, UPDATE, or DELETE statement. While the DBAPI spec apparently unfortunately states it can also apply to the number of rows from a SELECT statement, this is usually not the case in reality. In particular it usually requires the DBAPI to fully fetch all rows unconditionally, a generally undesirable behavior that a lot of DBAPIs still do by default (though newer DBAPIs are doing this less). In practice, a SELECT statement could represent millions of rows. If the RDBMS has not applied any GROUP BY or ORDER BY criterion to the rows, it doesn't even know how many rows it will find before it starts returning them. To get the total number of rows from a SELECT in a platform agnostic way, either execute a select count(*) with your criterion, or just fetch the whole result. Its not feasable for SQLAlchemy to force all results into a buffer just so this attribute could be available since its extremely inefficient. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: obtaining pid of forked process
On Fri, 12 Feb 2010 13:33:01 +0100, Alex Brasetvik a...@brasetvik.com wrote: On Feb 11, 2010, at 18:58 , Faheem Mitha wrote: sqlalchemy forks a process when it calls the db No, it does not. PostgreSQL forks a new backend process when a connection is established, however. It sounds like that's what you want. Do SELECT pg_backend_pid() to get the PID of the backend process serving your connection. That and other stat functions are documented here: http://www.postgresql.org/docs/current/static/monitoring-stats.html I see. Thanks, that's very helpful. Does the pid/process stay the same across successive calls to text()? I'm guessing that successive calls to text() would take place within the same session, and therefore correspond to the same backend session. If not, how can I get it to return the pid before I start the actual query? In any case, I'll experiment with this. Regards, Faheem. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: obtaining pid of forked process
On Thu, 11 Feb 2010 13:06:03 -0500, Michael Bayer mike...@zzzcomputing.com wrote: Faheem Mitha wrote: Hi, sqlalchemy forks a process when it calls the db (in my case PostgreSQL, but I don't think it matters) using, for example from sqlalchemy.sql import text s = text(...) um, what ? there's no forking in SQLAlchemy. Ok. Apologies for my cluelessness. Regards, Faheem. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Oracle in clause limit
grach wrote: Hello all, I'm relatively new to SQLAlchemy - is there any elegant workaround that SQLAlchemy provides for queries with in clause that contains more than 1000 items? I have, say, date, item, value table that I'd like to query for arbitrary set of dates and items (date and item list is provided by the user or generated by the program. (I'd like to avoid creating temp tables with arguments and perfoming a join). Since you don't want to use a subquery/temp table, there's no other option except to iteratively execute N number of queries where N is (number of items / 1000), and piece the results together. it can be as easy as: def select_lots_of_stuff(collection): while collection: items, collection = collection[:1000], collection[1000:] for result in conn.execute(select.where(col.in_(items))): yield result Many thanks, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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] Re: obtaining pid of forked process
Faheem Mitha wrote: PostgreSQL forks a new backend process when a connection is established, however. It sounds like that's what you want. Do SELECT pg_backend_pid() to get the PID of the backend process serving your connection. That and other stat functions are documented here: http://www.postgresql.org/docs/current/static/monitoring-stats.html I see. Thanks, that's very helpful. Does the pid/process stay the same across successive calls to text()? I'm guessing that successive calls to text() would take place within the same session, and therefore correspond to the same backend session. If not, how can I get it to return the pid before I start the actual query? In any case, I'll experiment with this. you would connect: conn = engine.connect() check the PID: pid = conn.execute(SELECT pg_backend_pid()).scalar() then continue as needed: conn.execute(text(...)) Regards, Faheem. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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: obtaining pid of forked process
On Fri, 12 Feb 2010 11:01:23 -0500, Michael Bayer mike...@zzzcomputing.com wrote: you would connect: conn = engine.connect() check the PID: pid = conn.execute(SELECT pg_backend_pid()).scalar() then continue as needed: conn.execute(text(...)) Thanks, Michael. That's very clear and helpful. Regards, Faheem. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Oracle in clause limit
Thanks! Michael Bayer mike...@zzzcomputing.com Sent by: sqlalchemy@googlegroups.com 02/12/2010 10:48 AM Please respond to sqlalchemy@googlegroups.com To sqlalchemy@googlegroups.com cc Subject Re: [sqlalchemy] Oracle in clause limit grach wrote: Hello all, I'm relatively new to SQLAlchemy - is there any elegant workaround that SQLAlchemy provides for queries with in clause that contains more than 1000 items? I have, say, date, item, value table that I'd like to query for arbitrary set of dates and items (date and item list is provided by the user or generated by the program. (I'd like to avoid creating temp tables with arguments and perfoming a join). Since you don't want to use a subquery/temp table, there's no other option except to iteratively execute N number of queries where N is (number of items / 1000), and piece the results together. it can be as easy as: def select_lots_of_stuff(collection): while collection: items, collection = collection[:1000], collection[1000:] for result in conn.execute(select.where(col.in_(items))): yield result Many thanks, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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. --- This communication may contain confidential and/or privileged information. If you are not the intended recipient (or have received this communication in error) please notify the sender immediately and destroy this communication. Any unauthorized copying, disclosure or distribution of the material in this communication is strictly forbidden. Deutsche Bank does not render legal or tax advice, and the information contained in this communication should not be regarded as such. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] inspecting whether a mapped property has been set without referencing it
If I have a one to many RelationProperty, which uses a list, how can I check if this has been set without actually referencing it? It seems once I reference it, it sets it to an empty list if it hasn't been set already. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: Insert from Select Implentation
To partially clarify and answer my own question here (I was very tired by the time I pasted this last night) In the case of: sa .insert (mytable ).values(myothertable.select().filter_by(foo=sa.bindparam(bar)) This doesn't currently work because the bindparam required for the select statement gets discarded. Having gone through the way SA compiles statements, it appears that parameters are used to fill in an insert statements values. The only way I can see of getting around this is that at the time of adding the values, they bindparams are extracted from the select and added to a new attribute on the insert statement, and around the time of creating the ExecutionContext, they are processed and added back into the query. My previous question about getting both the column names and the bindparams, I managed to solve by (duh!) getting the column names, compiling the select statement and then getting the bindparams. I would still be interested to know where the bindparams are stored in an uncompiled select though. I couldn't work it out. Sorry if all this is a little fuzzy. It's my first time at really trying to understand what SA does behind the scenes. Thanks Ed On 11 Feb 2010, at 22:23, Ed Singleton wrote: I've been having a clumsy hack at enabling myself to pass a select statement as a value to an insert statement. IE: sa.insert(mytable).values(myothertable.select()) I've got it working in that most basic case, but I'm struggling when the select statement has bindparams. The insert needs to take them, as well as (for some dialects) the column names of the select. The thing is I can work out how to get the bindparams from a compiled statement, and I can work out how to get the column names from a uncompiled statement, but I can't work out how to get both from one or the other. Any hints on this would be appreciated. Also, I slightly worried that there's an obvious reason why this hasn't been done before. Is there something stupid I'm missing? If anyone's tried patching SA to do this I would greatly appreciate any advice. Thanks Ed -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Re: Insert from Select Implentation
Ed Singleton wrote: To partially clarify and answer my own question here (I was very tired by the time I pasted this last night) In the case of: sa .insert (mytable ).values(myothertable.select().filter_by(foo=sa.bindparam(bar)) This doesn't currently work because the bindparam required for the select statement gets discarded. Having gone through the way SA compiles statements, it appears that parameters are used to fill in an insert statements values. The only way I can see of getting around this is that at the time of adding the values, they bindparams are extracted from the select and added to a new attribute on the insert statement, and around the time of creating the ExecutionContext, they are processed and added back into the query. My previous question about getting both the column names and the bindparams, I managed to solve by (duh!) getting the column names, compiling the select statement and then getting the bindparams. I would still be interested to know where the bindparams are stored in an uncompiled select though. I couldn't work it out. Sorry if all this is a little fuzzy. It's my first time at really trying to understand what SA does behind the scenes. if you're using the @compiler extension to generate this, the same compiler object would generate the string representation of both the insert() and the select(), gathering bindparam() objects from the structure as it goes. the params structure embedded on the Compiled at the end is what gets sent to execute() along with any ad-hoc values. The example here: http://www.sqlalchemy.org/docs/reference/ext/compiler.html?highlight=compiler#compiling-sub-elements-of-a-custom-expression-construct should accomplish all this. Thanks Ed On 11 Feb 2010, at 22:23, Ed Singleton wrote: I've been having a clumsy hack at enabling myself to pass a select statement as a value to an insert statement. IE: sa.insert(mytable).values(myothertable.select()) I've got it working in that most basic case, but I'm struggling when the select statement has bindparams. The insert needs to take them, as well as (for some dialects) the column names of the select. The thing is I can work out how to get the bindparams from a compiled statement, and I can work out how to get the column names from a uncompiled statement, but I can't work out how to get both from one or the other. Any hints on this would be appreciated. Also, I slightly worried that there's an obvious reason why this hasn't been done before. Is there something stupid I'm missing? If anyone's tried patching SA to do this I would greatly appreciate any advice. Thanks Ed -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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] Re: Insert from Select Implentation
On 12 Feb 2010, at 17:43, Michael Bayer wrote: Ed Singleton wrote: To partially clarify and answer my own question here (I was very tired by the time I pasted this last night) In the case of: sa .insert (mytable ).values(myothertable.select().filter_by(foo=sa.bindparam(bar)) This doesn't currently work because the bindparam required for the select statement gets discarded. Having gone through the way SA compiles statements, it appears that parameters are used to fill in an insert statements values. The only way I can see of getting around this is that at the time of adding the values, they bindparams are extracted from the select and added to a new attribute on the insert statement, and around the time of creating the ExecutionContext, they are processed and added back into the query. My previous question about getting both the column names and the bindparams, I managed to solve by (duh!) getting the column names, compiling the select statement and then getting the bindparams. I would still be interested to know where the bindparams are stored in an uncompiled select though. I couldn't work it out. Sorry if all this is a little fuzzy. It's my first time at really trying to understand what SA does behind the scenes. if you're using the @compiler extension to generate this, the same compiler object would generate the string representation of both the insert() and the select(), gathering bindparam() objects from the structure as it goes. the params structure embedded on the Compiled at the end is what gets sent to execute() along with any ad-hoc values. The example here: http://www.sqlalchemy.org/docs/reference/ext/compiler.html?highlight=compiler#compiling-sub-elements-of-a-custom-expression-construct should accomplish all this. You mean I've spent a day googling and two days reading SA source code and that's all I had to do? I am both miffed and delighted at the same time. I didn't know about this compiler extension and had been hacking away at the SA source to get it working as a default feature of insert statements. I'll try this out post-haste. Thanks again Ed -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Re: Insert from Select Implentation
On 12 Feb 2010, at 17:43, Michael Bayer wrote:
Ed Singleton wrote:
In the case of:
sa
.insert
(mytable
).values(myothertable.select().filter_by(foo=sa.bindparam(bar))
This doesn't currently work because... [snip]
if you're using the @compiler extension to generate this, the same
compiler object would generate the string representation of both the
insert() and the select(), gathering bindparam() objects from the
structure as it goes. the params structure embedded on the Compiled
at
the end is what gets sent to execute() along with any ad-hoc values.
The example here:
http://www.sqlalchemy.org/docs/reference/ext/compiler.html?highlight=compiler#compiling-sub-elements-of-a-custom-expression-construct
should accomplish all this.
I've had a go at this. I had to modify the string template slightly
to suit sqlites insert from syntax, but otherwise pretty much copy and
pasted from the docs. I keep getting the following error (sample
script below):
Traceback (most recent call last):
File lib/nm_mail/insert_from_test.py, line 58, in module
session.execute(ins)
File /Users/singletoned/.envs/newman-mail/lib/python2.6/site-
packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/orm/session.py,
line 737, in execute
clause, params or {})
File /Users/singletoned/.envs/newman-mail/lib/python2.6/site-
packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/engine/base.py,
line 1035, in execute
return Connection.executors[c](self, object, multiparams, params)
File /Users/singletoned/.envs/newman-mail/lib/python2.6/site-
packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/engine/base.py,
line 1095, in _execute_clauseelement
parameters=params
File /Users/singletoned/.envs/newman-mail/lib/python2.6/site-
packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/engine/base.py,
line 1170, in __create_execution_context
return dialect.execution_ctx_cls(dialect, connection=self,
**kwargs)
File /Users/singletoned/.envs/newman-mail/lib/python2.6/site-
packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/engine/default.py,
line 271, in __init__
raise exc.ArgumentError(Not an executable clause: %s % compiled)
sqlalchemy.exc.ArgumentError: Not an executable clause: INSERT INTO
Foo (id, name, body) SELECT Bar.id, Bar.name, Bar.body
FROM Bar
# -*- coding: utf-8 -*-
import sqlalchemy.orm
import sqlalchemy as sa
uri = sqlite://
metadata = sa.MetaData()
engine = sa.create_engine(uri)
metadata.bind = engine
Session = sa.orm.sessionmaker(bind=engine)
foo_table = sa.Table(Foo, metadata,
sa.Column(id, sa.Integer, primary_key=True),
sa.Column(name, sa.String(64)),
sa.Column(body, sa.String),
)
bar_table = sa.Table(Bar, metadata,
sa.Column(id, sa.Integer, primary_key=True),
sa.Column(name, sa.String(64)),
sa.Column(body, sa.String),
)
metadata.create_all()
session = Session()
q = bar_table.insert()
session.execute(q.values(name=bar1, body=blah))
session.execute(q.values(name=bar2, body=blah))
session.execute(q.values(name=bar3, body=flibble))
session.commit()
session.close()
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import ClauseElement
class InsertFromSelect(ClauseElement):
def __init__(self, table, select):
self.table = table
self.select = select
@compiles(InsertFromSelect)
def visit_insert_from_select(element, compiler, **kw):
return INSERT INTO %s (%s) %s % (
compiler.process(element.table, asfrom=True),
, .join([col.name for col in element.select.columns]),
compiler.process(element.select),
)
s = bar_table.select()
ins = InsertFromSelect(foo_table, s)
print ins
session.execute(ins)
session.commit()
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: Insert from Select Implentation
Ed Singleton wrote: class InsertFromSelect(ClauseElement): def __init__(self, table, select): self.table = table self.select = select @compiles(InsertFromSelect) def visit_insert_from_select(element, compiler, **kw): return INSERT INTO %s (%s) %s % ( compiler.process(element.table, asfrom=True), , .join([col.name for col in element.select.columns]), compiler.process(element.select), ) add the mixin sqlalchemy.sql.expression._Executable to your InsertFromSelect class.I'm going to rename it to Executable and will add it to the docs now. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Warnings take a really long time / NotImplementedError
If you prefer, you can reflect your database once and store the resulting MetaData (or individual Table objects) into a pickled datafile. Your application can then read the datafile upon startup to configure its previously loaded table metadata. The serializer extension makes this possible, requiring just a MetaData object when deserializing (the Session is optional). Pass a MetaData or Table to serializer.dumps() to generate the dump and use serializer.loads() to load it back: http://www.sqlalchemy.org/docs/reference/ext/serializer.html Jeff Peterson wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Thursday, February 11, 2010 1:26 PM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Warnings take a really long time / NotImplementedError Jeff Peterson wrote: The view name itself isn't but the names of all the tables that make up that view are. So I guess that must be why. It is only looking at the columns declared in your view - the Table reflection logic doesn't actually look at the original definition of the view (there is a function for that available but that's not what you're using here). I'm not familiar with what Oracle does here but if it places view columns into ALL_CONS_COLUMNS corresponding to the table column they represent, that would be the effect. But it seems strange that would be the case, since there's no constraint on your view. The other possibility is that you are actually reflecting tables somewhere else. If I am it's not on purpose. ;) I was able to make one observation though...during my test, trying to get all the kinks worked out I setup 2 connection strings, 1) the schema owner (who has rights to everything) and 2) my limited user that only has select rights on certain views. When this happens, I am connected as the schema user. When connected as the limited user it's lightning fast (I commented out the create code in the lib, I can't create new tables as it sits but it'll reflect just fine). So, bottom line is, despite the strangeness, I guess I can, just not worry about it, at least for now. But it's clear that when it can't touch those tables it doesn't perform those commands. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Thursday, February 11, 2010 12:59 PM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Warnings take a really long time / NotImplementedError Jeff Peterson wrote: That is the troubling part, I am reflecting a view, and yet it is still touching all those tables in the DB for schema='CRAR1APP' does the name of your view appear at all in ALL_CONS_COLUMNS.TABLE_NAME ? that's the only way reflection of a view could get the name of a table to reflect.if you turn on echo='debug' or set sqlalchemy.engine to DEBUG level logging, you'd see all the rows returned from every query. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Thursday, February 11, 2010 12:05 PM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Warnings take a really long time / NotImplementedError I thought you were reflecting a view ? a table will fan out to all of its constraints, yes. Jeff Peterson wrote: Right, and there is that same code outputted for every table in the schema, when reflecting that one view. What I posted was just the one snippet, it is repeated over and over for each different table. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Thursday, February 11, 2010 11:46 AM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Warnings take a really long time / NotImplementedError that SQL output is specific to 'table_name': 'CFA_CASH_FLOW_STATUS_TAB' and 'table_name': 'CFA_CASH_FLOW_TAB'. that's two tables. Jeff Peterson wrote: -- Jeffrey D Peterson Webmaster Crary Industries, Inc. From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Wednesday, February 10, 2010 6:30 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Warnings take a really long time / NotImplementedError On Feb 10, 2010, at 6:05 PM, Jeff Peterson wrote: It's touching a ton of tables, hundreds...if I had to guess I'd say every
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
Michael Bayer wrote: If you prefer, you can reflect your database once and store the resulting MetaData (or individual Table objects) into a pickled datafile. Your application can then read the datafile upon startup to configure its previously loaded table metadata. The serializer extension makes this possible, requiring just a MetaData object when deserializing (the Session is optional). Pass a MetaData or Table to serializer.dumps() to generate the dump and use serializer.loads() to load it back: http://www.sqlalchemy.org/docs/reference/ext/serializer.html slight adjustment. don't use the serializer extension, just use plain pickle.dumps()/loads() of the whole MetaData object. Jeff Peterson wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Thursday, February 11, 2010 1:26 PM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Warnings take a really long time / NotImplementedError Jeff Peterson wrote: The view name itself isn't but the names of all the tables that make up that view are. So I guess that must be why. It is only looking at the columns declared in your view - the Table reflection logic doesn't actually look at the original definition of the view (there is a function for that available but that's not what you're using here). I'm not familiar with what Oracle does here but if it places view columns into ALL_CONS_COLUMNS corresponding to the table column they represent, that would be the effect. But it seems strange that would be the case, since there's no constraint on your view. The other possibility is that you are actually reflecting tables somewhere else. If I am it's not on purpose. ;) I was able to make one observation though...during my test, trying to get all the kinks worked out I setup 2 connection strings, 1) the schema owner (who has rights to everything) and 2) my limited user that only has select rights on certain views. When this happens, I am connected as the schema user. When connected as the limited user it's lightning fast (I commented out the create code in the lib, I can't create new tables as it sits but it'll reflect just fine). So, bottom line is, despite the strangeness, I guess I can, just not worry about it, at least for now. But it's clear that when it can't touch those tables it doesn't perform those commands. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Thursday, February 11, 2010 12:59 PM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Warnings take a really long time / NotImplementedError Jeff Peterson wrote: That is the troubling part, I am reflecting a view, and yet it is still touching all those tables in the DB for schema='CRAR1APP' does the name of your view appear at all in ALL_CONS_COLUMNS.TABLE_NAME ? that's the only way reflection of a view could get the name of a table to reflect.if you turn on echo='debug' or set sqlalchemy.engine to DEBUG level logging, you'd see all the rows returned from every query. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Thursday, February 11, 2010 12:05 PM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Warnings take a really long time / NotImplementedError I thought you were reflecting a view ? a table will fan out to all of its constraints, yes. Jeff Peterson wrote: Right, and there is that same code outputted for every table in the schema, when reflecting that one view. What I posted was just the one snippet, it is repeated over and over for each different table. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Thursday, February 11, 2010 11:46 AM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Warnings take a really long time / NotImplementedError that SQL output is specific to 'table_name': 'CFA_CASH_FLOW_STATUS_TAB' and 'table_name': 'CFA_CASH_FLOW_TAB'. that's two tables. Jeff Peterson wrote: -- Jeffrey D Peterson Webmaster Crary Industries, Inc. From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Wednesday, February 10, 2010 6:30 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Warnings take a really long time / NotImplementedError
Re: [sqlalchemy] Re: Insert from Select Implentation
On 12 Feb 2010, at 19:36, Michael Bayer wrote: Ed Singleton wrote: class InsertFromSelect(ClauseElement): def __init__(self, table, select): self.table = table self.select = select @compiles(InsertFromSelect) def visit_insert_from_select(element, compiler, **kw): return INSERT INTO %s (%s) %s % ( compiler.process(element.table, asfrom=True), , .join([col.name for col in element.select.columns]), compiler.process(element.select), ) add the mixin sqlalchemy.sql.expression._Executable to your InsertFromSelect class.I'm going to rename it to Executable and will add it to the docs now. Okay, I've modified it to this, and it seems to work perfectly from sqlalchemy.sql.expression import ClauseElement, _Executable class InsertFromSelect( _Executable, ClauseElement): def __init__(self, table, select): self.table = table self.select = select @compiles(InsertFromSelect) def visit_insert_from_select(element, compiler, **kw): return INSERT INTO %s (%s) %s % ( compiler.process(element.table, asfrom=True), , .join([col.name for col in element.select.columns]), compiler.process(element.select), ) Thanks for all your help. You are a king. (If not one who leads me to throw away the hacking I did on SA. And I was so close to getting it working). Ed -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
