Re: [sqlalchemy] Getting a triple of related id fields
On Tue, Jun 20, 2023 at 3:47 PM Mike Bayer wrote: > > > step 1 is stop using that silly Flask extension that gives you > "Pipeline.query", I can't tell what it is you want to SELECT from either by > reading this query. Wow, that made things a lot easier. Is there any reason to avoid mixing Model.query with direct SQLAlchemy calls? We have a bunch of Model.query right now. I'm wondering if we'd need to rewrite all those upfront, or if we could redo them as time permits. Thanks! -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAOvKW55pTerGhFw6F7%3D%3DCgbz0fu2N71uUOgk73A91jP3M10AEQ%40mail.gmail.com.
[sqlalchemy] Getting a triple of related id fields
I've been banging on this for hours, but I seem to be getting nowhere. I've tried more things that I can count, but here are two of my attempts: # result = ( #Pipeline.query # .select_from(Storage, NewProduct) # .join(Storage, pipeline_alias1.storage_id == Storage.id) # .join(NewProduct, Storage.product_id == NewProduct.id) # .filter(pipeline_alias2.storage_id == storage_alias1.id) # .filter(storage_alias2.product_id == product_alias1.id) # ) result = ( Pipeline.query .select_from(Pipeline, Storage, NewProduct) .join(Storage, pipeline_alias1.storage_id == storage_alias1.id) .join(NewProduct, storage_alias2.product_id == product_alias1.id) ) I keep getting: sqlalchemy.exc.InvalidRequestError: Can't determine which FROM clause to join from, there are multiple FROMS which can join to this entity. Please use the .select_from() method to establish an explicit left side, as well as providing an explicit ON clause if not present already to help resolve the ambiguity. How can I tell SQLAlchemy which FROM to use? Thanks! -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAOvKW57frKP1EACaQ4zoRwu7kDAUULAbXx1xFi8eVdw69tZCZg%40mail.gmail.com.
[sqlalchemy] Turning a complex query into a view for SQLAlchemy?
Hi. In https://pajhome.org.uk/blog/10_reasons_to_love_sqlalchemy.html it says: When performing highly complex queries, it is possible to define these with SQLAlchemy syntax. However, I find there's a certain level of complexity where it becomes easier to write SQL directly. In that case, you can define a database view that encompasses the complex query, and SQLAlchemy can map the view to Python objects. I spoke with my team lead about this practice, and he was open to trying it but said he had heard that SQLAlchemy may have problems using views. Does anyone on the list have anything to add here? Thanks! -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAOvKW54y462yR4m5pOV5ukACA7v0CgCDUcrDztRBogtuE9t_GA%40mail.gmail.com.
[sqlalchemy] subquery relationships?
Hi folks. I have a subquery that is selected from a table with 5 foreign keys, and joined with another table with 3 foreign keys. And then that subquery is used in a join with the table having the 3 foreign keys again. I don't know how to tell what column(s) that join is happening on. What goes into SQLAlchemy's "thought process" when it joins a table with a subquery? Thanks! -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAOvKW56oZPu4NByXY1qZO99T8BQ_L54w42y6cex9zJyEoPmejw%40mail.gmail.com.
[sqlalchemy] Interpreting a SA traceback
I know, python2 is long dead. We’re almost ready for Python3, but not quite. Anyway, here’s a traceback that I’m not sure what to make of. Is it saying that a transaction got so big that it couldn’t be fully flushed within the timeout window? I’ve elided a sensitive part from the very end of the traceback. I realize this error is coming from mysql, not SA. Traceback (most recent call last): File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1475, in full_dispatch_request rv = self.dispatch_request() File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1461, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "/app/app/common/routing.py", line 117, in __call__ return Wrapper(*args, **kwargs) File "/app/app/common/auth/manager.py", line 340, in AuthDecorator return callback(self, *args, **kwargs) File "/app/app/common/routing.py", line 101, in Wrapper return self.callback(context, *a, **k) File "/app/app/api/service/v3/publish/controllers.py", line 99, in publish_new_build return publish_helper(context, manifestData, pipeline, keyring, token=token) File "/app/app/common/publishing/helpers.py", line 181, in publish_helper build.save(commit=False) File "/app/app/db/mixins.py", line 16, in save _session.flush() File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/scoping.py", line 153, in do return getattr(self.registry(), name)(*args, **kwargs) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2254, in flush self._flush(objects) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2380, in _flush transaction.rollback(_capture_exception=True) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2344, in _flush flush_context.execute() File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute rec.execute(self) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute uow File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj mapper, table, insert) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 866, in _emit_insert_statements execute(statement, params) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 948, in execute return meth(self, multiparams, params)ddd File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement compiled_sql, distilled_params File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context context) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception exc_info File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context context) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 507, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 165, in execute result = self._query(query) File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 321, in _query conn.query(q) File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 860, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1061, in _read_query_result result.read() File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1349, in read first_packet = self.connection._read_packet() File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1018, in _read_packet packet.check_error() File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 384, in check_error err.raise_mysql_exception(self._data) File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) InternalError: (pymysql.err.InternalError) (1205, u'Lock wait timeout exceeded; try restarting transaction') [SQL: u'INSERT INTO … (Background on this error at: http://sqlalche.me/e/2j85) We’re running: tact@tact_pub_api:/app$ python2 -m pip list -v | grep -i sqlalchemy Flask-SQLAlchemy 2.5.1 /usr/local/lib/python2.7/dist-packages pip SQLAlchemy1.2.5
Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
Granted, it’s difficult to read my (admittedly rather blank) mind. Maybe I should restate the question: What are my options? I just want to see the rows from the query below. Why is it telling me I need to aggregate, and if I do truly need to, what might be an aggregate function that won’t eliminate much of what the query is producing? From: 'Dan Stromberg [External]' via sqlalchemy Date: Tuesday, March 21, 2023 at 9:05 AM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") Alright, using join_from may have led to clearing a hurdle. I’m now using: query = ( select(NV. id, func. min(bs_3. build_id)) .select_from(bs) .join(v_2, onclause=(bs. version_id == v_2. id)) .join_from(bs_2, Br, onclause=(Br. id == bs_2. branch_id)) Alright, using join_from may have led to clearing a hurdle. I’m now using: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join_from(bs_2, Br, onclause=(Br.id == bs_2.branch_id)) ) ..and am getting a new error: sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1140, "In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'tact_dev.tb_nv.id'; this is incompatible with sql_mode=only_full_group_by") Do I need to aggregate? Or perhaps change sql_mode? Thanks! From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 5:33 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") OK, not really, you want tables in the FROM clause. use either the select_from() or join_from() method to do that: https: //docs. sqlalchemy. org/en/20/tutorial/data_select. html#explicit-from-clauses-and-joins On Mon, Mar 20, 2023, at 5: 16 PM, OK, not really, you want tables in the FROM clause. use either the select_from() or join_from() method to do that: https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#explicit-from-clauses-and-joins<https://urldefense.com/v3/__https:/docs.sqlalchemy.org/en/20/tutorial/data_select.html*explicit-from-clauses-and-joins__;Iw!!Ci6f514n9QsL8ck!gsSZYRslnIShc80D5SJP9hQv7FJkNL5Bzfvc8dkqobmEg8-ctkAcRyR1sZuv3pRL4eCzLvlJC-VDSf5sXXQNtX0d4POMpzTQh3-QUw$> On Mon, Mar 20, 2023, at 5:16 PM, 'Dan Stromberg [External]' via sqlalchemy wrote: I’m getting some pushback internally, from my team lead – he and I both think it’s probably too much detail to share. It’s 43 lines of SQL with multiple subqueries. Would just the simplest parts of the from clause work? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 1:11 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10: 33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV. id, what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10:33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs, Br) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which gave: 1054, "Unknown column 'tb_br.id' in 'on clause'" …and I tried: query = ( select(NV.id, func.min(bs_3.build_id), Br) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which also gave: (1054, "Unknown column 'tb_br.id' in 'on clause'") I’m guessing I’m missing something simple, but I have no idea what. Any (further) suggestions? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Saturday, March 18, 2023 at 8:01 AM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") the query emitted is: SELECT tb_nv. id, min(bs_3. build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs. version_id = v_2. id INNER JOIN tb_brst AS bs_2 ON tb_br. id = bs_2. branch_id the error means that your
Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
Hoping to save an iteration: the SQL currently looks like: [SQL: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id, tb_brst AS bs_2 INNER JOIN tb_br ON tb_br.id = bs_2.branch_id] From: 'Dan Stromberg [External]' via sqlalchemy Date: Tuesday, March 21, 2023 at 9:05 AM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") Alright, using join_from may have led to clearing a hurdle. I’m now using: query = ( select(NV. id, func. min(bs_3. build_id)) .select_from(bs) .join(v_2, onclause=(bs. version_id == v_2. id)) .join_from(bs_2, Br, onclause=(Br. id == bs_2. branch_id)) Alright, using join_from may have led to clearing a hurdle. I’m now using: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join_from(bs_2, Br, onclause=(Br.id == bs_2.branch_id)) ) ..and am getting a new error: sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1140, "In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'tact_dev.tb_nv.id'; this is incompatible with sql_mode=only_full_group_by") Do I need to aggregate? Or perhaps change sql_mode? Thanks! From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 5:33 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") OK, not really, you want tables in the FROM clause. use either the select_from() or join_from() method to do that: https: //docs. sqlalchemy. org/en/20/tutorial/data_select. html#explicit-from-clauses-and-joins On Mon, Mar 20, 2023, at 5: 16 PM, OK, not really, you want tables in the FROM clause. use either the select_from() or join_from() method to do that: https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#explicit-from-clauses-and-joins<https://urldefense.com/v3/__https:/docs.sqlalchemy.org/en/20/tutorial/data_select.html*explicit-from-clauses-and-joins__;Iw!!Ci6f514n9QsL8ck!gsSZYRslnIShc80D5SJP9hQv7FJkNL5Bzfvc8dkqobmEg8-ctkAcRyR1sZuv3pRL4eCzLvlJC-VDSf5sXXQNtX0d4POMpzTQh3-QUw$> On Mon, Mar 20, 2023, at 5:16 PM, 'Dan Stromberg [External]' via sqlalchemy wrote: I’m getting some pushback internally, from my team lead – he and I both think it’s probably too much detail to share. It’s 43 lines of SQL with multiple subqueries. Would just the simplest parts of the from clause work? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 1:11 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10: 33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV. id, what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10:33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs, Br) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which gave: 1054, "Unknown column 'tb_br.id' in 'on clause'" …and I tried: query = ( select(NV.id, func.min(bs_3.build_id), Br) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which also gave: (1054, "Unknown column 'tb_br.id' in 'on clause'") I’m guessing I’m missing something simple, but I have no idea what. Any (further) suggestions? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Saturday, March 18, 2023 at 8:01 AM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") the query emitted is: SELECT tb_nv. id, min(bs_3. build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs. version_id = v_2. id INNER JOIN tb_brst AS bs_2 ON tb_br. id = bs_2. branch_id the error means that your the query emitted is: SELECT tb_nv.id, min(bs_3.build_id) AS
Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
Alright, using join_from may have led to clearing a hurdle. I’m now using: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join_from(bs_2, Br, onclause=(Br.id == bs_2.branch_id)) ) ..and am getting a new error: sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1140, "In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'tact_dev.tb_nv.id'; this is incompatible with sql_mode=only_full_group_by") Do I need to aggregate? Or perhaps change sql_mode? Thanks! From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 5:33 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") OK, not really, you want tables in the FROM clause. use either the select_from() or join_from() method to do that: https: //docs. sqlalchemy. org/en/20/tutorial/data_select. html#explicit-from-clauses-and-joins On Mon, Mar 20, 2023, at 5: 16 PM, OK, not really, you want tables in the FROM clause. use either the select_from() or join_from() method to do that: https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#explicit-from-clauses-and-joins<https://urldefense.com/v3/__https:/docs.sqlalchemy.org/en/20/tutorial/data_select.html*explicit-from-clauses-and-joins__;Iw!!Ci6f514n9QsL8ck!gsSZYRslnIShc80D5SJP9hQv7FJkNL5Bzfvc8dkqobmEg8-ctkAcRyR1sZuv3pRL4eCzLvlJC-VDSf5sXXQNtX0d4POMpzTQh3-QUw$> On Mon, Mar 20, 2023, at 5:16 PM, 'Dan Stromberg [External]' via sqlalchemy wrote: I’m getting some pushback internally, from my team lead – he and I both think it’s probably too much detail to share. It’s 43 lines of SQL with multiple subqueries. Would just the simplest parts of the from clause work? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 1:11 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10: 33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV. id, what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10:33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs, Br) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which gave: 1054, "Unknown column 'tb_br.id' in 'on clause'" …and I tried: query = ( select(NV.id, func.min(bs_3.build_id), Br) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which also gave: (1054, "Unknown column 'tb_br.id' in 'on clause'") I’m guessing I’m missing something simple, but I have no idea what. Any (further) suggestions? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Saturday, March 18, 2023 at 8:01 AM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") the query emitted is: SELECT tb_nv. id, min(bs_3. build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs. version_id = v_2. id INNER JOIN tb_brst AS bs_2 ON tb_br. id = bs_2. branch_id the error means that your the query emitted is: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id the error means that your ON clause refers to a table "tb_br" which is not otherwise in the FROM clause: "ON tb_br.id = bs_2.branch_id" the ON clause can only refer to columns from tables that are being SELECTed from, such as: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id INNER JOIN tb_br ON tb_br.id = bs_2.branch_id INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id so you'd need to alter your query to include some indication how tb_br is part of what's being joined. On Fri, Mar 17, 2023, at 7:52 PM, 'Dan Stromberg' via sqlalchemy wro
Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
Here’s the select, and most of the from clause: select nv.id, min(bs.build_id) as min_build_id from tb_v as v, tb_nv as nv, tb_bs as bs, tb_br as br, From: 'Dan Stromberg [External]' via sqlalchemy Date: Monday, March 20, 2023 at 2:16 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") I’m getting some pushback internally, from my team lead – he and I both think it’s probably too much detail to share. It’s 43 lines of SQL with multiple subqueries. Would just the simplest parts of the from clause work? From: sqlalchemy@ googlegroups. com I’m getting some pushback internally, from my team lead – he and I both think it’s probably too much detail to share. It’s 43 lines of SQL with multiple subqueries. Would just the simplest parts of the from clause work? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 1:11 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10: 33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV. id, what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10:33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs, Br) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which gave: 1054, "Unknown column 'tb_br.id' in 'on clause'" …and I tried: query = ( select(NV.id, func.min(bs_3.build_id), Br) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which also gave: (1054, "Unknown column 'tb_br.id' in 'on clause'") I’m guessing I’m missing something simple, but I have no idea what. Any (further) suggestions? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Saturday, March 18, 2023 at 8:01 AM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") the query emitted is: SELECT tb_nv. id, min(bs_3. build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs. version_id = v_2. id INNER JOIN tb_brst AS bs_2 ON tb_br. id = bs_2. branch_id the error means that your the query emitted is: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id the error means that your ON clause refers to a table "tb_br" which is not otherwise in the FROM clause: "ON tb_br.id = bs_2.branch_id" the ON clause can only refer to columns from tables that are being SELECTed from, such as: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id INNER JOIN tb_br ON tb_br.id = bs_2.branch_id INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id so you'd need to alter your query to include some indication how tb_br is part of what's being joined. On Fri, Mar 17, 2023, at 7:52 PM, 'Dan Stromberg' via sqlalchemy wrote: Hi people. I'm having trouble with a test query. As the subject line says, I'm getting: sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") But it seems like tb_br exists, and has an id column - tb_br being an empty table, but still, existent: mysql> show create table tb_br; +---+-+ | Table | Create Table | +---+-+ | tb_br | CREATE TABLE `tb_br` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name
Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
I’m getting some pushback internally, from my team lead – he and I both think it’s probably too much detail to share. It’s 43 lines of SQL with multiple subqueries. Would just the simplest parts of the from clause work? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 1:11 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10: 33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV. id, what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10:33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs, Br) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which gave: 1054, "Unknown column 'tb_br.id' in 'on clause'" …and I tried: query = ( select(NV.id, func.min(bs_3.build_id), Br) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which also gave: (1054, "Unknown column 'tb_br.id' in 'on clause'") I’m guessing I’m missing something simple, but I have no idea what. Any (further) suggestions? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Saturday, March 18, 2023 at 8:01 AM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") the query emitted is: SELECT tb_nv. id, min(bs_3. build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs. version_id = v_2. id INNER JOIN tb_brst AS bs_2 ON tb_br. id = bs_2. branch_id the error means that your the query emitted is: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id the error means that your ON clause refers to a table "tb_br" which is not otherwise in the FROM clause: "ON tb_br.id = bs_2.branch_id" the ON clause can only refer to columns from tables that are being SELECTed from, such as: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id INNER JOIN tb_br ON tb_br.id = bs_2.branch_id INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id so you'd need to alter your query to include some indication how tb_br is part of what's being joined. On Fri, Mar 17, 2023, at 7:52 PM, 'Dan Stromberg' via sqlalchemy wrote: Hi people. I'm having trouble with a test query. As the subject line says, I'm getting: sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") But it seems like tb_br exists, and has an id column - tb_br being an empty table, but still, existent: mysql> show create table tb_br; +---+-+ | Table | Create Table | +---+-+ | tb_br | CREATE TABLE `tb_br` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---+-+ 1 row in set (0.04 sec) mysql> select * from tb_br; Empty set (0.03 sec) The query, along with sample models, looks like: #!/usr/bin/env python3 """ A little test program. Environment variables: DBU Your database user DBP Your database password DBH Your database host IDB Your initial database """ import os import pprint from sqlalchemy import create_engine, select from sqlalchemy.orm import aliased, sessionmaker, declarative_base from sqlalchemy.sql.expression import func
Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs, Br) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which gave: 1054, "Unknown column 'tb_br.id' in 'on clause'" …and I tried: query = ( select(NV.id, func.min(bs_3.build_id), Br) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which also gave: (1054, "Unknown column 'tb_br.id' in 'on clause'") I’m guessing I’m missing something simple, but I have no idea what. Any (further) suggestions? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Saturday, March 18, 2023 at 8:01 AM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") the query emitted is: SELECT tb_nv. id, min(bs_3. build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs. version_id = v_2. id INNER JOIN tb_brst AS bs_2 ON tb_br. id = bs_2. branch_id the error means that your the query emitted is: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id the error means that your ON clause refers to a table "tb_br" which is not otherwise in the FROM clause: "ON tb_br.id = bs_2.branch_id" the ON clause can only refer to columns from tables that are being SELECTed from, such as: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id INNER JOIN tb_br ON tb_br.id = bs_2.branch_id INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id so you'd need to alter your query to include some indication how tb_br is part of what's being joined. On Fri, Mar 17, 2023, at 7:52 PM, 'Dan Stromberg' via sqlalchemy wrote: Hi people. I'm having trouble with a test query. As the subject line says, I'm getting: sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") But it seems like tb_br exists, and has an id column - tb_br being an empty table, but still, existent: mysql> show create table tb_br; +---+-+ | Table | Create Table | +---+-+ | tb_br | CREATE TABLE `tb_br` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---+-+ 1 row in set (0.04 sec) mysql> select * from tb_br; Empty set (0.03 sec) The query, along with sample models, looks like: #!/usr/bin/env python3 """ A little test program. Environment variables: DBU Your database user DBP Your database password DBH Your database host IDB Your initial database """ import os import pprint from sqlalchemy import create_engine, select from sqlalchemy.orm import aliased, sessionmaker, declarative_base from sqlalchemy.sql.expression import func from flask_sqlalchemy import SQLAlchemy db = SQLAlchemy() Base = declarative_base() class NV(Base): __tablename__ = "tb_nv" __bind_key__ = "testdb" __table_args__ = ( { "mysql_engine": "InnoDB", "mysql_charset": "utf8", "mysql_collate": "utf8_general_ci", }, ) id = db.Column("id", db.Integer, primary_key=True, autoincrement=True) builds = db.relationship("Bld", primaryjoin="(NV.id == Bld.variant_id)") class Vers(Base): __tablename__ = "tb_vers" __bind_key__ = "testdb" __table_args__ = ( { "mysql_engine": "InnoDB", "mysql_charset": "utf8", "mysql_collate": "utf8_general_ci", }, ) id = db.Column("id", db.Integer, primary_key=True, autoincrement=True) class St(Base): _
Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
Sorry, I don’t know why Google Groups decided to aggregate a few lines into 2 large lines. Here’s that list of versions again. Hopefully GG will be appeased this time. I'm using: $ python3 -m pip list -v | grep -i sqlalchemy Flask-SQLAlchemy 2.5.1 /data/home/dstromberg/.local/lib/python3.10/site-packages pip SQLAlchemy 1.4.36 /data/home/dstromberg/.local/lib/python3.10/site-packages pip $ python3 -m pip list -v | grep -i mysql mysqlclient2.1.1 /data/home/dstromberg/.local/lib/python3.10/site-packages pip PyMySQL0.8.0 /data/home/dstromberg/.local/lib/python3.10/site-packages pip bash-4.2# mysql --version mysql Ver 14.14 Distrib 5.7.41, for Linux (x86_64) using EditLine wrapper -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/DM5PR12MB2503CB97085F7BF2AE76D952C5829%40DM5PR12MB2503.namprd12.prod.outlook.com.
[sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
Hi people. I'm having trouble with a test query. As the subject line says, I'm getting: sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") But it seems like tb_br exists, and has an id column - tb_br being an empty table, but still, existent: mysql> show create table tb_br; +---+-+ | Table | Create Table | +---+-+ | tb_br | CREATE TABLE `tb_br` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---+-+ 1 row in set (0.04 sec) mysql> select * from tb_br; Empty set (0.03 sec) The query, along with sample models, looks like: #!/usr/bin/env python3 """ A little test program. Environment variables: DBU Your database user DBP Your database password DBH Your database host IDB Your initial database """ import os import pprint from sqlalchemy import create_engine, select from sqlalchemy.orm import aliased, sessionmaker, declarative_base from sqlalchemy.sql.expression import func from flask_sqlalchemy import SQLAlchemy db = SQLAlchemy() Base = declarative_base() class NV(Base): __tablename__ = "tb_nv" __bind_key__ = "testdb" __table_args__ = ( { "mysql_engine": "InnoDB", "mysql_charset": "utf8", "mysql_collate": "utf8_general_ci", }, ) id = db.Column("id", db.Integer, primary_key=True, autoincrement=True) builds = db.relationship("Bld", primaryjoin="(NV.id == Bld.variant_id)") class Vers(Base): __tablename__ = "tb_vers" __bind_key__ = "testdb" __table_args__ = ( { "mysql_engine": "InnoDB", "mysql_charset": "utf8", "mysql_collate": "utf8_general_ci", }, ) id = db.Column("id", db.Integer, primary_key=True, autoincrement=True) class St(Base): __tablename__ = "tb_brst" __bind_key__ = "testdb" __table_args__ = ({"mysql_engine": "InnoDB", "mysql_charset": "utf8"},) id = db.Column("id", db.Integer, primary_key=True, autoincrement=True) version_id = db.Column( "version_id", db.Integer, db.ForeignKey( "tb_vers.id", name="fk_tb_brst_version_id", onupdate="CASCADE", ondelete="RESTRICT", ), nullable=False, ) branch_id = db.Column( "branch_id", db.Integer, db.ForeignKey( "tb_br.id", name="fk_tb_brst_branch_id", onupdate="CASCADE", ondelete="RESTRICT", ), nullable=False, ) build_id = db.Column( "build_id", db.Integer, db.ForeignKey( "tb_bld.id", name="fk_tb_brst_build_id", onupdate="CASCADE", ondelete="RESTRICT", ), nullable=False, ) version = db.relationship( "Vers", innerjoin=True, primaryjoin="(St.version_id == Vers.id)" ) branch = db.relationship( "Br", innerjoin=True, primaryjoin="(St.branch_id == Br.id)" ) build = db.relationship( "Bld", innerjoin=True, primaryjoin="(St.build_id == Bld.id)" ) class Br(Base): __tablename__ = "tb_br" __bind_key__ = "testdb" __table_args__ = ( { "mysql_engine": "InnoDB", "mysql_charset": "utf8", "mysql_collate": "utf8_general_ci", }, ) id = db.Column("id", db.Integer, primary_key=True, autoincrement=True) name = db.Column("name", db.String(45), nullable=False) class Bld(Base): __tablename__ = "tb_bld" __bind_key__ = "testdb" __table_args__ = ( { "mysql_engine": "InnoDB", "mysql_charset": "utf8", "mysql_collate": "utf8_general_ci", }, ) id = db.Column("id", db.Integer, primary_key=True, autoincrement=True) name = db.Column("name", db.String(100), nullable=False) variant_id = db.Column( "variant_id", db.Integer, db.ForeignKey( "tb_nv.id", name="fk_tb_bld_variant_id", onupdate="CASCADE", ondelete="RESTRICT", ), nullable=False, ) variant = db.relationship("NV") def display(values): """Display values in a decent way.""" pprint.pprint(values) def connect():
[sqlalchemy] 'unique' exception not translated by sqlalchemy
I'm trying to use UNIQUE on a column in a table, catching exception in order to determine conflict. Problem is that the exception I'm getting is sqlite3.IntegrityError. This is not a SqlAlchemy exception, and its a problem since sqlite is temporary. I expected to get a ORM/SA exception, that will be the same with different engines. (also sqlite goes bonkers after it happens, requiring transaction rollback) I know I can query first, but that makes using the UNIQUE designation kind of moot. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: How can I get the field names from an object?
This is almost but not quite what I want. In your example, if the dictionary includes keys that do not map to object fields, it will throw. I want to try and 'cherry-pick' fields of the object from the dictionary. With the introspect you gave me I can do that. Thanks, Dan On Monday, January 28, 2019 at 1:57:44 PM UTC+2, dan.b...@huawei.com wrote: > > > Lets say I have a class > > class Dog(AlchemyBase): > __tablename__ = 'dogs' > name = Column(String, primary_key=True) > color = Column(String) > flees = relationship("Flee", backref="dogs") > > > > How can I get the list of fields ['name', 'color', 'flees'] from the class? > > I'd like to write a generic load(Dog, dict) method, that will create a > Dog() with the field values that exist in dict. > > e.g. > d = {'name: 'snoopy', 'junk': 1} > dog = load(Dog, d) > is equivalent to dog = Dog(name = 'snoopy') > > d1 = {'alias' : 'kitti'} > but load(Cat, d1) > is equivalent to cat = Cat(alias = 'kitti) > > Load will pick field names that exist in the passed Object, and have a > value in dict > > Thanks, > Dan > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: How can I get the field names from an object?
This is doing almost, but not quite what I want. If the dictionary includes entries which are NOT fields in the object, On Monday, January 28, 2019 at 1:57:44 PM UTC+2, dan.b...@huawei.com wrote: > > > Lets say I have a class > > class Dog(AlchemyBase): > __tablename__ = 'dogs' > name = Column(String, primary_key=True) > color = Column(String) > flees = relationship("Flee", backref="dogs") > > > > How can I get the list of fields ['name', 'color', 'flees'] from the class? > > I'd like to write a generic load(Dog, dict) method, that will create a > Dog() with the field values that exist in dict. > > e.g. > d = {'name: 'snoopy', 'junk': 1} > dog = load(Dog, d) > is equivalent to dog = Dog(name = 'snoopy') > > d1 = {'alias' : 'kitti'} > but load(Cat, d1) > is equivalent to cat = Cat(alias = 'kitti) > > Load will pick field names that exist in the passed Object, and have a > value in dict > > Thanks, > Dan > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] How can I get the field names from an object?
Lets say I have a class class Dog(AlchemyBase): __tablename__ = 'dogs' name = Column(String, primary_key=True) color = Column(String) flees = relationship("Flee", backref="dogs") How can I get the list of fields ['name', 'color', 'flees'] from the class? I'd like to write a generic load(Dog, dict) method, that will create a Dog() with the field values that exist in dict. e.g. d = {'name: 'snoopy', 'junk': 1} dog = load(Dog, d) is equivalent to dog = Dog(name = 'snoopy') d1 = {'alias' : 'kitti'} but load(Cat, d1) is equivalent to cat = Cat(alias = 'kitti) Load will pick field names that exist in the passed Object, and have a value in dict Thanks, Dan -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Error streaming results of query with 0 rowcount from Redshift
It happens implicitly when iterating over the cursor to get the results. Here's an example using fetchmany: import psycopg2 from datetime import datetime conn_str = 'host=redshift_host port=redshift_port username=username password=password' query = 'SELECT * FROM test WHERE timestamp > %s' params = (datetime(2020, 12, 31, 0, 0),) with psycopg2.connect(conn_str) as conn: with conn.cursor(name='test_cursor') as cur: cur.execute(query, params) results = cur.fetchmany(1000) This doesn't produce any errors as well. On Wednesday, September 6, 2017 at 1:57:54 PM UTC-6, Mike Bayer wrote: > > On Wed, Sep 6, 2017 at 2:42 PM,wrote: > > Yes, it is a select query. I tried to re-create this just using > psycopg2 > > and named cursors and didn't get an error so I thought it had to be > > somewhere in sqlalchemy > > > > import psycopg2 > > from datetime import datetime > > conn_str = 'host=redshift_host port=redshift_port username=username > > password=password' > > query = 'SELECT * FROM test WHERE timestamp > %s' > > params = (datetime(2020, 12, 31, 0, 0),) > > with psycopg2.connect(conn_str) as conn: > > with conn.cursor(name='test_cursor') as cur: > > cur.itersize=1000 # default is 2000, but the server I connected > to > > only allowed 1000 > > cur.execute(query, params) > > results = [r for r in cur] > > > > > > I tried connecting to the same server and running the same query. No > error > > is returned and I end up with an results as an empty list. > > where's the fetchmany() call ? > > > > > > > > On Wednesday, September 6, 2017 at 12:27:13 PM UTC-6, Mike Bayer wrote: > >> > >> On Wed, Sep 6, 2017 at 12:21 PM, wrote: > >> > I've run into an issue with Redshift and I thought I would post it > here > >> > before logging a bug. If I run a query against a Redshift database > >> > table > >> > that happens to return 0 rows I get an error when I try to iterate > over > >> > the > >> > result set. I run a pretty simple script that is looking for rows > where > >> > a > >> > timestamp field contains a date time that is greater than the last > time > >> > the > >> > query was run. Most of the time there are several thousand rows, but > at > >> > times there are no rows that have been updated and the query returns > a > >> > result set with no records. > >> > > >> > It is simple enough to reproduce simply be generating a query that > >> > always > >> > returns 0 records. > >> > >> is it a SELECT query? > >> > >> I don't see anything on the SQLAlchemy side that's doing the wrong > >> thing here, the server-side cursors option just means we say > >> cursor.fetchmany() one or more times to pre-buffer rows from the > >> cursor. We do not "open" a second server side cursor.psycopg2 is > >> reporting the error at the moment the code is calling fetchmany(), > >> which does not advertise as opening a new cursor. > >> > >> > >> > > >> > from sqlalchemy import create_engine > >> > engine = > >> > > >> > > create_engine('postgres+psycopg2://username:password@redshifthost:port/db_name') > > > >> > with engine.connect().execute_options(stream_results=True) as conn: > >> > results = conn.execute(query) > >> > data = [r for r in results] > >> > > >> > Traceback (most recent call last): > >> > File > >> > > >> > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", > > > >> > line 1174, in fetchone > >> > row = self._fetchone_impl() > >> > File > >> > > >> > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", > > > >> > line 1296, in _fetchone_impl > >> > self.__buffer_rows() > >> > File > >> > > >> > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", > > > >> > line 1283, in __buffer_rows > >> > self.__rowbuffer = collections.deque(self.cursor.fetchmany(size)) > >> > psycopg2.InternalError: opening multiple cursors from within the same > >> > client > >> > connection is not allowed. > >> > The above exception was the direct cause of the following exception: > >> > Traceback (most recent call last): > >> > File "", line 3, in > >> > File "", line 3, in > >> > File > >> > > >> > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", > > > >> > line 870, in __iter__ > >> > row = self.fetchone() > >> > File > >> > > >> > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", > > > >> > line 1183, in fetchone > >> > self.cursor, self.context) > >> > File > >> > > >> > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/base.py", > > > >> > line 1402, in _handle_dbapi_exception > >> > exc_info >
Re: [sqlalchemy] Error streaming results of query with 0 rowcount from Redshift
Yes, it is a select query. I tried to re-create this just using psycopg2 and named cursors and didn't get an error so I thought it had to be somewhere in sqlalchemy import psycopg2 from datetime import datetime conn_str = 'host=redshift_host port=redshift_port username=username password=password' query = 'SELECT * FROM test WHERE timestamp > %s' params = (datetime(2020, 12, 31, 0, 0),) with psycopg2.connect(conn_str) as conn: with conn.cursor(name='test_cursor') as cur: cur.itersize=1000 # default is 2000, but the server I connected to only allowed 1000 cur.execute(query, params) results = [r for r in cur] I tried connecting to the same server and running the same query. No error is returned and I end up with an results as an empty list. On Wednesday, September 6, 2017 at 12:27:13 PM UTC-6, Mike Bayer wrote: > > On Wed, Sep 6, 2017 at 12:21 PM,> wrote: > > I've run into an issue with Redshift and I thought I would post it here > > before logging a bug. If I run a query against a Redshift database > table > > that happens to return 0 rows I get an error when I try to iterate over > the > > result set. I run a pretty simple script that is looking for rows where > a > > timestamp field contains a date time that is greater than the last time > the > > query was run. Most of the time there are several thousand rows, but at > > times there are no rows that have been updated and the query returns a > > result set with no records. > > > > It is simple enough to reproduce simply be generating a query that > always > > returns 0 records. > > is it a SELECT query? > > I don't see anything on the SQLAlchemy side that's doing the wrong > thing here, the server-side cursors option just means we say > cursor.fetchmany() one or more times to pre-buffer rows from the > cursor. We do not "open" a second server side cursor.psycopg2 is > reporting the error at the moment the code is calling fetchmany(), > which does not advertise as opening a new cursor. > > > > > > from sqlalchemy import create_engine > > engine = > > > create_engine('postgres+psycopg2://username:password@redshifthost:port/db_name') > > > > with engine.connect().execute_options(stream_results=True) as conn: > > results = conn.execute(query) > > data = [r for r in results] > > > > Traceback (most recent call last): > > File > > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", > > > > line 1174, in fetchone > > row = self._fetchone_impl() > > File > > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", > > > > line 1296, in _fetchone_impl > > self.__buffer_rows() > > File > > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", > > > > line 1283, in __buffer_rows > > self.__rowbuffer = collections.deque(self.cursor.fetchmany(size)) > > psycopg2.InternalError: opening multiple cursors from within the same > client > > connection is not allowed. > > The above exception was the direct cause of the following exception: > > Traceback (most recent call last): > > File "", line 3, in > > File "", line 3, in > > File > > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", > > > > line 870, in __iter__ > > row = self.fetchone() > > File > > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", > > > > line 1183, in fetchone > > self.cursor, self.context) > > File > > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/base.py", > > > > line 1402, in _handle_dbapi_exception > > exc_info > > File > > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/util/compat.py", > > > > line 203, in raise_from_cause > > reraise(type(exception), exception, tb=exc_tb, cause=cause) > > File > > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/util/compat.py", > > > > line 186, in reraise > > raise value.with_traceback(tb) > > File > > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", > > > > line 1174, in fetchone > > row = self._fetchone_impl() > > File > > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", > > > > line 1296, in _fetchone_impl > > self.__buffer_rows() > > File > > > "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", > > > > line 1283, in __buffer_rows > > self.__rowbuffer = collections.deque(self.cursor.fetchmany(size)) > > sqlalchemy.exc.InternalError: (psycopg2.InternalError) opening multiple > > cursors from within the
[sqlalchemy] Re: Error streaming results of query with 0 rowcount from Redshift
Sorry, that code sample contained a typo. It should be: from sqlalchemy import create_engine engine = create_engine('postgres+psycopg2://username:password@ redshifthost:port/db_name') with engine.connect().execution_options(stream_results=True) as conn: results = conn.execute(query) data = [r for r in results] -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Error streaming results of query with 0 rowcount from Redshift
I've run into an issue with Redshift and I thought I would post it here before logging a bug. If I run a query against a Redshift database table that happens to return 0 rows I get an error when I try to iterate over the result set. I run a pretty simple script that is looking for rows where a timestamp field contains a date time that is greater than the last time the query was run. Most of the time there are several thousand rows, but at times there are no rows that have been updated and the query returns a result set with no records. It is simple enough to reproduce simply be generating a query that always returns 0 records. from sqlalchemy import create_engine engine = create_engine( 'postgres+psycopg2://username:password@redshifthost:port/db_name') with engine.connect().execute_options(stream_results=True) as conn: results = conn.execute(query) data = [r for r in results] Traceback (most recent call last): File "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 1174, in fetchone row = self._fetchone_impl() File "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 1296, in _fetchone_impl self.__buffer_rows() File "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 1283, in __buffer_rows self.__rowbuffer = collections.deque(self.cursor.fetchmany(size)) psycopg2.InternalError: opening multiple cursors from within the same client connection is not allowed. The above exception was the direct cause of the following exception: Traceback (most recent call last): File "", line 3, in File "", line 3, in File "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 870, in __iter__ row = self.fetchone() File "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 1183, in fetchone self.cursor, self.context) File "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception exc_info File "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise raise value.with_traceback(tb) File "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 1174, in fetchone row = self._fetchone_impl() File "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 1296, in _fetchone_impl self.__buffer_rows() File "/Users/dstovall/.virtualenvs/connectors/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 1283, in __buffer_rows self.__rowbuffer = collections.deque(self.cursor.fetchmany(size)) sqlalchemy.exc.InternalError: (psycopg2.InternalError) opening multiple cursors from within the same client connection is not allowed. I get the same problem using fetchone() or fetchmany() on the result set. If I don't use .execute_options(stream_results=True) to create the connection then I don't get the error. It seems that option somehow ends up creating multiple server-side cursors when the are no records in the result set. If any records are returned in the result set then I don't get the error. Right now, as a work around I simply check the results.rowcount attribute before iterating through results, but I don't really like that work around. Has anyone else encountered this problem with streamed results from Redshift? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Querying from MS SQL Server functions
Hello, I have been trying to find the appropriate syntax to query from a user defined function. I am dealing with a SQL Server 2008 database, and I am using sqlalchemy 0.8.4 with pyodbc 3.0.7. The machine the python app is running on is Windows 7 64 bit, but I am using 32 bit python. I followed the pointers in this threadhttps://groups.google.com/forum/#!searchin/sqlalchemy/call$20database$20function/sqlalchemy/tYVxitn9j1A/L5URn6ryHWUJ, but it seems that I can't get sqlalchemy to behave properly. The sql I am trying to execute looks like the following select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638, DEFAULT) Note that there are no parameter names and the usage of the Default keyword. Null can be used in place of the Default, but that could be problematic if a parameter's default value isn't null. Furthermore, this function can be joined to tables within the database like so select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638, DEFAULT) uf left outer join db_table dt on uf.table_id = dt.id Is there anyway to handle this situation? Below are the attempts that I have tried function_call = select([column('table_id'),column('widget_type'),column('effective_date'),column('widget_id')], from_obj=[func.some_user_defined_function(bindparam('effective_date_start',value='2013-12-01'), bindparam('effective_date_end', value=None,quote=False), bindparam('widget_type_list', value='3'), bindparam('company_id', value=638), bindparam('widget_id', value=None,quote=False))]) This generates the following SQL SELECT table_id, widget_type, effective_date, widget_id FROM some_user_defined_function(:effective_date_start, :effective_date_end, :widget_type_list, :company_id, :widget_id) however I get no results from executing it. I have also tried not using bindparam function_call = select([column('emp_id'),column('plan_type'),column('effective_date'),column('history_answersheet_id')], from_obj=[func.some_user_defined_function('2013-12-01', None, '1', 1591, None)]) and then I get SELECT table_id, widget_type, effective_date, widget_id FROM some_user_defined_function(:some_user_defined_function_1, NULL, :some_user_defined_function_2, :some_user_defined_function_3, NULL) but again no results. (As a side note I am attempting to use a session to execute these objects. To eliminate the possiblity that the issue is within pyodbc I tried the following cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=10.0.0.555;DATABASE=yup;UID=user;PWD=pass') cursor = cnxn.cursor() cursor.execute(select * from some_user_defined_function('2013-12-29', NULL, '3', 638, NULL)) for row in cursor: print row and that did work. So I have two questions. 1. Why can't I see my results from the select objects I am using? 2. How can I pass Default as a parameter to the function? Thanks in advance --Dan Clark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: Querying from MS SQL Server functions
Nevermind about question 2. I really thought I was using parameters that would return something. Apparently not. However, question 2 still remains. --Dan On Monday, December 30, 2013 7:12:04 PM UTC-5, Dan wrote: Hello, I have been trying to find the appropriate syntax to query from a user defined function. I am dealing with a SQL Server 2008 database, and I am using sqlalchemy 0.8.4 with pyodbc 3.0.7. The machine the python app is running on is Windows 7 64 bit, but I am using 32 bit python. I followed the pointers in this threadhttps://groups.google.com/forum/#!searchin/sqlalchemy/call$20database$20function/sqlalchemy/tYVxitn9j1A/L5URn6ryHWUJ, but it seems that I can't get sqlalchemy to behave properly. The sql I am trying to execute looks like the following select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638, DEFAULT) Note that there are no parameter names and the usage of the Default keyword. Null can be used in place of the Default, but that could be problematic if a parameter's default value isn't null. Furthermore, this function can be joined to tables within the database like so select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638, DEFAULT) uf left outer join db_table dt on uf.table_id = dt.id Is there anyway to handle this situation? Below are the attempts that I have tried function_call = select([column('table_id'),column('widget_type'),column('effective_date'),column('widget_id')], from_obj=[func.some_user_defined_function(bindparam('effective_date_start',value='2013-12-01'), bindparam('effective_date_end', value=None,quote=False), bindparam('widget_type_list', value='3'), bindparam('company_id', value=638), bindparam('widget_id', value=None,quote=False))]) This generates the following SQL SELECT table_id, widget_type, effective_date, widget_id FROM some_user_defined_function(:effective_date_start, :effective_date_end, :widget_type_list, :company_id, :widget_id) however I get no results from executing it. I have also tried not using bindparam function_call = select([column('emp_id'),column('plan_type'),column('effective_date'),column('history_answersheet_id')], from_obj=[func.some_user_defined_function('2013-12-01', None, '1', 1591, None)]) and then I get SELECT table_id, widget_type, effective_date, widget_id FROM some_user_defined_function(:some_user_defined_function_1, NULL, :some_user_defined_function_2, :some_user_defined_function_3, NULL) but again no results. (As a side note I am attempting to use a session to execute these objects. To eliminate the possiblity that the issue is within pyodbc I tried the following cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=10.0.0.555;DATABASE=yup;UID=user;PWD=pass') cursor = cnxn.cursor() cursor.execute(select * from some_user_defined_function('2013-12-29', NULL, '3', 638, NULL)) for row in cursor: print row and that did work. So I have two questions. 1. Why can't I see my results from the select objects I am using? 2. How can I pass Default as a parameter to the function? Thanks in advance --Dan Clark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Querying from MS SQL Server functions
Yes, column_literal did the trick. So this is what I have function_call = select([column('table_id'),column('widget_type'),column('effective_date'),column('widget_id')], from_obj=[func.some_user_defined_function('2013-12-01', literal_column(DEFAULT), '1', 1550, literal_column(DEFAULT))]) Then I was able to figure out how to join it to my widgets table. Since the from clause isn't a typical one the selectable must be aliased otherwise sqlalchemy throws up an error. a = alias(function_call, 'test') results = DBSession.query(Widget).join(a, Widget.id== a.c.widget_id).all() My code is pretty rough but it works. Thanks again Michael. --Dan On Monday, December 30, 2013 7:36:40 PM UTC-5, Michael Bayer wrote: On Dec 30, 2013, at 7:12 PM, Dan wpu@gmail.com javascript: wrote: Hello, I have been trying to find the appropriate syntax to query from a user defined function. I am dealing with a SQL Server 2008 database, and I am using sqlalchemy 0.8.4 with pyodbc 3.0.7. The machine the python app is running on is Windows 7 64 bit, but I am using 32 bit python. I followed the pointers in this threadhttps://groups.google.com/forum/#!searchin/sqlalchemy/call$20database$20function/sqlalchemy/tYVxitn9j1A/L5URn6ryHWUJ, but it seems that I can't get sqlalchemy to behave properly. The sql I am trying to execute looks like the following select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638, DEFAULT) Note that there are no parameter names and the usage of the Default keyword. Null can be used in place of the Default, but that could be problematic if a parameter's default value isn't null. Furthermore, this function can be joined to tables within the database like so select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638, DEFAULT) uf left outer join db_table dt on uf.table_id = dt.id Is there anyway to handle this situation? Below are the attempts that I have tried function_call = select([column('table_id'),column('widget_type'),column('effective_date'),column('widget_id')], from_obj=[func.some_user_defined_function(bindparam('effective_date_start',value='2013-12-01'), bindparam('effective_date_end', value=None,quote=False), bindparam('widget_type_list', value='3'), bindparam('company_id', value=638), bindparam('widget_id', value=None,quote=False))]) “DEFAULT” is a fixed keyword here so you’d be looking specifically to not bind any value from the application into a bound placeholder (e.g. a question mark ?). To deliver a fixed keyword, use a construct like “literal_column()”. In addition, you don’t typically need to use bindparam() explicitly unless you are looking to re-use the statement repeatedly . Using a literal Python value will automatically be coerced into a bound parameter. from sqlalchemy.sql import column, select, func, literal_column function_call = select([ column('table_id'), column('widget_type'), column('effective_date'), column('widget_id')]).\ select_from( func.some_user_defined_function( 2013-12-01, literal_column(DEFAULT), 3, 638, literal_column(DEFAULT) ) ) result = session.execute(function_call) SELECT table_id, widget_type, effective_date, widget_id FROM some_user_defined_function(:effective_date_start, :effective_date_end, :widget_type_list, :company_id, :widget_id) this is the SQL that you’d see from printing the statement or turning it into a string. however, if you’re passing the construct to session.execute(), and are watching the SQL output using echo=True (which I recommend when debugging these things), you’d see question marks with Pyodbc, not parameters like :effective_date_start. if you set echo=‘debug’ on your create_engine() you’ll see not just the SQL emitted but also the rows that are received in the raw. To eliminate the possiblity that the issue is within pyodbc I tried the following cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=10.0.0.555;DATABASE=yup;UID=user;PWD=pass') cursor = cnxn.cursor() cursor.execute(select * from some_user_defined_function('2013-12-29', NULL, '3', 638, NULL)) for row in cursor: print row if you’re still testing against pyodbc, please try it like this: cursor.execute(select * from some_user_defined_function(?, NULL, ?, ?, NULL)”, [‘2013-12-29’, ‘3’, 638
Re: [sqlalchemy] Re: problems with filter_by()
On Monday, January 14, 2008 10:59:17 AM UTC-5, Michael Bayer wrote: On Jan 14, 2008, at 10:45 AM, maxi wrote: Thaks for your help. Can you post an example over how to use filter and filter_by in new sqlalchemy versions? http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html#sqlalchemy.orm.query.Query.filter_by Docs are good but for the very lazy :) filter_by(name = 'Dan') or filter(User.name == 'Dan') -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Extending SQL Alchemy core (0.7.x) with new Select type statement
I'm trying to extend SQLA with a construct for using SQL Server's PIVOT functionality. I've written a class and a @compiles function to generate the query for this and this produces the correct query (e.g., given a sqlalchemy.Table and some Column objects it produces the right query). My problem is that the return type is a string, not a sqlalchemy.sql.expression.Select like object, so I can't do something like x = Pivot(table.c.key_col, table.c.pivot_col, ['attribute1, 'attribute2'], from_query) y = sqlalchemy.select([x.c.attribute1]) After Googling around I really couldn't find any examples like this. The examples on the documentation page seem to also just produce strings (and are Executable rather than Selectable anyway). (Mainly http://www.sqlalchemy.org/trac/wiki/UsageRecipes and http://docs.sqlalchemy.org/en/rel_0_7/core/compiler.html) Anyone have some tips on how to make the above do-able? An explanation for how to produce any kind of custom select statement (that could then be selected from) would be fine. Or if this isn't possible currently please let me know. Thanks, Dan -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Many to Many and Unique Object question
I've got a many to many relationship with the association table. The issue I am running into is that a given pesticide entry may have a pest or pests already in the pest table. I don't want to add the pest since it will be a duplicate, however I still want the association of that pest to the pesticide done. I've looked at the Unique Object wiki, however I am not sure that solves my issue. Is there a way that SQLAlchemy can handle this, or do I need to manually build the entries in the PestToPesticide association table? Thanks! Dan PestToPesticide = Table('pest_to_pesticide', Base.metadata, Column('pesticide_id', Integer, ForeignKey('pesticide.row_id')), Column('pest_id', Integer, ForeignKey('pest.row_id')) ) class Pesticide(Base): __tablename__ = 'pesticide' row_id = Column(Integer,primary_key=True) name = Column(String(64), unique=True) pestList =relationship(Pest, secondary=PestToPesticide, backref=pesticide) class Pest(object): row_id = Column(Integer,primary_key=True) name = Column(String(), unique=True) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Many to Many and Unique Object question
Using the collection_class=set is only going to work if I try to add the same pest multiple times into pestList isn't it? My situation is more along the lines that the pest table already has the pest, and the pesticide I want to add also will have that pest, so I want the entries in the pest_to_pesticide created. What currently happens is an IntegrityError is thrown complaining that there is a duplicate pest entry. On Wednesday, April 24, 2013 1:24:59 PM UTC-4, Michael Bayer wrote: pesttopesticide is maintained by relationship() as the secondary table, but the expectation is that rows in this table will be unique. if you are appending duplicate entries to pestList or pesticide you may want to use collection_class=set to maintain uniqueness in the collection. On Apr 24, 2013, at 12:51 PM, dan d...@inlet.geol.sc.edu javascript: wrote: I've got a many to many relationship with the association table. The issue I am running into is that a given pesticide entry may have a pest or pests already in the pest table. I don't want to add the pest since it will be a duplicate, however I still want the association of that pest to the pesticide done. I've looked at the Unique Object wiki, however I am not sure that solves my issue. Is there a way that SQLAlchemy can handle this, or do I need to manually build the entries in the PestToPesticide association table? Thanks! Dan PestToPesticide = Table('pest_to_pesticide', Base.metadata, Column('pesticide_id', Integer, ForeignKey('pesticide.row_id')), Column('pest_id', Integer, ForeignKey('pest.row_id')) ) class Pesticide(Base): __tablename__ = 'pesticide' row_id = Column(Integer,primary_key=True) name = Column(String(64), unique=True) pestList =relationship(Pest, secondary=PestToPesticide, backref=pesticide) class Pest(object): row_id = Column(Integer,primary_key=True) name = Column(String(), unique=True) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Many to Many and Unique Object question
Cool, that's what I wanted to know. I didn't want to miss some function that did this for me if it was there. Thanks alot for the help! Dan On Wednesday, April 24, 2013 1:42:39 PM UTC-4, Michael Bayer wrote: On Apr 24, 2013, at 1:38 PM, dan d...@inlet.geol.sc.edu javascript: wrote: Using the collection_class=set is only going to work if I try to add the same pest multiple times into pestList isn't it? My situation is more along the lines that the pest table already has the pest, and the pesticide I want to add also will have that pest, so I want the entries in the pest_to_pesticide created. What currently happens is an IntegrityError is thrown complaining that there is a duplicate pest entry. well the issue of the pest_to_pesticide table and the unique entries in Pest are two separate things. since this is many to many, sure any number of Pests can be associated with any number of Pesticides, so to achieve this you of course need to work with an existing Pest entry and associate it as needed. Typically, if you know what Pest you want to work with, you look it up in the database, if its not there, you create it. The UniqueObject recipe is one way to make this more invisible but it isn't a requirement.Just to get things to work you might want to keep it simple and just do a get_or_create_pest() function, before turning it into something more transparent. On Wednesday, April 24, 2013 1:24:59 PM UTC-4, Michael Bayer wrote: pesttopesticide is maintained by relationship() as the secondary table, but the expectation is that rows in this table will be unique. if you are appending duplicate entries to pestList or pesticide you may want to use collection_class=set to maintain uniqueness in the collection. On Apr 24, 2013, at 12:51 PM, dan d...@inlet.geol.sc.edu wrote: I've got a many to many relationship with the association table. The issue I am running into is that a given pesticide entry may have a pest or pests already in the pest table. I don't want to add the pest since it will be a duplicate, however I still want the association of that pest to the pesticide done. I've looked at the Unique Object wiki, however I am not sure that solves my issue. Is there a way that SQLAlchemy can handle this, or do I need to manually build the entries in the PestToPesticide association table? Thanks! Dan PestToPesticide = Table('pest_to_pesticide', Base.metadata, Column('pesticide_id', Integer, ForeignKey('pesticide.row_id')), Column('pest_id', Integer, ForeignKey('pest.row_id')) ) class Pesticide(Base): __tablename__ = 'pesticide' row_id = Column(Integer,primary_key=True) name = Column(String(64), unique=True) pestList =relationship(Pest, secondary=PestToPesticide, backref=pesticide) class Pest(object): row_id = Column(Integer,primary_key=True) name = Column(String(), unique=True) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] SQLAlchemy Consultant needed
Hello, We (QNX Software Systems) have an embedded project developed using SQLAlchemy and using sqlite3 and the database. It works, but needs an expert hand at tuning, debugging, and optimizing. Looking for someone to start ASAP. Please send your resume or qualifications to me @ dcardam...@qnx.com. Will welcome remote workers. Thanks, Dan -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/4NPzACxaypsJ. To post to this group, send email to sqlalchemy@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] Appending to an expired relationship list
Dear list, In our application we've run across what seems to be at least a serious gotcha, if not an actual bug, in SQLAlchemy. Please let me know if you agree, or if there is something I'm doing wrong. Attached is a minimal reproducer (as minimal as I could manage anyway). The assertion at the end of the script fails with SQLAlchemy 0.6.8 and 0.7.6. I'm using MySQL, although I don't think the database matters. (SQLite's nested transaction support is broken, so it won't work.) The most important part is the lazy_create method, which is how we are avoiding race conditions when inserting into tables with a unique constraint. It will either insert a new row with the given unique column value(s), or select the existing row. (The IntegrityError it catches will be a unique constraint violation.) @classmethod def lazy_create(cls, **kwargs): session.begin_nested() try: item = cls(**kwargs) session.add(item) session.commit() except IntegrityError: session.rollback() item = session.query(cls).filter_by(**kwargs).one() return item The problem comes when we later do something like this: task.packages.append(Package.lazy_create(name=u'asdf')) If the Package with name 'asdf' already exists, this .append() call will have no effect. The Package instance is silently discarded from the task.packages list. As we eventually discovered, the reason is that the nested rollback inside lazy_create causes task.packages to be expired. But the .append() method is still called on the expired list because of the way our statement is written. The expired list is discarded, and so the .append() has no effect. In this particular case, we can rewrite the statement as: package = Package.lazy_create(name=u'asdf') task.packages.append(package) which fixes the problem, but it worries me. Shouldn't calling .append() or any other method on an expired relationship list at least raise an exception, given that the list has been discarded? Could SQLAlchemy expire the relationship list without actually replacing the list instance? Then we wouldn't need to worry about whether we are accidentally holding onto references to expired lists which have been replaced out from underneath us. Is there some other way we could avoid this kind of problem? -- Dan Callaghan d...@djc.id.au -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. bz816879.py Description: application/python
[sqlalchemy] turn filtered query with subquery into relationship
Dear List, Hoping for help with following unanswered stackoverflow question. If the approach is not the best, that feedback is good as well. In the case below is it better to just not use relationships and just use the method with the query? Thanks In the code below I want to replace *all_holdings* in Account with a property called *holdings* that returns the *desired_holdings* (which are the holdings representing the latest known quantity which can change over time). I'm having trouble figuring out how to construct the call to relationship. In addition any comments on the appropriateness of the pattern (keeping historic data in a single table and using a max date subquery to get most recent), better alternatives, improvements on the query appreciated. from sqlalchemy import Column, Integer, String, Date, DateTime, REAL, ForeignKey, func from sqlalchemy.orm import relationship, aliased from sqlalchemy.sql.operators import and_, eq from sqlalchemy.ext.declarative import declarative_base from db import session import datetime import string Base = declarative_base() class MySQLSettings(object): __table_args__ = {'mysql_engine':'InnoDB'} class Account(MySQLSettings, Base): __tablename__ = 'account' id = Column(Integer, primary_key=True) name = Column(String(64)) all_holdings = relationship('Holding', backref='account') def desired_holdings(self): max_date_subq = session.query(Holding.account_id.label('account_id'), Holding.stock_id.label('stock_id'), func.max(Holding.as_of).label('max_as_of')). \ group_by(Holding.account_id, Holding.stock_id).subquery() desired_query = session.query(Holding).join(Account, Account.id==account.id).join(max_date_subq).\ filter(max_date_subq.c.account_id==account.id).\ filter(Holding.as_of==max_date_subq.c.max_as_of).\ filter(Holding.account_id==max_date_subq.c.account_id).\ filter(Holding.stock_id==max_date_subq.c.stock_id) return desired_query.all() def __init__(self, name): self.name = name class Stock(MySQLSettings, Base): __tablename__ = 'stock' id = Column(Integer, primary_key=True) name = Column(String(64)) def __init__(self, name): self.name = name class Holding(MySQLSettings, Base): __tablename__ = 'holding' id = Column(Integer, primary_key=True) account_id = Column(Integer, ForeignKey('account.id'), nullable=False) stock_id = Column(Integer, ForeignKey('stock.id'), nullable=False) quantity = Column(REAL) as_of = Column(Date) stock = relationship('Stock') def __str__(self): return Holding(%f, '%s' '%s')%(self.quantity, self.stock.name, str(self.as_of)) def __init__(self, account, stock, quantity, as_of): self.account_id = account.id self.stock_id = stock.id self.quantity = quantity self.as_of = as_of if __name__ == __main__: ibm = Stock('ibm') session.add(ibm) account = Account('a') session.add(account) session.flush() session.add_all([ Holding(account, ibm, 100, datetime.date(2001, 1, 1)), Holding(account, ibm, 200, datetime.date(2001, 1, 3)), Holding(account, ibm, 300, datetime.date(2001, 1, 5)) ]) session.commit() print All holdings by relation:\n\t, \ string.join([ str(h) for h in account.all_holdings ], \n\t) print Desired holdings query:\n\t, \ string.join([ str(h) for h in account.desired_holdings() ], \n\t) The results when run are: All holdings by relation: Holding(100.00, 'ibm' '2001-01-01') Holding(200.00, 'ibm' '2001-01-03') Holding(300.00, 'ibm' '2001-01-05') Desired holdings query: Holding(300.00, 'ibm' '2001-01-05') -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/KKB3-3r5kSAJ. To post to this group, send email to sqlalchemy@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] Single Table Inheritance with same Column Name
Hi all, I'm running SQLAlchemy 0.7.0, against MySQL 5.1. I'm having issues with a field that I'd like shared between the classes in single table inheritance that I would like to have referred to differently in the subclasses: class Post(Base): __tablename__ = 'posts' id = Column(Integer, primary_key = True) type = Column(String(40)) data = Column(String(255)) __mapper_args__ = {'polymorphic_on': type} class Link(Post): __mapper_args__ = {'polymorphic_identity': 'link'} url = Column('data', String(255)) When I run this, I get the following error: sqlalchemy.exc.ArgumentError: Column 'data' on class class 'api.models.posts.Link' conflicts with existing column 'posts.data' I searched for answers, and came across the following post: http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg20077.html When I tried its solution, however, I get the error TypeError: getattr(): attribute name must be string when the code actually gets called. The offending line of code is return getattr(obj, self.name) from Module sqlalchemy.orm.descriptor_props:53 in fget. In this case, self.name is an sqlalchemy.orm.attributes.InstrumentedAttribute object. If I cast it to a string, getattr returns the data that I wanted. Is there a better way to do what I'm trying to do, or is this a bug in the code? Thanks, Dan -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Exists/Correlate newbie question
Michael, I removed the correlate and it still works. I would have sworn until I added the correlation I kept getting an error in the subquery. Thanks again! On Mar 28, 12:25 pm, Michael Bayer mike...@zzzcomputing.com wrote: right so, the correlate() can be against multi_obs directly, not just __table__, and also shouldn't be required at all since auto-correlation is sufficient here. On Mar 28, 2011, at 12:23 PM, dan wrote: Thanks for the feedback. I found an example in google groups that got me on the right track. For those interested, my sqlalchemy looks like: multi_obs2 = aliased(multi_obs) subQ = qaqc.db.session.query(multi_obs2).\ filter(multi_obs2.m_date = beginDate).\ filter(multi_obs2.m_date endDate).\ filter(multi_obs2.sensor_id == nnSensorId).\ filter(multi_obs2.d_top_of_hour == 1).\ filter(multi_obs2.d_report_hour == multi_obs.d_report_hour).\ correlate(multi_obs.__table__).\ statement recs = qaqc.db.session.query(multi_obs).\ filter(multi_obs.m_date = beginDate).\ filter(multi_obs.m_date endDate).\ filter(multi_obs.sensor_id == sensorId).\ filter(multi_obs.d_top_of_hour == 1).\ filter(exists(subQ)).\ order_by(multi_obs.m_date.asc()).all() -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://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 sqlalchemy@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: Exists/Correlate newbie question
Thanks for the feedback. I found an example in google groups that got me on the right track. For those interested, my sqlalchemy looks like: multi_obs2 = aliased(multi_obs) subQ = qaqc.db.session.query(multi_obs2).\ filter(multi_obs2.m_date = beginDate).\ filter(multi_obs2.m_date endDate).\ filter(multi_obs2.sensor_id == nnSensorId).\ filter(multi_obs2.d_top_of_hour == 1).\ filter(multi_obs2.d_report_hour == multi_obs.d_report_hour).\ correlate(multi_obs.__table__).\ statement recs = qaqc.db.session.query(multi_obs).\ filter(multi_obs.m_date = beginDate).\ filter(multi_obs.m_date endDate).\ filter(multi_obs.sensor_id == sensorId).\ filter(multi_obs.d_top_of_hour == 1).\ filter(exists(subQ)).\ order_by(multi_obs.m_date.asc()).all() -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Exists/Correlate newbie question
I've got a SQL query I am trying to convert over into sqlalchemy, however I just can't suss out the correlate and exists part of sqlalchemy. My query is: SELECT m_date,sensor_id,m_value,d_report_hour FROM multi_obs mo WHERE m_date = '2011-03-23T00:00:00' and m_date '2011-03-23T24:00:00' AND sensor_id = 4644 AND EXISTS (SELECT d_report_hour FROM multi_obs WHERE m_date = '2011-03-23T00:00:00' AND m_date '2011-03-23T24:00:00' AND sensor_id=518 and mo.d_report_hour=d_report_hour) ORDER BY sensor_id ASC, d_report_hour ASC; I've got my multi_obs table already in an object in sqlalchemy. I am not sure how to get the mo correlation working inside the EXISTS subquery. I cobbled up this: matchDatesQ = qaqc.db.session.query(multi_obs).\ filter(multi_obs.m_date = beginDate).\ filter(multi_obs.m_date endDate).\ filter(multi_obs.sensor_id == nnSensorId).\ filter(multi_obs.d_report_hour == mo.d_report_hour).\ filter(multi_obs.d_top_of_hour == 1).\ correlate(multi_obs).\ subquery() recs = qaqc.db.session.query(multi_obs).\ correlate('mo').\ filter(multi_obs.m_date = beginDate).\ filter(multi_obs.m_date endDate).\ filter(multi_obs.sensor_id == sensorId).\ filter(multi_obs.d_top_of_hour == 1).\ filter(exists(matchDatesQ)).\ order_by(multi_obs.m_date.asc()).all() however, I get errors in the matchesDateQ query about the mo not being defined. I understand why that is, I just am not sure how to go about getting this to work. Any tips/hints are greatly appreciated. Dan -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Implementing fine-grained access control
I'd like to find some way to implement access controls on mapped objects, with the following features: * Example: given a BlogPost object, only the owner, or a superuser, would be allowed to set fields such as title and body. * Example: reading the body field would check the privacy field as well as the current user, and only let the owner read a private field. * The owner should be determined based on a configurable column name, or as the result of a method call. * The current user should be explicitly specified rather than coming from some global state. The intention is not to make unwanted operations impossible, but to offer the programmer a degree of confidence that, so long as he uses the object in a particular way, the security constraints he specifies won't be violated, regardless of logic errors elsewhere (in a web layer, typically). It seems that one possible way to do this would be to use proxy objects to access the real instances. Returning proxies doesn't seem difficult (a mapper extension could do this if the mapped class specifies it desires it). Interaction with the session might be problematic, though, if all you have is proxy objects. Does this seem to be the correct path to follow, or is there a better approach? -- 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: Implementing fine-grained access control
On Nov 22, 11:15 am, Michael Bayer mike...@zzzcomputing.com wrote: I'm assuming the reason for proxy objects is so that usage would continue to look like: blogpost.body = new body Right, exactly. So for that kind of thing, if you want certain operations to proceed under the umbrella of some context, like who the current user is, Python context managers are very neat for this. with security_manager.user(some_user): blogpost.body = new body Using a context manager is a nice idea. Although thread-locals... a bit icky. You'd normally use @property on your BlogPost object to intercept read/set events, or the @validates decorator which catches only set events, to achieve this. BlogPost could find the local context manager usually via thread local. If I use properties with the same names as the columns, how can I avoid them clobbering the actual columns? I did try subclassing DeclarativeMeta to enforce column_prefix='_', but it I think I misunderstood what that does, because it made query(BlogPost).get(...) complain about the DB not having an _id field. Does column_prefix change what SA expects the DB columns look like, or just the names of the attributes? -- 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: Implementing fine-grained access control
Thanks for those tips. InstrumentationManager sounded like the best thing, so I've gone with that idea. Here's the basic structure of it for anyone who's interested: http://pastie.textmate.org/1318179 Thanks again, Michael. -- 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] Secialists question: how to do implement stock-management
Hi, i have a question to database/design specialist. How can (should!) i implement a stock management system in sql-alchemy and python? I get the following data from another system via files - movements: bills from a scanner at a cash deck - movements: from goods-receipt Of course i have also master data on an per article basis. What i want do is keep charge of the stock available in our warehouse. Should i built a warehouse table with [ article / amount of article available ] and update the articles with an update statement like UPDATE warehouse_table SET amount = amount - (bill amount) where article = bill_article ? Would that be a good solution? Is there any literature or reference implementations around? Any hint is much apreciated. Although i have written a couple of database applications, i never had the probleme to change a field (amount field per article) so often. I guess there is a lot of data comming in ... Thanks in advance, Dan -- 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] using funcs within a primary join
Hey, I am trying to create a funky relationship where I want to take a field in a table, take a portion of it and then use that portion to create a relation. Is this even possible? My relation is below reseller = relation(Reseller, uselist = False, primaryjoin = func.substring(credits.c.billing_code, 3, 25) == Reseller.resellerid) Any suggestions to make this happen? --Dan -- 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] Custom type does not seem to honour is_mutable
I have created a custom type in order to store denormalized PKs in a TEXT field. The idea is that the text is converted back and forth from a set of integers: http://paste.pocoo.org/show/249784/ This seems to work OK, however if you make a change to the set it's not picked up by SQLAlchemy on commit. For example, given the following model: class Post(DeclarativeBase): __tablename__ = posts id = Column(Integer, primary_key=True) votes = Column(DenormalizedText) def __init__(self, *args, **kwargs): super(Post, self).__init__(*args, **kwargs) self.votes = self.votes or set() If I do this: post = Post() post.votes.add(3) session.add(post) session.commit() The value '3' is committed to the 'votes' column as expected. However if I then try to modify: post.votes.add(5) session.commit() The change to the set is not saved to the DB, i.e. it's still 3. -- 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: Custom type does not seem to honour is_mutable
On Aug 13, 3:17 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 13, 2010, at 10:01 AM, Dan wrote: I have created a custom type in order to store denormalized PKs in a TEXT field. The idea is that the text is converted back and forth from a set of integers: http://paste.pocoo.org/show/249784/ this is unrelated, but the code is incorrect there, should be def process(value): if value is not None: items = [str(item).strip() for item in value] value = self.separator.join(item for item in items if item) otherwise, you must implement copy_value() on your type. Here, the value isn't being copied so there's nothing to compare to. Yes, sorry for the typo. Realized myself once I'd posted. Usually you're supposed to mixin MutableType which will raise notimplemented for copy_value(). I guess still more docs are needed since you were misled by the is_mutable() method. I've tried the same thing with the MutableType mixin with the same result, i.e: class DenormalizedText(types.TypeDecorator, types.MutableType): -- 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: Custom type does not seem to honour is_mutable
Unfortunately still getting the same result: http://paste.pocoo.org/show/249801/ The test snippet shows that the modified set is not actually saved to the database. On Aug 13, 3:29 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 13, 2010, at 10:24 AM, Dan wrote: On Aug 13, 3:17 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 13, 2010, at 10:01 AM, Dan wrote: I have created a custom type in order to store denormalized PKs in a TEXT field. The idea is that the text is converted back and forth from a set of integers: http://paste.pocoo.org/show/249784/ this is unrelated, but the code is incorrect there, should be def process(value): if value is not None: items = [str(item).strip() for item in value] value = self.separator.join(item for item in items if item) otherwise, you must implement copy_value() on your type. Here, the value isn't being copied so there's nothing to compare to. Yes, sorry for the typo. Realized myself once I'd posted. Usually you're supposed to mixin MutableType which will raise notimplemented for copy_value(). I guess still more docs are needed since you were misled by the is_mutable() method. I've tried the same thing with the MutableType mixin with the same result, i.e: class DenormalizedText(types.TypeDecorator, types.MutableType): MutableType would be first. But again this only just so the NotImplementedError lets you know copy_value() is needed. I could make the default copy_value() raise if is_mutable() is true...though it pains me to add more method calls... from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import * Base = declarative_base() metadata = Base.metadata engine = create_engine('sqlite://', echo=True) from sqlalchemy import types class DenormalizedText(types.TypeDecorator): Stores denormalized primary keys that can be accessed as a set. :param coerce: coercion function that ensures correct type is returned :param separator: separator character impl = types.Text def __init__(self, coerce=int, separator= , **kwargs): self.coerce = coerce self.separator = separator super(DenormalizedText, self).__init__(**kwargs) def bind_processor(self, dialect): def process(value): if value is not None: items = [str(item).strip() for item in value] value = self.separator.join(item for item in items if item) return value return process def result_processor(self, dialect, coltype): def process(value): if not value: return set() return set(self.coerce(item) \ for item in value.split(self.separator)) return process def copy_value(self, value): return set(value) def is_mutable(self): return True class Post(Base): __tablename__ = posts id = Column(Integer, primary_key=True) votes = Column(DenormalizedText) def __init__(self, *args, **kwargs): super(Post, self).__init__(*args, **kwargs) self.votes = self.votes or set() Base.metadata.create_all(engine) session = sessionmaker(engine)() post = Post() post.votes.add(3) session.add(post) session.commit() print --- post.votes.add(5) 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 athttp://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: Custom type does not seem to honour is_mutable
that code snippet is not complete (doesn't create a Session, doesn't add Post to it, doesn't commit() or flush() the session but then removes it so I guess maybe its a scoped_session, don't know) so I don't actually know what you're doing. The test case below adds your assertion, uses the Session properly, and works fine. The previous test I pasted also works (if I bothered to write out a full test for it, you can be sure I ran it). Sorry for lack of context - had to strip out test from a whole lot of other code - not an excuse I know. There must be some other issue here, I'll investigate further and let you know. -- 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: Custom type does not seem to honour is_mutable
The issue appeared to be removing the session instance (session.remove()) - I was trying to get a clean session for testing. Anyway, works fine now - thanks for your help and sorry for wasting your time. On Aug 13, 4:21 pm, Dan danjac...@gmail.com wrote: that code snippet is not complete (doesn't create a Session, doesn't add Post to it, doesn't commit() or flush() the session but then removes it so I guess maybe its a scoped_session, don't know) so I don't actually know what you're doing. The test case below adds your assertion, uses the Session properly, and works fine. The previous test I pasted also works (if I bothered to write out a full test for it, you can be sure I ran it). Sorry for lack of context - had to strip out test from a whole lot of other code - not an excuse I know. There must be some other issue here, I'll investigate further and let you know. -- 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] SQL Alchemy for bigger enterprise application
Hi, i m a developer with some experience in python and django - a framework i realy like. However, i just read the sqlalchemy docu and i think the orm seems to be really professional. Is sqlalchemy the most prominent orm for python? Is it suitable to write larger enterprise applications? Are there any drawbacks to sqlalchemy? Are there any other big orm projects for python, which one should have a look at? Sorry for asking this kind of general questions. But im looking for some decission hints ... Dan -- 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: Acting on creation of model instances
On May 26, 2:17 pm, Michael Bayer mike...@zzzcomputing.com wrote: its in the identity map after the flush succeeds, which is well before after_commit() is called. That sounds reasonable, but I have debug output from after_attach, before_flush, after_flush, before_commit and after_commit, all of which show a session.identity_map without the newly added Story instance. In fact, the only time I see a Story is as the 'instance' argument to after_attach. But it's still doing the INSERT into the stories table. Here's my debug output: http://pastie.textmate.org/private/ixa4sjopmf8nowblec1ra Why would I be seeing that behaviour? Is it indicative of me having set something up wrongly elsewhere? -- 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: Acting on creation of model instances
Thanks for your help. I'm not exactly sure what happened, so I'll have to carefully look over this area again, but part of it was definitely to do with needing the distinct session. Here's the minimal example I extracted: http://pastie.textmate.org/private/lpgkq7gkaypmgkphknr2w Frustratingly, the identity map problem disappeared when I created the minimal example, and after getting the distinct session stuff to work and putting that back in the original code, all seems well. A question, though: on line 11, should I be replacing self.new or appending to it? That is, when the final flush is done by commit(), does session.new still contain everything that was newly added, regardless of manual flush()es that happened before, or does session.new only contain unflushed items? -- 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] Acting on creation of model instances
I've only found partial answers to this problem so far, so I'd like to expand on it here. I have a site in which users post stories, and their friends are notified. In order to decouple different parts of the business logic, I would like to use a publish/subscribe mechanism that raises an event for any new model instance, so that a news feed item referring to that story can be created for each of the author's friends. I believe that I want to create the news feed item only when the story has been successfully committed, so that failure in creating the news feed item doesn't cause the story to be rolled back too. My current code looks like this: class EventExtension(SessionExtension): def __init__(self): self.new = [] def after_flush(self, session, flush_context): self.new = session.new return EXT_CONTINUE def after_commit(self, session): for instance in self.new: fire_event('model/create/%s' % instance.__class__.__name__, instance) self.new = [] return EXT_CONTINUE The reason for the __init__ method is that after_commit was being called without after_flush having been called, because I was calling session.commit() after processing every request, or session.rollback() if an exception was thrown. Perhaps autocommit would suit me better. The problem I have is that the newly created Story doesn't have its relationship attributes populated. For example, author_id==1, but author==None. This particularly confuses me because the docs say that relations are lazily loaded by default. Can someone explain to me what is going on, and clue me in to the correct way to do this? Thank you. -- 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: Acting on creation of model instances
On May 26, 9:43 am, Michael Bayer mike...@zzzcomputing.com wrote: You can expire the attribute manually ahead of time if you want it to reload its value (should be fine within after_commit). No, it seems that in after_commit the newly added instance is not yet in session.identity_map, so session.expire throws an exception: InvalidRequestError: Instance 'Story at 0x102e46ad0' is not persistent within this Session. At this point I'm confused. Why would it not be persistent after a commit? (If I remove the extra code, the Story is persisted just fine, so it's not like the transaction is failing without the extension.) -- 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
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.
[sqlalchemy] Re: SQL Server 2008 geography type
I am having some problems using this approach. How should I use the class STAsText in the GisElement.wkt property (example below)? My current attempts are not working... it seems that the correct statement is generated SELECT :param_1.STAsText(), but the Geography instance is not being passed as a parameter, i.e. the parameters list is empty. example script snippets @compiles_as_bound class STAsText(FunctionElement): name = 'STAsText' class GisElement(object): Represents a geometry value. @property def wkt(self): return STAsText(literal(self, Geography)).select() #... print session.scalar(r1.road_geom.wkt) Error messages 2010-01-27 12:25:16,904 INFO sqlalchemy.engine.base.Engine.0x...4270 SELECT :param_1.STAsText() 2010-01-27 12:25:16,904 INFO sqlalchemy.engine.base.Engine.0x...4270 [] Traceback (most recent call last): File .\sql_server_spatial.py, line 514, in module print session.scalar(r1.road_geom.as_wkt) File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg \sqlalchemy\orm\session.py, line 742, in scalar return self.execute(clause, params=params, mapper=mapper, **kw).scalar() File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg \sqlalchemy\orm\session.py, line 737, in execute clause, params or {}) File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg \sqlalchemy\engine\base.py, line 1043, in execute return Connection.executors[c](self, object, multiparams, params) File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg \sqlalchemy\engine\base.py, line 1105, in _execute_clauseelement return self.__execute_context(context) File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg \sqlalchemy\engine\base.py, line 1128, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg \sqlalchemy\engine\base.py, line 1190, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg \sqlalchemy\engine\base.py, line 1188, in _cursor_execute self.dialect.do_execute(cursor, statement, parameters, context=context) File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg \sqlalchemy\engine\default.py, line 220, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', [42000] [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near ':'. (102) (SQLExecDirectW)) u'SELECT :param_1.STAsText()' [] -- 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: SQL Server 2008 geography type
That fixed it :) and thank you Michael for your quick response to my questions. -- 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: SQL Server 2008 geography type
Thank you Michael, that really helped a lot. -- 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: Tutorial program 'selectdemo.py'
Thanks much! I couldn't find where that module is buried inside the documentation, hence my question. Best Regards, Dan Presley --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Why has sum been removed from sqlalchemy.orm.query.Query ?
Using sqlalchemy version 0.5.2, I've noticed that the sum method seems to have been removed from Query : session.query(MyClass).sum(MyClass.price) 'Query' object has no attribute 'sum' I can't find this change documented anywhere : it was present in 0.4 as I recall. Any reason for this ? --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Why has sum been removed from sqlalchemy.orm.query.Query ?
That worked great, thanks for your help. 2009/6/23 Michael Bayer mike...@zzzcomputing.com: Dan wrote: Using sqlalchemy version 0.5.2, I've noticed that the sum method seems to have been removed from Query : session.query(MyClass).sum(MyClass.price) 'Query' object has no attribute 'sum' I can't find this change documented anywhere : it was present in 0.4 as I recall. looks like we did apply_sum() but not sum(). its redundant. use query.value(func.sum(somecolumn)). -- Dan Jacob Skype: danjac40 Mobile: (++44) (0)7735452075 --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How does query.get() work?
It's not so much that I'm querying but that I get a set of id's from the user and I've got some logic that will often change some of the values. I wanted to take advantage of SA's orm capabilities as opposed to issuing selects and updates. It's possible in the logic that I already have some of the entities retrieved, but it didn't make sense for me to pass around a map, knowing that SA already keeps one. And that still doesn't seem quite right--having to manage which objects I've previously selected. Perhaps I should just issue the updates. Christiaan Putter wrote: Hi, Small extract from the query's _get method: if not self._populate_existing and not refresh_state and not self._mapper_zero().always_refresh and lockmode is None: try: instance = self.session.identity_map[key] state = attributes.instance_state(instance) if state.expired: try: state() except orm_exc.ObjectDeletedError: self.session._remove_newly_deleted(state) return None return instance except KeyError: pass So you have access to the identity map through session.identity_map[key], where key is: key = self._only_mapper_zero(get() can only be used against a single mapped class.).identity_key_from_primary_key(ident) ident is the primary key for the record you're looking for. The above will basically return the instance to you if it's already in the identity map. Though state() will still execute sql to refresh attributes. So what you can do is build a loop using something like that going through all the primary keys you'd like to get directly from the identity mapper. I'm assuming it's using a pretty good hash so access should be some constant factor. Then you can use the normal query.get() to retrieve the ones that failed from the database. This is really something I would advise against though. What exactly are you querying for? Why don't you just keep a list of all the instances you've already loaded? 2009/3/25 Dan F danielfal...@gmail.com: I understand what get() is supposed to do, but it doesn't clear it up because it still seems like there should be a way of retrieving a *set* of records back from the database at once. I only see a couple choices currently. Either I can use filter() and retrieve every record in the set (even the ones that are mapped), or I can use get on each row individually. Since get() checks the identity map, it won't get records it doesn't need to get, but on the other hand, each record has to get retrieved in its own call. Does this make sense, and do I have it right? Thanks. On Mar 24, 2:08 am, Christiaan Putter ceput...@googlemail.com wrote: Hi, You won't be able to get() multiple objects at the same time. query(SomeClass).get(pk1, pk2, pk3) takes in a tuple of values representing the primary key of some record in your table. In this case the primary key consists of three separate columns (thus a composite key), though the record they identify will always be unique within your table. That's sort of the point of it being a primary key. Read the docs for an explanation of what parameters get() expects. Of course you could use filter() and get the same result. I'm not sure but I guess the actual SQL executed by SA should look exactly the same, set echo to True and have a look. The difference being of course that you can use filter to return more then one record. I'm not sure how get() works on databases that don't need primary keys. I'm guessing it won't. Maybe it's in the docs. Hope that cleared things up. Regards, Christian 2009/3/23 Dan F danielfal...@gmail.com: Hi, Is there a difference between using query.get(ident) and using query.filter(MyClass.id.in_())? Specifically with regard to how the mapper is used? If I'm right in my assumption, get() uses the map to avoid extra lookups, but I question whether the filter method is doing the same. If I'm correct, shouldn't there be a way to get() multiple objects at the same time? Thanks. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How does query.get() work?
I understand what get() is supposed to do, but it doesn't clear it up because it still seems like there should be a way of retrieving a *set* of records back from the database at once. I only see a couple choices currently. Either I can use filter() and retrieve every record in the set (even the ones that are mapped), or I can use get on each row individually. Since get() checks the identity map, it won't get records it doesn't need to get, but on the other hand, each record has to get retrieved in its own call. Does this make sense, and do I have it right? Thanks. On Mar 24, 2:08 am, Christiaan Putter ceput...@googlemail.com wrote: Hi, You won't be able to get() multiple objects at the same time. query(SomeClass).get(pk1, pk2, pk3) takes in a tuple of values representing the primary key of some record in your table. In this case the primary key consists of three separate columns (thus a composite key), though the record they identify will always be unique within your table. That's sort of the point of it being a primary key. Read the docs for an explanation of what parameters get() expects. Of course you could use filter() and get the same result. I'm not sure but I guess the actual SQL executed by SA should look exactly the same, set echo to True and have a look. The difference being of course that you can use filter to return more then one record. I'm not sure how get() works on databases that don't need primary keys. I'm guessing it won't. Maybe it's in the docs. Hope that cleared things up. Regards, Christian 2009/3/23 Dan F danielfal...@gmail.com: Hi, Is there a difference between using query.get(ident) and using query.filter(MyClass.id.in_())? Specifically with regard to how the mapper is used? If I'm right in my assumption, get() uses the map to avoid extra lookups, but I question whether the filter method is doing the same. If I'm correct, shouldn't there be a way to get() multiple objects at the same time? Thanks. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: any effort getting sqlalchemy to work on ironpython is going on ?
On Thu, Sep 11, 2008 at 10:01 PM, sakesun [EMAIL PROTECTED] wrote: Traceback (most recent call last): File stdin, line 1, in module IndexError: Index was outside the bounds of the array. I've had that before on complex python packages (e.g. Jinja2), I'm pretty sure it's an internal error in IronPython, but beats me as to where it's happening. You should try running it under the debugger. -Dan --~--~-~--~~~---~--~~ 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: Result Set From An Oracle Function
Not sure how to do it otherwise. This is how its been coded -- what is the alternative? On May 9, 8:34 am, Michael Bayer [EMAIL PROTECTED] wrote: On May 9, 2008, at 4:26 AM, Dan wrote: Using cx_Oracle, the following does the trick (note this is a contrived example): def test(orcl_conn): curs = orcl_conn.cursor() cursorToBind = orcl_conn.cursor() curs.execute(begin :cr1 := aaa_test(pWhen = :arg1); end;, arg1 = None, cr1 = cursorToBind) marketData = {}# dictionary to hold the records i = 1 for col in cursorToBind.fetchall(): marketData[str(i)] = col i = i + 1 return marketData why is it necessary to send the cursor in as a bind parameter ? I've used custom PL/SQL plenty and i've never heard of that technique. --~--~-~--~~~---~--~~ 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] Quoted Column Name Problem
I have an Oracle schema that has a column name labeled as 'when' (without the quotes). Everything works fine in PL/SQL when referencing this column -- not special handling needs to be done. However, we created a sqlalchemy definition as follows: monitor_mts_table = Table('monitor_mts', metadata, Column('monitor_mts_id', Integer, Sequence('seq_monitor_mts'), primary_key = True), Column('when', OracleDateTime), Column('status', Integer), Column('created_date', OracleDateTime), ) When we reference this we get a database error from oracle that the column monitor_mts.when does not exist. Notice the double quotes. This is what is throwing off oracle since the column definition in the table is not double quoted. I have tried appending the clause quote=False to the Column definition above but it does not affect what is being sent to the database -- still a doubled quoted column name. Any ideas how to work around this without changing the underlying table definition in Oracle? Thanks, Dan --~--~-~--~~~---~--~~ 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] Result Set From An Oracle Function
I am trying to call an Oracle function (that does not take any parameters) and returns a result set. Here is the code I used: s = select([*], from_obj=[func.aaa_test()], bind=engine) However, when I issue this I get the following error: DatabaseError: (DatabaseError) ORA-04044: procedure, function, package, or type is not allowed here 'SELECT * FROM aaa_test' {} How can I rework this? Dan --~--~-~--~~~---~--~~ 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] ConcurrentModificationError without concurrent modification?
ConcurrentModificationError: Deleted rowcount 3 does not match number of objects deleted 1 raised by this code: with Session() as session: dl = session.merge(dl) session.delete(dl) There are no other sessions alive, so how can that possibly be a concurrent modification? Using sqlalchemy 0.4 beta 5, python 2.5, sqllite. dl has a many-to-many relationship associated with it. Any ideas why this is happening? Thanks, -Dan --~--~-~--~~~---~--~~ 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: ConcurrentModificationError without concurrent modification?
On 10/1/07, Michael Bayer [EMAIL PROTECTED] wrote: most likely too many association rows are present in the m2m table. Do you mean there were redundant associations? How can I check if that's the cause of the problem? Thanks, -Dan --~--~-~--~~~---~--~~ 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: 0.4 and server-side cursors
looks like it won't work using psycopg2's named cursor implementation, at least not without some patching. it seems like psycopg should fill in cursor.description after it issues the DECLARE, but it doesn't. you can do it using one normal (non-named) cursor in SA using the method above, but it would probably require some significant work, since you'd have to transform SELECT statements into FETCH statements. anyway, thanks for the fix! On Sep 26, 8:01 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Sep 26, 2007, at 5:31 PM, Dan Watson wrote: cursor.description isn't available after the DECLARE (analogous to conn.cursor(x)), but is available after a FETCH (analogous to a cur.fetchXXX()). If you want to get rid of the buffering resultproxy, you can do a FETCH ABSOLUTE 0 FROM cursor_name - that will make cursor.description available, and leave the SS cursor positioned before the first row. hm does that require a second cursor ? its giving me cant execute twice on a named cursor. if so, the current approach we have is probably more efficient... --~--~-~--~~~---~--~~ 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] 0.4 and server-side cursors
It seems that something changed in 0.4 that causes server-side cursors (in postgresql) to fail. I'm issuing the DECLARE/FETCH commands manually through connection.execute, not using server_side_cursors in the dialect, since I only want certain queries to use them. I verified that this works on the rel_0_3 branch. Here's a minimal test case: from sqlalchemy import * engine = create_engine( 'postgres://user:[EMAIL PROTECTED]:port/db' ) conn = engine.connect() conn.execute( DECLARE test_cur CURSOR WITH HOLD FOR SELECT * FROM table ) res = conn.execute( FETCH FORWARD 64 FROM test_cur ) for row in res: print row This results in a InterfaceError: cursor already closed exception when iterating over the resultproxy. Any ideas? Dan --~--~-~--~~~---~--~~ 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: 0.4 and server-side cursors
cursor.description isn't available after the DECLARE (analogous to conn.cursor(x)), but is available after a FETCH (analogous to a cur.fetchXXX()). If you want to get rid of the buffering resultproxy, you can do a FETCH ABSOLUTE 0 FROM cursor_name - that will make cursor.description available, and leave the SS cursor positioned before the first row. On Sep 26, 4:34 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Sep 26, 2007, at 3:52 PM, Dan Watson wrote: It seems that something changed in 0.4 that causes server-side cursors (in postgresql) to fail. I'm issuing the DECLARE/FETCH commands manually through connection.execute, not using server_side_cursors in the dialect, since I only want certain queries to use them. I verified that this works on the rel_0_3 branch. Here's a minimal test case: from sqlalchemy import * engine = create_engine( 'postgres://user:[EMAIL PROTECTED]:port/db' ) conn = engine.connect() conn.execute( DECLARE test_cur CURSOR WITH HOLD FOR SELECT * FROM table ) res = conn.execute( FETCH FORWARD 64 FROM test_cur ) for row in res: print row This results in a InterfaceError: cursor already closed exception when iterating over the resultproxy. one thing I'd note is that this approach, well it *shouldn't* go very far in 0.3, because when SS cursors are used, psycopg2 does not have cursor.description available until the first row is fetched, and ResultProxy immediately fetches cursor.description; this is why we made a separate buffered ResultProxy which only takes effect when server_side_cursors=True just to deal with this. but im not familiar with FETCH FORWARD 64 and it seems to be prefetching the description field somehow ? is there a way to combine this with a regular SELECT so that we wouldnt need the buffered result handle ? in 0.4, the error is just because your statement doesnt start with SELECT, which results in it just grabbing cursor.rowcount and closing immediately; this removes the need to explicitly close all INSERT/UPDATE/DELETE statements which otherwise can hang open and leave things locked. FETCH is added to the regexp in r3521. --~--~-~--~~~---~--~~ 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: backref relation is None instead of list?
On 9/9/07, Michael Bayer [EMAIL PROTECTED] wrote: I cant reproduce this, although the error to me seems like you are actually saying c._parent = [] (some list object). If thats not it, send along a reproducing test script. I tried to recreate it in the shell and failed. I then loaded my application, changed _parents to parent and it just worked. I don't think the change did it, I think it just disappeared (maybe the reboot?) If it happens again I'll post it on this mailing list. Thanks for the tip about one-to-many relations, I was hoping they worked like that, but when I got the errors I assumed it must not. Sorry for the false alarm, -Dan --~--~-~--~~~---~--~~ 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] TypeError with 0.4 and python 2.3
Right now, importing sqlalchemy on python 2.3 is broken. __name__ is readonly in 2.3, so the import fails: from sqlalchemy import * Traceback (most recent call last): File stdin, line 1, in ? File /home/watsond/python_packages/sqlalchemy/__init__.py, line 9, in ? from sqlalchemy.schema import * File /home/watsond/python_packages/sqlalchemy/schema.py, line 1087, in ? class MetaData(SchemaItem): File /home/watsond/python_packages/sqlalchemy/schema.py, line 1180, in MetaData connect = util.deprecated(connect) File /home/watsond/python_packages/sqlalchemy/util.py, line 552, in deprecated func_with_warning.__name__ = func.__name__ TypeError: readonly attribute I'm not sure what side-effects it would cause (my guess would be harder-to-read tracebacks when calling deprecated methods?), but commenting out the assignment got me up and running again. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: sa newbie
thanks! On 3/6/07, Jonathan Ellis [EMAIL PROTECTED] wrote: try db.books.select(db.books.c.book_skus.like('abcd%')) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] sa newbie
I'm trying to track down the syntax for using a 'like' clause with sql soup. I'm trying to do something like select book_sku from books where book_sku like 'abcd%'; best i can tell, my syntax should look something like: skus = db.books.select(book_skus.like('abcd%')) but I'm getting an error, so obviously not. Pointers? tia, dan --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---