[sqlalchemy] SELECT executemany
Is there some other way I can execute a query using a list as a bound parameter?
res = db.db_con.text('SELECT module_extra_key_name FROM module_extra_keys
WHERE module_id IN :module_ids').execute({'module_ids': [1,2]})
The above is giving me an You cannot execute SELECT statements in
executemany() error.
This is while I'm running 0.3.8.
--~--~-~--~~~---~--~~
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] Failures with AssociationProxy (starting with r2598)
Starting with r2598, I'm seeing some failures with how I'm using the
association proxy. I've modified the
examples/association/proxied_association.py file which duplicates the
problem.
It is possible that I'm not suppose to remove associations like I am.
But it did work before (in 0.3.7 and 0.3.6).
It looks like if I read in part of the association into the current
session and then delete it, things don't go so well.
Index: examples/association/proxied_association.py
===
--- examples/association/proxied_association.py (revision 2723)
+++ examples/association/proxied_association.py (working copy)
@@ -106,8 +106,18 @@
+# new additions to proxied_association.py
+#engine.echo = True
+new_item = Item('new item', 100)
+session.clear()
+order = session.query(Order).get_by(customer_name='john smith')
+bogus = order.items[0].item_id # comment out and it works on 0.3.8
+order.itemassociations = None
+session.flush()
+order.items.append(new_item)
+session.flush()
It fails with:
sqlalchemy.exceptions.SQLError: (IntegrityError) orderitems.order_id
may not be NULL u'INSERT INTO orderitems (item_id, price) VALUES (?,
?)' [5, 100]
Thanks,
Paul
--~--~-~--~~~---~--~~
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: Failures with AssociationProxy (starting with r2598)
Would you write a failing test for this condition? I looked at the
unit tests but wasn't able to make a failing test. So that is why I
modified the example.
I also tried to formulate a work around. And failed at that as well.
On 6/12/07, Michael Bayer [EMAIL PROTECTED] wrote:
Ive added ticket #597 for this.
On Jun 12, 2:22 am, Paul Kippes [EMAIL PROTECTED]
wrote:
Starting with r2598, I'm seeing some failures with how I'm using the
association proxy. I've modified the
examples/association/proxied_association.py file which duplicates the
problem.
It is possible that I'm not suppose to remove associations like I am.
But it did work before (in 0.3.7 and 0.3.6).
It looks like if I read in part of the association into the current
session and then delete it, things don't go so well.
Index: examples/association/proxied_association.py
===
--- examples/association/proxied_association.py (revision 2723)
+++ examples/association/proxied_association.py (working copy)
@@ -106,8 +106,18 @@
+# new additions to proxied_association.py
+#engine.echo = True
+new_item = Item('new item', 100)
+session.clear()
+order = session.query(Order).get_by(customer_name='john smith')
+bogus = order.items[0].item_id # comment out and it works on 0.3.8
+order.itemassociations = None
+session.flush()
+order.items.append(new_item)
+session.flush()
It fails with:
sqlalchemy.exceptions.SQLError: (IntegrityError) orderitems.order_id
may not be NULL u'INSERT INTO orderitems (item_id, price) VALUES (?,
?)' [5, 100]
Thanks,
Paul
--~--~-~--~~~---~--~~
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: PROPOSAL: whack query.select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by(), get_by(), auto-join feature
These sound like a good progression of the library. Would there be a way to turn on warnings if one were to use a deprecated interface? Or if the programmer wants to be even more strict, raise an exception? This wouldn't be the default, but rather an available option. --~--~-~--~~~---~--~~ 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] Changing my SQLAlchemy include point
First, let me admit that I'm not an expert at Python. I'm trying to test the newer versions of SQLAlchemy before I install it on my server. According to http://docs.python.org/inst/search-path.html, the PYTHONPATH should add paths to the beginning of sys.path. The docs at http://docs.python.org/lib/module-site.html do not mention PYTHONPATH at all (which is irritating). Anyway, what I'm seeing is that easy-install.pth has ./setuptools-0.6c3-py2.5.egg and ./SQLAlchemy-0.3.1-py2.5.egg When I check the sys.path value, I'm getting blank, setuptools, SQLAlchemy, and THEN the contents of PYTHONPATH. Really from the Python documentation, PYTHONPATH sounds like it will go at the front. But this isn't what is going on. What is the best way to have a system-installed SQLAlchemy (for others) and then override it by the user (me) to test new versions? --~--~-~--~~~---~--~~ 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: new setuptools vs local SA copy
I found the thread about PYTHONPATH--interesting. It does seem that eggs are not only preventing the expected behavior, but they are also preventing the documented behavior. However, I don't think that using eggs is the best choice for a fast progressing library like SQLAlchemy--especially with this behavior. Plus, if the egg developer isn't participating in a discussion on this, why should that distribution method even be used? Paul --~--~-~--~~~---~--~~ 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] Error starting with revision 2620
I have a query that started to fail with r2620. Here is a portion on the code and the exception that was thrown. I've tested against the most current revision and that also fails. Is this something I'm doing wrong or a real bug? --Thanks, Paul in_sql = sql.select([db.module_inputs.c.signal_id], db.module_inputs.c.module_id == m.module_id) out_sql = sql.select([db.module_outputs.c.signal_id], db.module_outputs.c.module_id == m.module_id) select_sql = in_sql.union(out_sql) # Do name ordering using the above select_sql in a subselect signal_list = model.session.query(domain_model.Signal).select( db.signals.c.signal_id.in_(select_sql), order_by=[db.signals.c.signal_name]) Traceback (most recent call last): File tests/test_domain/test_utils.py, line 638, in test_module_signals_order_by_type domain_utils.TYPE_ORDER) File /home/user/appl/scripts/lib/appl/domain/utils.py, line 914, in module_signals db.signals.c.signal_name]) File /home/user/.python/sqlalchemy/orm/query.py, line 319, in select return self.select_whereclause(whereclause=arg, **kwargs) File /home/user/.python/sqlalchemy/orm/query.py, line 326, in select_whereclause return self._select_statement(statement, params=params) File /home/user/.python/sqlalchemy/orm/query.py, line 939, in _select_statement return self.execute(statement, params=params, **kwargs) File /home/user/.python/sqlalchemy/orm/query.py, line 843, in execute result = self.session.execute(self.mapper, clauseelement, params=params) File /home/user/.python/sqlalchemy/orm/session.py, line 183, in execute return self.connection(mapper, close_with_result=True).execute(clause, params, **kwargs) File /home/user/.python/sqlalchemy/engine/base.py, line 520, in execute return Connection.executors[c](self, object, *multiparams, **params) File /home/user/.python/sqlalchemy/engine/base.py, line 560, in execute_clauseelement return self.execute_compiled(elem.compile(dialect=self.dialect, parameters=param), *multiparams, **params) File /home/user/.python/sqlalchemy/engine/base.py, line 571, in execute_compiled self._execute_raw(context) File /home/user/.python/sqlalchemy/engine/base.py, line 584, in _execute_raw self._execute(context) File /home/user/.python/sqlalchemy/engine/base.py, line 602, in _execute raise exceptions.SQLError(context.statement, context.parameters, e) SQLError: (OperationalError) near SELECT: syntax error u'SELECT signals.signal_type_id AS signals_signal_type_id, signals.signal_id AS signals_signal_id, signals.signal_range AS signals_signal_range, signals.signal_description AS signals_signal_description, signals.signal_name AS signals_signal_name, signals.signal_units_id AS signals_signal_units_id \nFROM signals JOIN signal_types ON signal_types.signal_type_id = signals.signal_type_id \nWHERE signals.signal_id IN SELECT module_inputs.signal_id \nFROM module_inputs \nWHERE module_inputs.module_id = ? UNION SELECT module_outputs.signal_id \nFROM module_outputs \nWHERE module_outputs.module_id = ? ORDER BY signal_types.signal_type_size_order, signals.signal_name' [21, 21] --~--~-~--~~~---~--~~ 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: Is ILIKE supported?
Michael, I certainly understand why this hasn't been added. With some databases like sqlite, a case sensitive search isn't even possible. This is much more complicated that I would have imagined. --~--~-~--~~~---~--~~ 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] Adding support for ESCAPE
Currently sqlalchemy doesn't support a query like this: SELECT my_name FROM names WHERE my_name LIKE 'larry\_%' ESCAPE '\' Could such a feature be added to be used in a similar way to the LIMIT feature? It looks like a simple enough patch for me to do. But I don't have knowledge of the array of databases necessary. Thanks, Paul --~--~-~--~~~---~--~~ 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: Adding support for ESCAPE
It permits escaping of the wild LIKE characters _ and %. Say, for example, I have this column data: 1 larry_one 2 larry_two 3 larrysmall 4 larrybig 5 larry_small 6 larry_big SELECT my_name FROM names WHERE my_name LIKE 'larry_%' would return all the rows; but using SELECT my_name FROM names WHERE my_name LIKE 'larry\_%' ESCAPE '\' see: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r751.htm would return not return rows 3 and 4 On 4/4/07, Michael Bayer [EMAIL PROTECTED] wrote: what is ESCAPE used for exactly (i.e. whats it going to do to that \ ?) ? what DB is this ? can this same functionality be achieved via bind parameters ? On Apr 4, 2007, at 10:30 AM, Paul Kippes wrote: Currently sqlalchemy doesn't support a query like this: SELECT my_name FROM names WHERE my_name LIKE 'larry\_%' ESCAPE '\' Could such a feature be added to be used in a similar way to the LIMIT feature? It looks like a simple enough patch for me to do. But I don't have knowledge of the array of databases necessary. Thanks, Paul --~--~-~--~~~---~--~~ 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: Adding support for ESCAPE
I've been using sqlite and as far as I know, it requires the ESCAPE clause. I'll take a stab at creating a patch this evening. On 4/4/07, Michael Bayer [EMAIL PROTECTED] wrote: in postgres for example, \ is already the escape character, so you wouldnt need to say ESCAPE '\'. is this not the case in DB2 (we dont support DB2 anyway yet ?) ? if you want to provide a patch, this would be a keyword argument to the like() function, and would probably involve replacing the BooleanExpression that uses like as a regular operator with its own construct, i.e. sqlalchemy.sql._LikeClause. then an explicit visit_like() would be added to ansisql.py to process the construct as well as the optional escapes keyword argument. otherwise, please add a new enhancement ticket to trac (via the bugs link on the site) and we'll get it in the queue. On Apr 4, 2007, at 12:34 PM, Paul Kippes wrote: It permits escaping of the wild LIKE characters _ and %. Say, for example, I have this column data: 1 larry_one 2 larry_two 3 larrysmall 4 larrybig 5 larry_small 6 larry_big SELECT my_name FROM names WHERE my_name LIKE 'larry_%' would return all the rows; but using SELECT my_name FROM names WHERE my_name LIKE 'larry\_%' ESCAPE '\' see: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp? topic=/com.ibm.db2.udb.admin.doc/doc/r751.htm would return not return rows 3 and 4 On 4/4/07, Michael Bayer [EMAIL PROTECTED] wrote: what is ESCAPE used for exactly (i.e. whats it going to do to that \ ?) ? what DB is this ? can this same functionality be achieved via bind parameters ? On Apr 4, 2007, at 10:30 AM, Paul Kippes wrote: Currently sqlalchemy doesn't support a query like this: SELECT my_name FROM names WHERE my_name LIKE 'larry\_%' ESCAPE '\' Could such a feature be added to be used in a similar way to the LIMIT feature? It looks like a simple enough patch for me to do. But I don't have knowledge of the array of databases necessary. Thanks, Paul --~--~-~--~~~---~--~~ 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] Is ILIKE supported?
In my version of sqlalchemy I didn't find any ability for ILIKE. Is this supported? If a patch is needed, would a new operator ilike be okay? --~--~-~--~~~---~--~~ 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] Changing the order_by with Result-Set mapping
I'm finally getting a bit more at ease with the more complex aspects of SQLAlchemy. But yet again, I still need some help. Now I'm trying to change the ordering from my mapper's default. My Signal mapper was defined to order by the signal_name (using an AssociationProxy). That has been working. With the below new query, I'd like to order using an additional table's column: signal_type_size_order. u1_join = sql.join(db.signals, db.module_inputs, sql.and_(db.module_inputs.c.module_id == m.module_id, db.module_inputs.c.signal_id == db.signals.c.signal_id)) u2_join = sql.join(db.signals, db.module_outputs, sql.and_(db.module_outputs.c.module_id == m.module_id, db.module_outputs.c.signal_id == db.signals.c.signal_id)) sig_id_sel = sql.select([db.signals.c.signal_id], from_obj=[u1_join]). \ union(sql.select([db.signals.c.signal_id], from_obj=[u2_join])) # Construct a query to gather the signals from the above UNION sel = sql.select([db.signals, db.signal_types], db.signals.c.signal_id.in_(sig_id_sel), from_obj=[sql.join(db.signals, db.signal_types)], use_labels=True, order_by=[db.signal_types.c.signal_type_size_order, db.signals.c.signal_name]) res = model.session.query(domain_model.Signal).instances(sel.execute(), sql.class_mapper(domain_model.SignalType)) Everything is perfect up through the sel assignment. sel evaluates to the below query which returns a correct order from the sqlite3 CLI. Something is wrong with my res assignment line. (I am using 0.3.5 at least until my unit tests pass again.) SELECT signals.signal_id AS signals_signal_id, signals.signal_name AS signals_signal_name, signals.signal_description AS signals_signal_description, signals.signal_units_id AS signals_signal_units_id, signals.signal_type_id AS signals_signal_type_id, signals.signal_range AS signals_signal_range, signal_types.signal_type_id AS signal_types_signal_type_id, signal_types.signal_type_name AS signal_types_signal_type_name, signal_types.signal_type_ctype AS signal_types_signal_type_44f9, signal_types.signal_type_size_order AS signal_types_signal_type_e087, signal_types.signal_type_description AS signal_types_signal_type_f7d7 FROM signals JOIN signal_types ON signal_types.signal_type_id = signals.signal_type_id WHERE signals.signal_id IN (SELECT signals.signal_id AS signal_id FROM signals JOIN module_inputs ON module_inputs.module_id = ? AND module_inputs.signal_id = signals.signal_id UNION SELECT signals.signal_id AS signal_id FROM signals JOIN module_outputs ON module_outputs.module_id = ? AND module_outputs.signal_id = signals.signal_id) ORDER BY signal_types.signal_type_size_order, signals.signal_name --~--~-~--~~~---~--~~ 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] Subselect is preventing additional where conditions--help
I'm trying to create a subselect that has a where condition using an
identically named column as the outer select. I'm not able to figure
out how to tell SQLAlchemy that I need two parameters--one for each
query. Here is what my SQL would look like if I wrote it by hand:
SELECT signals.*
FROM module_outputs
WHERE module_id = :module_id
AND signal_id NOT IN (
SELECT signal_id
FROM module_inputs
WHERE module_id = :module_id)
This is how the joining tables are defined:
self.module_inputs = sqla.Table('module_inputs', metadata,
sqla.Column('module_id',
sqla.Integer,
sqla.ForeignKey('modules.module_id'),
nullable = False),
sqla.Column('signal_id',
sqla.Integer,
sqla.ForeignKey('signals.signal_id'),
nullable = False),
sqla.PrimaryKeyConstraint('module_id', 'signal_id')
)
self.module_outputs = sqla.Table('module_outputs', metadata,
sqla.Column('module_id',
sqla.Integer,
sqla.ForeignKey('modules.module_id'),
nullable = False),
sqla.Column('signal_id',
sqla.Integer,
sqla.ForeignKey('signals.signal_id'),
nullable = False),
sqla.PrimaryKeyConstraint('module_id', 'signal_id')
)
Below is some code that attempts to pass the necessary parameters.
I'd like to place both parameters in the execute() line, but it seems
I may need alias one or both parameters. So far, I'm stumped on what
to and what I'm doing wrong.
db = model.db.tables
# Construct the subselect
not_in_join = sql.join(db.signals, db.module_outputs)
not_in_sel = sql.select([db.signals.c.signal_id],
db.module_outputs.c.module_id == m.module_id,
from_obj=[not_in_join])
# Construct the primary query to return signal_ids
sel = sql.select([db.signals.c.signal_id],
sql.not_(db.signals.c.signal_id.in_(not_in_sel)),
from_obj=[sql.join(db.signals, db.module_inputs)])
# print sel
# SELECT signals.signal_id
# FROM signals
# JOIN module_inputs ON signals.signal_id =
# module_inputs.signal_id
# WHERE signals.signal_id NOT IN (
# SELECT signals.signal_id AS signal_id
# FROM signals
# JOIN module_outputs ON signals.signal_id =
#module_outputs.signal_id
# WHERE module_outputs.module_id = ?)
# The above generated query would be fine if execute() would work.
id_res = sel.execute(module_id = m.module_id)
# This does not add an extra
# AND module_inputs.module_id = ?
sel = sql.select([db.signals.c.signal_id],
sql.and_(sql.not_(db.signals.c.signal_id.in_(not_in_sel)),
db.module_inputs.c.module_id == m.module_id),
from_obj=[sql.join(db.signals, db.module_inputs)])
# The above doesn't work either
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
