Re: [sqlalchemy] Getting a triple of related id fields

[Dan Stromberg]

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

[Dan Stromberg]

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?

[Dan Stromberg]

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?

[Dan Stromberg]

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

[Dan Stromberg]

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'")

['Dan Stromberg [External]' via sqlalchemy]

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'")

['Dan Stromberg [External]' via sqlalchemy]

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'")

['Dan Stromberg [External]' via sqlalchemy]

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'")

['Dan Stromberg [External]' via sqlalchemy]

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'")

['Dan Stromberg [External]' via sqlalchemy]

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'")

['Dan Stromberg [External]' via sqlalchemy]

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'")

['Dan Stromberg [External]' via sqlalchemy]

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'")

['Dan Stromberg' via sqlalchemy]

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

[dan . bar . dov]

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?

[dan . bar . dov]

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?

[dan . bar . dov]

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?

[dan . bar . dov]

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

[dan]

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

[dan]

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

[dan]

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

[dan]

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

[Dan]

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

[Dan]

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

[Dan]

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()

[Dan Andreescu]

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

[Dan Farmer]

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

[dan]

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

[dan]

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

[dan]

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

[Dan Cardamore]

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

[Dan Callaghan]

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

[dan]

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

[Dan Getelman]

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

[dan]

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

[dan]

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

[dan]

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

[Dan Ellis]

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

[Dan Ellis]

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

[Dan Ellis]

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

[Dan @ Austria]

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

[Dan]

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

[Dan]

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

[Dan]

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

[Dan]

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

[Dan]

 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

[Dan]

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

[Dan @ Austria]

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

[Dan Ellis]

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

[Dan Ellis]

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

[Dan Ellis]

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

[Dan Ellis]

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

[Dan Kuebrich]

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

[Dan K]

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

[Dan Kuebrich]

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

[dan]

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

[dan]

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

[dan]

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'

[Dan Presley]

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 ?

[Dan]

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 ?

[Dan Jacob]

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?

[Dan F]

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?

[Dan F]

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 ?

[Dan Eloff]

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

[Dan]

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

[Dan]

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

[Dan]

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?

[Dan Eloff]

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?

[Dan Eloff]

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

[Dan Watson]

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

[Dan Watson]

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

[Dan Watson]

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?

[Dan Eloff]

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

[Dan Watson]

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

[Dan Trevino]

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

[dan]

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
-~--~~~~--~~--~--~---