Re: [sqlalchemy] default value in multiple insert
On May 18, 2010, at 10:06 PM, Dan Kuebrich wrote:
My apologies; I expressed my question rather incoherently.
if the question is, I want to multiple insert like [{'a':1, 'b':2, 'c':3},
{'a':2}, {'a':3, 'b':4}], etc. only some dictionaries are missing different
keys, that is not allowed. The structure of the SQL statement as parsed by
MySQL determines for which columns the server-side default is emitted and
there is only one version of that with an executemany. So every param
dictionary must have at least all the keys which the first one does.
This was the basic scenario of my question. However, unless I misunderstand,
it does seem to be allowed: the missing values are replaced with NULL in
the generated query.
it should not be in SQLA 0.6.Specific logic was added to disallow this.
But what I actually meant to ask was this: sqlalchemy handles missing
dictionary entries as NULL for the purpose of inserts, is there an easy way
to have it generate a SQL statement with DEFAULT for missing dictionary
entries?
Eg. execute(testtable.insert(), [{'a':1, 'b':2, 'c':3}, {'a':2}, {'a':3,
'b':4}]) = INSERT INTO testtable (a,b,c) VALUES
(1,2,3),(2,DEFAULT,DEFAULT),(3,4,DEFAULT)
ive never seen that syntax before (i.e. DEFAULT is actually present as a
value).Assuming its valid, you could achieve it using a client side
default as I mentioned earlier.i.e. Column('foo', Integer,
default=text('DEFAULT')). But again, not with an executemany() and
heterogeneous dictinoaries as you have above. The SQL statement is rendered
only once, and either has a bind parameter for a particular position or not.
--
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] default value in multiple insert
Michael--thanks for your help. As you probably guessed, I'm still running
0.5. DEFAULT is, in fact, a valid MySQL keyword, though I didn't know about
it before looking into this problem.
On Wed, May 19, 2010 at 10:09 AM, Michael Bayer mike...@zzzcomputing.comwrote:
On May 18, 2010, at 10:06 PM, Dan Kuebrich wrote:
My apologies; I expressed my question rather incoherently.
if the question is, I want to multiple insert like [{'a':1, 'b':2, 'c':3},
{'a':2}, {'a':3, 'b':4}], etc. only some dictionaries are missing different
keys, that is not allowed. The structure of the SQL statement as parsed by
MySQL determines for which columns the server-side default is emitted and
there is only one version of that with an executemany. So every param
dictionary must have at least all the keys which the first one does.
This was the basic scenario of my question. However, unless I
misunderstand, it does seem to be allowed: the missing values are replaced
with NULL in the generated query.
it should not be in SQLA 0.6.Specific logic was added to disallow this.
But what I actually meant to ask was this: sqlalchemy handles missing
dictionary entries as NULL for the purpose of inserts, is there an easy way
to have it generate a SQL statement with DEFAULT for missing dictionary
entries?
Eg. execute(testtable.insert(), [{'a':1, 'b':2, 'c':3}, {'a':2}, {'a':3,
'b':4}]) = INSERT INTO testtable (a,b,c) VALUES
(1,2,3),(2,DEFAULT,DEFAULT),(3,4,DEFAULT)
ive never seen that syntax before (i.e. DEFAULT is actually present as a
value).Assuming its valid, you could achieve it using a client side
default as I mentioned earlier.i.e. Column('foo', Integer,
default=text('DEFAULT')). But again, not with an executemany() and
heterogeneous dictinoaries as you have above. The SQL statement is
rendered only once, and either has a bind parameter for a particular
position or not.
--
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.comsqlalchemy%2bunsubscr...@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] default value in multiple insert
I found a similar thread from about a year ago (http:// groups.google.com/group/sqlalchemy/browse_thread/thread/ 66ef04fd10fd2be/ec7784b70abedabe), but it never seemed to answer the most burning question: is there a way in sqlalchemy to do a multiple insert with default values for unspecified columns? One way this might be possible in SQL is to use the DEFAULT keyword, but I haven't found anything about it in sqlalchemy. Below I have SQL that shows: a) the current response of sqlalchemy to a multiple insert with a row dict missing a value for a column. b) the usage of the DEFAULT keyword I am referring to mysql create table testytest ( mycol int(11) default 5 ); Query OK, 0 rows affected (0.00 sec) mysql insert into testytest (mycol) values (NULL); Query OK, 1 row affected (0.00 sec) mysql insert into testytest (mycol) values (DEFAULT); Query OK, 1 row affected (0.00 sec) mysql insert into testytest (mycol) values (12); Query OK, 1 row affected (0.00 sec) mysql select * from testytest; +---+ | mycol | +---+ | NULL | | 5 | |12 | +---+ 3 rows in set (0.00 sec) This is trivial seeming in the single insert case, but it seems like in the multiple insert case, sqlalchemy will require me to use NULLable columns and ignore defaults. Am I missing something? -- 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] default value in multiple insert
not sure what the question is - how to use server-side defaults ?Column
accepts a server_default keyword for this purpose.You leave the key out
of the columns dictionary for those columns where you want the server_default
to fire off.
if the question is, I want to multiple insert like [{'a':1, 'b':2, 'c':3},
{'a':2}, {'a':3, 'b':4}], etc. only some dictionaries are missing different
keys, that is not allowed. The structure of the SQL statement as parsed by
MySQL determines for which columns the server-side default is emitted and there
is only one version of that with an executemany. So every param dictionary
must have at least all the keys which the first one does.
SQLAlchemy also allows client side defaults, but the rules are the same for
executemany(), since SQLalchemy wants to keep the behaviors consistent and
doesn't want to spend time scanning through the parameter list if its not
needed.
On May 18, 2010, at 6:53 PM, Dan K wrote:
I found a similar thread from about a year ago (http://
groups.google.com/group/sqlalchemy/browse_thread/thread/
66ef04fd10fd2be/ec7784b70abedabe), but it never seemed to answer the
most burning question: is there a way in sqlalchemy to do a multiple
insert with default values for unspecified columns? One way this
might be possible in SQL is to use the DEFAULT keyword, but I haven't
found anything about it in sqlalchemy.
Below I have SQL that shows:
a) the current response of sqlalchemy to a multiple insert with a row
dict missing a value for a column.
b) the usage of the DEFAULT keyword I am referring to
mysql create table testytest ( mycol int(11) default 5 );
Query OK, 0 rows affected (0.00 sec)
mysql insert into testytest (mycol) values (NULL);
Query OK, 1 row affected (0.00 sec)
mysql insert into testytest (mycol) values (DEFAULT);
Query OK, 1 row affected (0.00 sec)
mysql insert into testytest (mycol) values (12);
Query OK, 1 row affected (0.00 sec)
mysql select * from testytest;
+---+
| mycol |
+---+
| NULL |
| 5 |
|12 |
+---+
3 rows in set (0.00 sec)
This is trivial seeming in the single insert case, but it seems like
in the multiple insert case, sqlalchemy will require me to use
NULLable columns and ignore defaults. Am I missing something?
--
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] default value in multiple insert
My apologies; I expressed my question rather incoherently.
if the question is, I want to multiple insert like [{'a':1, 'b':2, 'c':3},
{'a':2}, {'a':3, 'b':4}], etc. only some dictionaries are missing different
keys, that is not allowed. The structure of the SQL statement as parsed by
MySQL determines for which columns the server-side default is emitted and
there is only one version of that with an executemany. So every param
dictionary must have at least all the keys which the first one does.
This was the basic scenario of my question. However, unless I misunderstand,
it does seem to be allowed: the missing values are replaced with NULL in
the generated query. But what I actually meant to ask was this: sqlalchemy
handles missing dictionary entries as NULL for the purpose of inserts, is
there an easy way to have it generate a SQL statement with DEFAULT for
missing dictionary entries?
Eg. execute(testtable.insert(), [{'a':1, 'b':2, 'c':3}, {'a':2}, {'a':3,
'b':4}]) = INSERT INTO testtable (a,b,c) VALUES
(1,2,3),(2,DEFAULT,DEFAULT),(3,4,DEFAULT)
--
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.
