[sqlalchemy] Re: multiple statements in a ddl construct
hi jason,
after a second thought, i have used your idea for data preloading. it is way
more pythonic and portable that ddl.
thanks again,
alex
On Mon, Sep 8, 2008 at 6:45 PM, jason kirtland [EMAIL PROTECTED] wrote:
alex bodnaru wrote:
hello friends,
i wanted to do a few sql commands in a ddl construct, but i failed with:
pysqlite2:dbapi2 warning: you can execute one statement at a time
i'm not very familiar with python db layer, but i know sqlite may be invoked
specifically to execute one, or many statements divided by ';', so i suspect
it's specifically invoked for one statement.
while this is a good security measure for sql from untrusted sources, like
user
input, it's quite annoying for a situation where free sql should be
specifically
added.
as for my case, i had a batch of inserts based on an external file,
and i couldn't
invoke ddl.execute_at in a loop, so i had to switch to inserting a
batch of unioned
selects in one insert, which was nice to learn :).
The use case behind the DDL() construct is a single statement. You can
fire multiple statements by using multiple DDL()s. But for inserts,
I've found it more useful to write a 'after-create' event listener from
scratch. Here's one that I use in pretty much every project, in some
form or another:
def fixture(table, column_names, *rows):
Insert data into table after creation.
def onload(event, schema_item, connection):
insert = table.insert()
connection.execute(
insert,
[dict(zip(column_names, column_values))
for column_values in rows])
table.append_ddl_listener('after-create', onload)
Looks like this in use:
fixture(some_table,
('x', 'y'),
(1, 2),
(3, 4),
(5, 6))
--~--~-~--~~~---~--~~
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: multiple statements in a ddl construct
On Monday 08 September 2008 18:45:17 jason kirtland wrote:
alex bodnaru wrote:
hello friends,
i wanted to do a few sql commands in a ddl construct, but i
failed with:
pysqlite2:dbapi2 warning: you can execute one statement at a time
i'm not very familiar with python db layer, but i know sqlite may
be invoked specifically to execute one, or many statements
divided by ';', so i suspect it's specifically invoked for one
statement.
while this is a good security measure for sql from untrusted
sources, like user input, it's quite annoying for a situation
where free sql should be specifically
added.
as for my case, i had a batch of inserts based on an external
file, and i couldn't
invoke ddl.execute_at in a loop, so i had to switch to inserting
a batch of unioned
selects in one insert, which was nice to learn :).
The use case behind the DDL() construct is a single statement. You
can fire multiple statements by using multiple DDL()s. But for
inserts, I've found it more useful to write a 'after-create' event
listener from scratch. Here's one that I use in pretty much every
project, in some form or another:
def fixture(table, column_names, *rows):
Insert data into table after creation.
def onload(event, schema_item, connection):
insert = table.insert()
connection.execute(
insert,
[dict(zip(column_names, column_values))
for column_values in rows])
table.append_ddl_listener('after-create', onload)
Looks like this in use:
fixture(some_table,
('x', 'y'),
(1, 2),
(3, 4),
(5, 6))
hmm.. interesting. how would u do an initial insert of batch of
objects (orm-mapped to whatever entangled bunch of tables)? any
possible optimization?
for obj in objfactory(somedicts): sess.save(obj); sess.flush()
isn't very fast thing...
any needed gymnastics with the objects is possible (grouping by type
or whatever)
--~--~-~--~~~---~--~~
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: multiple statements in a ddl construct
hi jason,
On Mon, Sep 8, 2008 at 6:45 PM, jason kirtland [EMAIL PROTECTED] wrote:
alex bodnaru wrote:
hello friends,
i wanted to do a few sql commands in a ddl construct, but i failed with:
pysqlite2:dbapi2 warning: you can execute one statement at a time
i'm not very familiar with python db layer, but i know sqlite may be invoked
specifically to execute one, or many statements divided by ';', so i suspect
it's specifically invoked for one statement.
while this is a good security measure for sql from untrusted sources, like
user
input, it's quite annoying for a situation where free sql should be
specifically
added.
as for my case, i had a batch of inserts based on an external file,
and i couldn't
invoke ddl.execute_at in a loop, so i had to switch to inserting a
batch of unioned
selects in one insert, which was nice to learn :).
The use case behind the DDL() construct is a single statement. You can
fire multiple statements by using multiple DDL()s. But for inserts,
I've found it more useful to write a 'after-create' event listener from
scratch. Here's one that I use in pretty much every project, in some
form or another:
def fixture(table, column_names, *rows):
Insert data into table after creation.
def onload(event, schema_item, connection):
insert = table.insert()
connection.execute(
insert,
[dict(zip(column_names, column_values))
for column_values in rows])
table.append_ddl_listener('after-create', onload)
Looks like this in use:
fixture(some_table,
('x', 'y'),
(1, 2),
(3, 4),
(5, 6))
thanks for your idea. it looks cool. i understand this will be
triggered after all DDL end.
but i wanted to do arbitrary sql as DDL provides, and the insert was
just an example for a
series of statements.
just wondered why would this be the place to limit to one statement:
it isn't parsed by SA
in any way.
btw, my case is a generic ddl extension to elixir, made to be run
once. but i made it receive
a list of commands, and register each command in turn, and did the job.
best regards,
alex
--~--~-~--~~~---~--~~
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: multiple statements in a ddl construct
hi az,
thanks for commenting.
loading the whole (elixir) model to the (sqlite) db took about 3 seconds,
and it being done once anyway.
so jetflight performance was not a constraint ;) .
best regards,
alex
On Tue, Sep 9, 2008 at 9:53 AM, [EMAIL PROTECTED] wrote:
On Monday 08 September 2008 18:45:17 jason kirtland wrote:
alex bodnaru wrote:
hello friends,
i wanted to do a few sql commands in a ddl construct, but i
failed with:
pysqlite2:dbapi2 warning: you can execute one statement at a time
i'm not very familiar with python db layer, but i know sqlite may
be invoked specifically to execute one, or many statements
divided by ';', so i suspect it's specifically invoked for one
statement.
while this is a good security measure for sql from untrusted
sources, like user input, it's quite annoying for a situation
where free sql should be specifically
added.
as for my case, i had a batch of inserts based on an external
file, and i couldn't
invoke ddl.execute_at in a loop, so i had to switch to inserting
a batch of unioned
selects in one insert, which was nice to learn :).
The use case behind the DDL() construct is a single statement. You
can fire multiple statements by using multiple DDL()s. But for
inserts, I've found it more useful to write a 'after-create' event
listener from scratch. Here's one that I use in pretty much every
project, in some form or another:
def fixture(table, column_names, *rows):
Insert data into table after creation.
def onload(event, schema_item, connection):
insert = table.insert()
connection.execute(
insert,
[dict(zip(column_names, column_values))
for column_values in rows])
table.append_ddl_listener('after-create', onload)
Looks like this in use:
fixture(some_table,
('x', 'y'),
(1, 2),
(3, 4),
(5, 6))
hmm.. interesting. how would u do an initial insert of batch of
objects (orm-mapped to whatever entangled bunch of tables)? any
possible optimization?
for obj in objfactory(somedicts): sess.save(obj); sess.flush()
isn't very fast thing...
any needed gymnastics with the objects is possible (grouping by type
or whatever)
--~--~-~--~~~---~--~~
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: multiple statements in a ddl construct
On Tuesday 09 September 2008 18:19:59 alex bodnaru wrote: hi az, thanks for commenting. loading the whole (elixir) model to the (sqlite) db took about 3 seconds, and it being done once anyway. so jetflight performance was not a constraint ;) . ... hmm.. interesting. how would u do an initial insert of batch of objects (orm-mapped to whatever entangled bunch of tables)? any possible optimization? for obj in objfactory(somedicts): sess.save(obj); sess.flush() isn't very fast thing... any needed gymnastics with the objects is possible (grouping by type or whatever) ah, no worries, that was a question to jason. in my case it is about inserting several hundreds/thousands objects. --~--~-~--~~~---~--~~ 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: multiple statements in a ddl construct
[EMAIL PROTECTED] wrote:
On Monday 08 September 2008 18:45:17 jason kirtland wrote:
alex bodnaru wrote:
hello friends,
i wanted to do a few sql commands in a ddl construct, but i
failed with:
pysqlite2:dbapi2 warning: you can execute one statement at a time
i'm not very familiar with python db layer, but i know sqlite may
be invoked specifically to execute one, or many statements
divided by ';', so i suspect it's specifically invoked for one
statement.
while this is a good security measure for sql from untrusted
sources, like user input, it's quite annoying for a situation
where free sql should be specifically
added.
as for my case, i had a batch of inserts based on an external
file, and i couldn't
invoke ddl.execute_at in a loop, so i had to switch to inserting
a batch of unioned
selects in one insert, which was nice to learn :).
The use case behind the DDL() construct is a single statement. You
can fire multiple statements by using multiple DDL()s. But for
inserts, I've found it more useful to write a 'after-create' event
listener from scratch. Here's one that I use in pretty much every
project, in some form or another:
def fixture(table, column_names, *rows):
Insert data into table after creation.
def onload(event, schema_item, connection):
insert = table.insert()
connection.execute(
insert,
[dict(zip(column_names, column_values))
for column_values in rows])
table.append_ddl_listener('after-create', onload)
Looks like this in use:
fixture(some_table,
('x', 'y'),
(1, 2),
(3, 4),
(5, 6))
hmm.. interesting. how would u do an initial insert of batch of
objects (orm-mapped to whatever entangled bunch of tables)? any
possible optimization?
for obj in objfactory(somedicts): sess.save(obj); sess.flush()
isn't very fast thing...
any needed gymnastics with the objects is possible (grouping by type
or whatever)
I don't see any optimizations offhand for ORM-driven loads. The general
case is going to have dependencies on the niceties that the ORM
provides, like assoc proxies, mapper extension actions on insert, etc.
--~--~-~--~~~---~--~~
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: multiple statements in a ddl construct
alex bodnaru wrote:
hi jason,
On Mon, Sep 8, 2008 at 6:45 PM, jason kirtland [EMAIL PROTECTED] wrote:
alex bodnaru wrote:
hello friends,
i wanted to do a few sql commands in a ddl construct, but i failed with:
pysqlite2:dbapi2 warning: you can execute one statement at a time
i'm not very familiar with python db layer, but i know sqlite may be invoked
specifically to execute one, or many statements divided by ';', so i suspect
it's specifically invoked for one statement.
while this is a good security measure for sql from untrusted sources, like
user
input, it's quite annoying for a situation where free sql should be
specifically
added.
as for my case, i had a batch of inserts based on an external file,
and i couldn't
invoke ddl.execute_at in a loop, so i had to switch to inserting a
batch of unioned
selects in one insert, which was nice to learn :).
The use case behind the DDL() construct is a single statement. You can
fire multiple statements by using multiple DDL()s. But for inserts,
I've found it more useful to write a 'after-create' event listener from
scratch. Here's one that I use in pretty much every project, in some
form or another:
def fixture(table, column_names, *rows):
Insert data into table after creation.
def onload(event, schema_item, connection):
insert = table.insert()
connection.execute(
insert,
[dict(zip(column_names, column_values))
for column_values in rows])
table.append_ddl_listener('after-create', onload)
Looks like this in use:
fixture(some_table,
('x', 'y'),
(1, 2),
(3, 4),
(5, 6))
thanks for your idea. it looks cool. i understand this will be
triggered after all DDL end.
but i wanted to do arbitrary sql as DDL provides, and the insert was
just an example for a
series of statements.
The DDL() function is just some sugar for the DDL event shown interface
above. If you want to perform more than one statement, the API is in
place for any customization you'd like.
just wondered why would this be the place to limit to one statement:
it isn't parsed by SA
in any way.
SA doesn't put any limits on what SQL gets pushed through. It's passed
through directly to the DB-API execute() method. I'd guess that most
DB-API implementations will probably reject multiple statements in a
single execution.
--~--~-~--~~~---~--~~
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: multiple statements in a ddl construct
hi jason,
On Tue, Sep 9, 2008 at 9:59 PM, jason kirtland [EMAIL PROTECTED] wrote:
alex bodnaru wrote:
hi jason,
On Mon, Sep 8, 2008 at 6:45 PM, jason kirtland [EMAIL PROTECTED] wrote:
alex bodnaru wrote:
hello friends,
i wanted to do a few sql commands in a ddl construct, but i failed with:
pysqlite2:dbapi2 warning: you can execute one statement at a time
i'm not very familiar with python db layer, but i know sqlite may be
invoked
specifically to execute one, or many statements divided by ';', so i
suspect
it's specifically invoked for one statement.
while this is a good security measure for sql from untrusted sources, like
user
input, it's quite annoying for a situation where free sql should be
specifically
added.
as for my case, i had a batch of inserts based on an external file,
and i couldn't
invoke ddl.execute_at in a loop, so i had to switch to inserting a
batch of unioned
selects in one insert, which was nice to learn :).
The use case behind the DDL() construct is a single statement. You can
fire multiple statements by using multiple DDL()s. But for inserts,
I've found it more useful to write a 'after-create' event listener from
scratch. Here's one that I use in pretty much every project, in some
form or another:
def fixture(table, column_names, *rows):
Insert data into table after creation.
def onload(event, schema_item, connection):
insert = table.insert()
connection.execute(
insert,
[dict(zip(column_names, column_values))
for column_values in rows])
table.append_ddl_listener('after-create', onload)
Looks like this in use:
fixture(some_table,
('x', 'y'),
(1, 2),
(3, 4),
(5, 6))
thanks for your idea. it looks cool. i understand this will be
triggered after all DDL end.
but i wanted to do arbitrary sql as DDL provides, and the insert was
just an example for a
series of statements.
The DDL() function is just some sugar for the DDL event shown interface
above. If you want to perform more than one statement, the API is in
place for any customization you'd like.
just wondered why would this be the place to limit to one statement:
it isn't parsed by SA
in any way.
SA doesn't put any limits on what SQL gets pushed through. It's passed
through directly to the DB-API execute() method. I'd guess that most
DB-API implementations will probably reject multiple statements in a
single execution.
i really have no experience with pydb. but i know for sure that sqlite can
either work with one statement or with multiple ones, depending on the caller's
option.
alex
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
