[sqlalchemy] Joined/Eager loading into a non-relationship

[Nick Whyte]

Hey, 

I'm working on a more complex problem with the ORM functionality of SQLA. 

I have a reasonably simple relationship, ie, 

class A(Base):
id = sa.Column(sa.Integer(), primary_key=True)
b_collection = sa.orm.relationship('B')


class B(Base):
id = sa.Column(sa.Integer(), primary_key=True)
a_id = sa.Column(sa.Integer(), sa.ForeignKey('a.id', 
ondelete='CASCADE'))
a = sa.orm.relationship('A')

c_id = sa.Column(sa.Integer(), sa.ForeignKey('c.id', 
ondelete='CASCADE'))
c = sa.orm.relationship('C')

class C(Base):

id = sa.Column(sa.Integer(), primary_key=True)
newvalue = ?? ??

And I query the model like so, and iterate and access via the relationships.

obj = session.query(A).first()

I'm at a point now where I need to add a bit more information to "C", 
during the time of query - ie, this depends on the web request context, so 
(i don't think), *column_property *will work for me. 

This extra information is in the form of a sa.select() which returns tuples 
of (c.id, c.newvalue). I can easily do a join on these two when querying C 
directly, ie: *session.query(C, extra_info.c.newvalue).join() *However, 
it seems doing this with nested collections is a bit harder. 

One option (which I think I may have to end up taking) is going to be 
iterating the entire extra info query, and store it in a dict, and iterate 
through every C record in the relationship, however this isn't going to be 
as performant as I would like.

I've attempted to work with *contains_eager*, however this doesn't seem to 
work for non-relationship fields, which is what this new field would 
ideally end up being, in the sense of the ORM.

It seems deferred might be able to help me out here, but there doesn't seem 
to be any API that allows me to load into a deferred property from a higher 
level query. 

Any suggestions would be great, and hopefully help me stop tearing me hair 
out!

Cheers!

-- 
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] MySQL server has gone away

[Dev Mukherjee]

On Wed, Jun 15, 2016 at 7:27 AM, Jeffrey Yunes  wrote:

> Hi all,
> After successful queries and a 10 minute wait, I'm getting the popular
> "MySQL server has gone away." I have a single-threaded app and my
> pool_recycle is way less than my wait_timeout.
>
> Do I need to create a new session after the pool recycles? I'd love to
> know which part of the docs I've missed.
>
> I'm using oursql, so my problem sounds related to:
> https://groups.google.com/forum/#!searchin/sqlalchemy/oursql$20gone$20away/sqlalchemy/K9Pk2pXbLgQ/XWzHnp5Ev4EJ
>
> Any suggestions?
>
>
This might not have anything to do with your particular issue but I thought
I'd share a particular instance we had the same things happening to us. And
it turned out to have nothing to do with SQLAlchemy.

We had an app running on Apache + mod_wsgi on a VM and the Web server and
MySQL server were on the same VM. The VM didn't have much resources
assigned to it.

It turned out to be a disk IO on the VM host causing the MySQL to "go
away". We have the same application running on AWS with hundreds of users
and a shared MySQL backend and have had no issues.

Once again; this might have nothing to do with your issue but just throwing
it out there.

-- 
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] MySQL server has gone away

[Jeffrey Yunes]

Hi Mike and all,
So, mostly good news.

First to answer your question, I'm not aware of any long standing 
queries. During the 10 minutes between queries, `SHOW PROCESSLIST` 
indicates there are no queries running (two sleeping). The only remotely 
suspicious thing in the query log is a `BEGIN (implicit)` with no 
matching `END`.


Now the good news. According to `SHOW VARIABLES` wait_timeout was set to 
the default of 28800. But as I'm sure you've heard before, I queried 
that in an interactive terminal, where it was set to 
interactive_timeout. My wait_timeout for a non-interactive terminal was 
set to the default of 10 minutes. Embarrassingly, I've done that once 
before.


Anyway, what's unresolved is why initializing an engine with 
pool_recycle=300 does not refresh my session's underlying connection 
after 5 minutes, when the timeout is 10 minutes. I'm using the session 
object like this:


query = settings.alchemy_session.query(MyTable).filter(MyTable.name == 
name)

for row in query:
  pass
[nothing else with settings.alchemy_session]

Is there something else I need to do so my session releases or refreshes 
its connection from the pool?


Thanks!

On 14 Jun 2016, at 16:28, Mike Bayer wrote:

not really.  Are you leaving a long-running transaction open and not 
closing it (or rolling back / committing) ?




On 06/14/2016 07:06 PM, Jeffrey Yunes wrote:

Sorry, no dice!

I switched to pymysql.

I'm connecting via 127.0.0.1, so I don't think it's a network issue.

Pretty much the same error...

Any thoughts?

2016-06-14 15:45:20,889 INFO sqlalchemy.engine.base.Engine SHOW
VARIABLES LIKE 'sql_mode'
...
2016-06-14 15:56:42,530 INFO sqlalchemy.pool.QueuePool Invalidate
connection 
(reason: OperationalError:(2013, 'Lost connection to MySQL server 
during

query'))
Traceback (most recent call last):
  File "/home/user/projects/my_project/bin/test.py", line 533, in 


main()
  File "/home/user/projects/my_project/my_package/x/y.py", line 97, 
in func

for a, b in query:
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
line 2761, in __iter__
return self._execute_and_instances(context)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
line 2776, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 914, in execute
return meth(self, multiparams, params)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
line 323, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, 
params)

  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1010, in _execute_clauseelement
compiled_sql, distilled_params
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1146, in _execute_context
context)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1341, in _handle_dbapi_exception
exc_info
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py",
line 202, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1139, in _execute_context
context)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py",
line 450, in do_execute
cursor.execute(statement, parameters)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/cursors.py",
line 161, in execute
result = self._query(query)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/cursors.py",
line 317, in _query
conn.query(q)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py",
line 835, in query
self._affected_rows = 
self._read_query_result(unbuffered=unbuffered)

  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py",
line 1019, in _read_query_result
result.read()
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py",
line 1302, in read
first_packet = self.connection._read_packet()
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py",
line 961, in _read_packet
packet_header = self._read_bytes(4)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py",
line 998, in _read_bytes
2013, "Lost connection to MySQL server during query")
sqlalchemy.exc.OperationalError: 

Re: [sqlalchemy] MySQL server has gone away

[Mike Bayer]

not really.  Are you leaving a long-running transaction open and not 
closing it (or rolling back / committing) ?




On 06/14/2016 07:06 PM, Jeffrey Yunes wrote:

Sorry, no dice!

I switched to pymysql.

I'm connecting via 127.0.0.1, so I don't think it's a network issue.

Pretty much the same error...

Any thoughts?

2016-06-14 15:45:20,889 INFO sqlalchemy.engine.base.Engine SHOW
VARIABLES LIKE 'sql_mode'
...
2016-06-14 15:56:42,530 INFO sqlalchemy.pool.QueuePool Invalidate
connection 
(reason: OperationalError:(2013, 'Lost connection to MySQL server during
query'))
Traceback (most recent call last):
  File "/home/user/projects/my_project/bin/test.py", line 533, in 
main()
  File "/home/user/projects/my_project/my_package/x/y.py", line 97, in func
for a, b in query:
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
line 2761, in __iter__
return self._execute_and_instances(context)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
line 2776, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 914, in execute
return meth(self, multiparams, params)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
line 323, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1010, in _execute_clauseelement
compiled_sql, distilled_params
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1146, in _execute_context
context)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1341, in _handle_dbapi_exception
exc_info
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py",
line 202, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1139, in _execute_context
context)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py",
line 450, in do_execute
cursor.execute(statement, parameters)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/cursors.py",
line 161, in execute
result = self._query(query)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/cursors.py",
line 317, in _query
conn.query(q)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py",
line 835, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py",
line 1019, in _read_query_result
result.read()
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py",
line 1302, in read
first_packet = self.connection._read_packet()
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py",
line 961, in _read_packet
packet_header = self._read_bytes(4)
  File
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py",
line 998, in _read_bytes
2013, "Lost connection to MySQL server during query")
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013,
'Lost connection to MySQL server during query') [SQL: u'SELECT ...]

Process finished with exit code 1

On 14 Jun 2016, at 15:15, Mike Bayer wrote:


Well I'd get off of OurSQL to start with since it is unmaintained for
years now.   The "gone away" error doesn't always mean the connection
was actually dropped, in some old school situations it just means the
client got out of sync with the MySQL protocol.

(  waits  )

still broken?  next issue would be how your MySQL/MariaDB is set up.
Like is this using HAProxy or something like that.   If not, then
looking at your network would be next.

First try using either mysqlclient or pymysql, those are the two
clients to use right now.



On 06/14/2016 05:27 PM, Jeffrey Yunes wrote:

Hi all,
After successful queries and a 10 minute wait, I'm getting the popular
"MySQL server has gone away." I have a single-threaded app and my
pool_recycle is way less than my wait_timeout.

Do I need to create a new session after the pool recycles? I'd love to
know which part of the docs I've missed.

I'm using oursql, so my problem sounds related to:

Re: [sqlalchemy] MySQL server has gone away

[Jeffrey Yunes]

Sorry, no dice!

I switched to pymysql.

I'm connecting via 127.0.0.1, so I don't think it's a network issue.

Pretty much the same error...

Any thoughts?

2016-06-14 15:45:20,889 INFO sqlalchemy.engine.base.Engine SHOW 
VARIABLES LIKE 'sql_mode'

...
2016-06-14 15:56:42,530 INFO sqlalchemy.pool.QueuePool Invalidate 
connection  
(reason: OperationalError:(2013, 'Lost connection to MySQL server during 
query'))

Traceback (most recent call last):
  File "/home/user/projects/my_project/bin/test.py", line 533, in 


main()
  File "/home/user/projects/my_project/my_package/x/y.py", line 97, in 
func

for a, b in query:
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", 
line 2761, in __iter__

return self._execute_and_instances(context)
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", 
line 2776, in _execute_and_instances

result = conn.execute(querycontext.statement, self._params)
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", 
line 914, in execute

return meth(self, multiparams, params)
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", 
line 323, in _execute_on_connection

return connection._execute_clauseelement(self, multiparams, params)
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", 
line 1010, in _execute_clauseelement

compiled_sql, distilled_params
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", 
line 1146, in _execute_context

context)
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", 
line 1341, in _handle_dbapi_exception

exc_info
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", 
line 202, in raise_from_cause

reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", 
line 1139, in _execute_context

context)
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", 
line 450, in do_execute

cursor.execute(statement, parameters)
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/cursors.py", 
line 161, in execute

result = self._query(query)
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/cursors.py", 
line 317, in _query

conn.query(q)
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py", 
line 835, in query
self._affected_rows = 
self._read_query_result(unbuffered=unbuffered)
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py", 
line 1019, in _read_query_result

result.read()
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py", 
line 1302, in read

first_packet = self.connection._read_packet()
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py", 
line 961, in _read_packet

packet_header = self._read_bytes(4)
  File 
"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/pymysql/connections.py", 
line 998, in _read_bytes

2013, "Lost connection to MySQL server during query")
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 
'Lost connection to MySQL server during query') [SQL: u'SELECT ...]


Process finished with exit code 1

On 14 Jun 2016, at 15:15, Mike Bayer wrote:

Well I'd get off of OurSQL to start with since it is unmaintained for 
years now.   The "gone away" error doesn't always mean the connection 
was actually dropped, in some old school situations it just means the 
client got out of sync with the MySQL protocol.


(  waits  )

still broken?  next issue would be how your MySQL/MariaDB is set up. 
Like is this using HAProxy or something like that.   If not, then 
looking at your network would be next.


First try using either mysqlclient or pymysql, those are the two 
clients to use right now.




On 06/14/2016 05:27 PM, Jeffrey Yunes wrote:

Hi all,
After successful queries and a 10 minute wait, I'm getting the 
popular

"MySQL server has gone away." I have a single-threaded app and my
pool_recycle is way less than my wait_timeout.

Do I need to create a new session after the pool recycles? I'd love 
to

know which part of the docs I've missed.

I'm using oursql, so my problem sounds related to:
https://groups.google.com/forum/#!searchin/sqlalchemy/oursql$20gone$20away/sqlalchemy/K9Pk2pXbLgQ/XWzHnp5Ev4EJ

Any suggestions?

Thanks!

Here are the details:

|
importsqlalchemy

Re: [sqlalchemy] MySQL server has gone away

[Mike Bayer]

Well I'd get off of OurSQL to start with since it is unmaintained for 
years now.   The "gone away" error doesn't always mean the connection 
was actually dropped, in some old school situations it just means the 
client got out of sync with the MySQL protocol.


(  waits  )

still broken?  next issue would be how your MySQL/MariaDB is set up. 
Like is this using HAProxy or something like that.   If not, then 
looking at your network would be next.


First try using either mysqlclient or pymysql, those are the two clients 
to use right now.




On 06/14/2016 05:27 PM, Jeffrey Yunes wrote:

Hi all,
After successful queries and a 10 minute wait, I'm getting the popular
"MySQL server has gone away." I have a single-threaded app and my
pool_recycle is way less than my wait_timeout.

Do I need to create a new session after the pool recycles? I'd love to
know which part of the docs I've missed.

I'm using oursql, so my problem sounds related to:
https://groups.google.com/forum/#!searchin/sqlalchemy/oursql$20gone$20away/sqlalchemy/K9Pk2pXbLgQ/XWzHnp5Ev4EJ

Any suggestions?

Thanks!

Here are the details:

|
importsqlalchemy

sqlalchemy.__version__

'1.0.13'
|


# mysql --version

mysql Ver 15.1 Distrib 5.5.49-MariaDB, for debian-linux-gnu (x86_64)
using readline 5.2



aborted_clients is increasing, wait_timeout is set to 28800.

mysql error log:

Jun 14 13:36:50 hostname mysqld: 160614 13:36:50 [Warning] Aborted
connection 170 to db: 'db' user: 'user' host: 'localhost' (Unknown
error)


initialization code:
|
definit():
  globalalchemy_session
  ...
  engine
=create_engine("mysql+oursql://%s:%s@%s:%d/%s"%(user,passwd,host,port,db),
 pool_recycle=300,
 echo=True,
 echo_pool=True)
  metadata =MetaData()
  metadata.reflect(engine)
  alchemy_base =automap_base(metadata=metadata)
  alchemy_base.prepare(engine,reflect=True)
  alchemy_session =Session(engine)
|

Stack trace + echo:

2016-06-14 13:26:15,546 INFO sqlalchemy.engine.base.Engine SHOW
VARIABLES LIKE 'sql_mode'
...
2016-06-14 13:26:17,789 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
...
2016-06-14 13:37:36,986 INFO sqlalchemy.pool.QueuePool Invalidate
connection  (reason:
OperationalError:(2006, 'MySQL server has gone away', None))
2016-06-14 13:37:36,986 ERROR sqlalchemy.pool.QueuePool Exception
closing connection 
Traceback (most recent call last):
File

"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/pool.py",
line 290, in _close_connection
self._dialect.do_close(connection)
File

"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py",
line 426, in do_close
dbapi_connection.close()
File "oursqlx/connection.pyx", line 170, in oursql.Connection.close
(oursqlx/oursql.c:6764)
File "oursqlx/connection.pyx", line 215, in
oursql.Connection.rollback (oursqlx/oursql.c:7299)
File "oursqlx/connection.pyx", line 183, in
oursql.Connection._raise_error (oursqlx/oursql.c:6984)
OperationalError: (2006, 'MySQL server has gone away', None)
Traceback (most recent call last):
File "/home/user/projects/my_project/bin/test.py", line 533, in 
main()
...
File "/home/user/projects/my_project/pkg/x/y.py", line 97, in func
for a, b in query:
File

"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
line 2761, in __iter__
return self._execute_and_instances(context)
File

"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
line 2776, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File

"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 914, in execute
return meth(self, multiparams, params)
File

"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
line 323, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File

"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1010, in _execute_clauseelement
compiled_sql, distilled_params
File

"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1146, in _execute_context
context)
File

"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1341, in _handle_dbapi_exception
exc_info
File

"/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py",
line 202, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File

[sqlalchemy] MySQL server has gone away

[Jeffrey Yunes]

Hi all,
After successful queries and a 10 minute wait, I'm getting the popular 
"MySQL server has gone away." I have a single-threaded app and my 
pool_recycle is way less than my wait_timeout.

Do I need to create a new session after the pool recycles? I'd love to know 
which part of the docs I've missed.

I'm using oursql, so my problem sounds related to: 
https://groups.google.com/forum/#!searchin/sqlalchemy/oursql$20gone$20away/sqlalchemy/K9Pk2pXbLgQ/XWzHnp5Ev4EJ

Any suggestions?

Thanks!

Here are the details:

import sqlalchemy
>>> sqlalchemy.__version__
'1.0.13'


# mysql --version
>
mysql Ver 15.1 Distrib 5.5.49-MariaDB, for debian-linux-gnu (x86_64) using 
> readline 5.2
>
 

aborted_clients is increasing, wait_timeout is set to 28800.

mysql error log:

> Jun 14 13:36:50 hostname mysqld: 160614 13:36:50 [Warning] Aborted 
> connection 170 to db: 'db' user: 'user' host: 'localhost' (Unknown error)
>

initialization code:
def init():
  global alchemy_session
  ...
  engine = create_engine("mysql+oursql://%s:%s@%s:%d/%s" % (user, passwd, 
host, port, db),
 pool_recycle=300,
 echo=True,
 echo_pool=True)
  metadata = MetaData()
  metadata.reflect(engine)
  alchemy_base = automap_base(metadata=metadata)
  alchemy_base.prepare(engine, reflect=True)
  alchemy_session = Session(engine)

Stack trace + echo:

> 2016-06-14 13:26:15,546 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES 
> LIKE 'sql_mode'
> ...
> 2016-06-14 13:26:17,789 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
> ...
> 2016-06-14 13:37:36,986 INFO sqlalchemy.pool.QueuePool Invalidate 
> connection  (reason: 
> OperationalError:(2006, 'MySQL server has gone away', None))
> 2016-06-14 13:37:36,986 ERROR sqlalchemy.pool.QueuePool Exception closing 
> connection 
> Traceback (most recent call last):
> File 
> "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/pool.py",
>  
> line 290, in _close_connection
> self._dialect.do_close(connection)
> File 
> "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py",
>  
> line 426, in do_close
> dbapi_connection.close()
> File "oursqlx/connection.pyx", line 170, in oursql.Connection.close 
> (oursqlx/oursql.c:6764)
> File "oursqlx/connection.pyx", line 215, in oursql.Connection.rollback 
> (oursqlx/oursql.c:7299)
> File "oursqlx/connection.pyx", line 183, in oursql.Connection._raise_error 
> (oursqlx/oursql.c:6984)
> OperationalError: (2006, 'MySQL server has gone away', None)
> Traceback (most recent call last):
> File "/home/user/projects/my_project/bin/test.py", line 533, in 
> main()
> ...
> File "/home/user/projects/my_project/pkg/x/y.py", line 97, in func
> for a, b in query:
> File 
> "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
>  
> line 2761, in __iter__
> return self._execute_and_instances(context)
> File 
> "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
>  
> line 2776, in _execute_and_instances
> result = conn.execute(querycontext.statement, self._params)
> File 
> "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>  
> line 914, in execute
> return meth(self, multiparams, params)
> File 
> "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
>  
> line 323, in _execute_on_connection
> return connection._execute_clauseelement(self, multiparams, params)
> File 
> "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>  
> line 1010, in _execute_clauseelement
> compiled_sql, distilled_params
> File 
> "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>  
> line 1146, in _execute_context
> context)
> File 
> "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>  
> line 1341, in _handle_dbapi_exception
> exc_info
> File 
> "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py",
>  
> line 202, in raise_from_cause
> reraise(type(exception), exception, tb=exc_tb, cause=cause)
> File 
> "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>  
> line 1139, in _execute_context
> context)
> File 
> "/home/user/projects/my_project/venv/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/oursql.py",
>  
> line 77, in do_execute
> cursor.execute(statement, parameters)
> File "oursqlx/cursor.pyx", line 120, in oursql.Cursor.execute 
> (oursqlx/oursql.c:20439)
> File "oursqlx/cursor.pyx", line 111, in oursql.Cursor.execute 
> (oursqlx/oursql.c:20301)
> File "oursqlx/statement.pyx", line 157, in oursql._Statement.prepare 
> (oursqlx/oursql.c:10423)
> File "oursqlx/statement.pyx", line 127, in oursql._Statement._raise_error 

Re: [sqlalchemy] Question about eagerloading and depth of inheritance with respect to polymorphic mappings

[Mike Bayer]

On 06/14/2016 11:28 AM, Ken Linehan wrote:

Hello,

I'm working with a class inheritance structure which can be illustrated
by this example:
|
class Entity(object):

  def __init__(self,
entity_type=EntityTypeEntity,
id=None):

  self.entity_type = entity_type
  self.id = id

class Person(Entity):

  def __init__(self,
entity_class=None,
entity_type=EntityTypePerson,
id=None,
name_first=None,
name_last=None,
name_middle=None,
name_prefix=None,
name_suffix=None):

  super(Person, self).__init__(entity_type,
   id)

  self.entity_class = entity_class
  self.name_first = name_first
  self.name_last = name_last
  self.name_middle = name_middle
  self.name_prefix = name_prefix
  self.name_suffix = name_suffix

class SupporterPerson(Person):

  def __init__(self,
entity_class=EntityClassSupporterPerson,
entity_type=EntityTypePerson,
id=None,
name_first=None,
name_last=None,
name_middle=None,
name_prefix=None,
name_suffix=None,
relationship=None):

  super(SupporterPerson, self).__init__(entity_class,
entity_type,
id,
name_first,
name_last,
name_middle,
name_prefix,
name_suffix)

  self.relationship = relationship
|

My current mappings look like this:
|
# Mappers
self.entity_table_mapper = mapper(
  Entity,
  self.entities_table,
  polymorphic_on=self.entities_table.c.entity_type,
  polymorphic_identity=EntityTypeEntity,
  with_polymorphic='*',
  properties={
'entity_type': self.entities_table.c.entity_type,
'id': self.entities_table.c.id,
  }
)

self.persons_table_mapper = mapper(
  Person,
  self.persons_table,
  polymorphic_on=self.persons_table.c.entity_class,
  polymorphic_identity=EntityTypePerson,
  with_polymorphic='*',
  inherits=Entity,
)

self.supporter_persons_table_mapper = mapper(
  SupporterPerson,
  self.supporter_persons_table,
  polymorphic_identity=EntityClassSupporterPerson,
  with_polymorphic='*',
  inherits=Person,
)
|

I am able to instantiate a SupporterPerson() and successfully store all
attributes correctly in their respective tables. So the mapping seems to
work well. However...


what's likely happening is that you're attempting to use two levels of 
polymorphic_on which isn't supported right now.  a load of Entity will 
only consider those classes it can identify via entity_type.


There's a fairly elaborate proof of concept for this feature at 
https://bitbucket.org/zzzeek/sqlalchemy/issues/2555/cascading-polymorphic-ons 
, I've just re-reviewed it and it should still work.







In the broader context of my application, a query to load Causes() (
sketched out below ) will eagerload a list of that causes supporters.
 When supporters are eagerloaded, the supporter is reliably loaded to
the level of Person() however the attributes specific to
SupporterPerson() are not loaded.  In other words, the attributes
specific to the youngest child in my class inheritance, structure are
NOT loaded by the eagerload.

|
class Cause(object):

  def __init__(self,
id=None
supporters=None):

  self.id = id
  self.supporters = [] if supporters is None else None
|

So my question is, Is there a problem with my mapping? Or is this depth
of inheritance not supported by the eagerload? Or perhaps there is
something else I'm missing altogether ( unrelated to the eagerload ) ?
Any advice or assistance would be greatly appreciated. Sorry if I'm
missing something that is already clearly stated in the documentation.

Best,

Ken

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


--
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] Question about eagerloading and depth of inheritance with respect to polymorphic mappings

[Ken Linehan]

Hello,

I'm working with a class inheritance structure which can be illustrated by 
this example:
class Entity(object):

  def __init__(self,
entity_type=EntityTypeEntity,
id=None):

  self.entity_type = entity_type  
  self.id = id

class Person(Entity):

  def __init__(self,
entity_class=None,
entity_type=EntityTypePerson,
id=None,
name_first=None,
name_last=None,
name_middle=None,
name_prefix=None,
name_suffix=None):

  super(Person, self).__init__(entity_type,
   id)

  self.entity_class = entity_class
  self.name_first = name_first
  self.name_last = name_last
  self.name_middle = name_middle
  self.name_prefix = name_prefix
  self.name_suffix = name_suffix

class SupporterPerson(Person):
  
  def __init__(self,
entity_class=EntityClassSupporterPerson,
entity_type=EntityTypePerson,
id=None,
name_first=None,
name_last=None,
name_middle=None,
name_prefix=None,
name_suffix=None,
relationship=None):

  super(SupporterPerson, self).__init__(entity_class,
entity_type,
id,
name_first,
name_last,
name_middle,
name_prefix,
name_suffix)

  self.relationship = relationship

My current mappings look like this:
# Mappers
self.entity_table_mapper = mapper(
  Entity,
  self.entities_table,
  polymorphic_on=self.entities_table.c.entity_type,
  polymorphic_identity=EntityTypeEntity,
  with_polymorphic='*',
  properties={
'entity_type': self.entities_table.c.entity_type,
'id': self.entities_table.c.id,
  }
)

self.persons_table_mapper = mapper(
  Person,
  self.persons_table,
  polymorphic_on=self.persons_table.c.entity_class,
  polymorphic_identity=EntityTypePerson,
  with_polymorphic='*',
  inherits=Entity,
)

self.supporter_persons_table_mapper = mapper(
  SupporterPerson,
  self.supporter_persons_table,
  polymorphic_identity=EntityClassSupporterPerson,
  with_polymorphic='*',
  inherits=Person,
)

I am able to instantiate a SupporterPerson() and successfully store all 
attributes correctly in their respective tables. So the mapping seems to 
work well. However...

In the broader context of my application, a query to load Causes() ( 
sketched out below ) will eagerload a list of that causes supporters.  When 
supporters are eagerloaded, the supporter is reliably loaded to the level 
of Person() however the attributes specific to SupporterPerson() are not 
loaded.  In other words, the attributes specific to the youngest child in 
my class inheritance, structure are NOT loaded by the eagerload.

class Cause(object):

  def __init__(self,
id=None
supporters=None):

  self.id = id
  self.supporters = [] if supporters is None else None

So my question is, Is there a problem with my mapping? Or is this depth of 
inheritance not supported by the eagerload? Or perhaps there is something 
else I'm missing altogether ( unrelated to the eagerload ) ? Any advice or 
assistance would be greatly appreciated. Sorry if I'm missing something 
that is already clearly stated in the documentation.

Best,

Ken

-- 
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] nullable dates

[Marco Correia]

Thanks. It turned out that I was sending an empty string for date (instead 
of null), and that was being translated as -00-00 (your script works 
except I was using mysql+pysql). I didn't saw it since google developer 
tools logs, wrongly, that a null is being sent.

Thanks a lot for the help.

Marco

On Tuesday, June 14, 2016 at 1:46:19 PM UTC+1, Mike Bayer wrote:
>
> Let's do an MCVE: 
>
> from sqlalchemy import * 
> from sqlalchemy.orm import * 
> from sqlalchemy.ext.declarative import declarative_base 
>
> Base = declarative_base() 
>
>
> class A(Base): 
>  __tablename__ = 'a' 
>  id = Column(Integer, primary_key=True) 
>  end_date = Column(Date(),nullable=True) 
>
> e = create_engine("mysql://scott:tiger@localhost/test", echo=True) 
> Base.metadata.drop_all(e) 
> Base.metadata.create_all(e) 
>
>
> s = Session(e) 
> s.add(A(end_date=None)) 
> s.commit() 
>
> print s.query(A.end_date).all() 
>
>
> on the Python side, the output is (after it checks for the table etc): 
>
> CREATE TABLE a ( 
> id INTEGER NOT NULL AUTO_INCREMENT, 
> end_date DATE, 
> PRIMARY KEY (id) 
> ) 
>
>
> 2016-06-14 08:39:17,680 INFO sqlalchemy.engine.base.Engine () 
> 2016-06-14 08:39:17,699 INFO sqlalchemy.engine.base.Engine COMMIT 
> 2016-06-14 08:39:17,701 INFO sqlalchemy.engine.base.Engine BEGIN 
> (implicit) 
> 2016-06-14 08:39:17,702 INFO sqlalchemy.engine.base.Engine INSERT INTO a 
> (end_date) VALUES (%s) 
> 2016-06-14 08:39:17,702 INFO sqlalchemy.engine.base.Engine (None,) 
> 2016-06-14 08:39:17,703 INFO sqlalchemy.engine.base.Engine COMMIT 
> 2016-06-14 08:39:17,708 INFO sqlalchemy.engine.base.Engine BEGIN 
> (implicit) 
> 2016-06-14 08:39:17,708 INFO sqlalchemy.engine.base.Engine SELECT 
> a.end_date AS a_end_date 
> FROM a 
> 2016-06-14 08:39:17,709 INFO sqlalchemy.engine.base.Engine () 
> [(None,)] 
>
> it's not actually possible to load a date of -00-00 in Python, so 
> making sure on the MySQL side: 
>
> [classic@photon2 sqlalchemy]$ mysql -u root 
> Welcome to the MariaDB monitor.  Commands end with ; or \g. 
> Your MariaDB connection id is 3 
> Server version: 10.1.14-MariaDB MariaDB Server 
>
> Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. 
>
> Type 'help;' or '\h' for help. Type '\c' to clear the current input 
> statement. 
>
> MariaDB [(none)]> use test; 
> Reading table information for completion of table and column names 
> You can turn off this feature to get a quicker startup with -A 
>
> Database changed 
> MariaDB [test]> select * from a; 
> ++--+ 
> | id | end_date | 
> ++--+ 
> |  1 | NULL | 
> ++--+ 
> 1 row in set (0.00 sec) 
>
>
>
> so you'd want to check is: 
>
> 1. does this test case do something different for you ?   Can you modify 
> it to show your result? 
>
> 2. what version of MySQL / MariaDB is this? 
>
> 3. what kinds of settings do you have for SQL_MODE , storage engine, 
> etc.   Is this definitely the type "DATE" ?  (TIMESTAMP acts more 
> unusually for example) 
>
> 4. what database driver ?  (what version?) 
>
> 5. etc. etc. 
>
>
> I can tell you right off that SQLAlchemy doesn't do anything with MySQL 
> dates, they are passed through straight to the driver. 
>
> Looking over stackoverflow the main reason for this -00-00 thing is 
> people inserting invalid dates as strings.   The MySQL python drivers 
> should not be able to do that. 
>
>
>
>
>
>
>
>
> On 06/14/2016 07:20 AM, Marco Correia wrote: 
> > Hi, 
> > 
> > I have a date column which is optional, therefore I created it like 
> this: 
> > 
> > end_date = Column(Date(),nullable=True) 
> > 
> > Apparently, if I do not specify the date, the database (mysql) stores 
> > "-00-00". This is a problem later, when I do queries using clauses 
> > like the following: 
> > 
> > or_(Job.end_date.is_(None),Job.end_date>=date) 
> > 
> > I find it a bit odd that when using sqlalchemy something that gets 
> > stored as None is retrieved as -00-00, so I guess I'm making some 
> > mistake. 
> > 
> > Trying to find a solution to this problem led me to do 
> > 
> > end_date = Column(Date(),nullable=True,server_default=text("NULL")) 
> > 
> > but it makes no difference. 
> > 
> > Can someone help? Thanks! 
> > 
> > Marco 
> > 
> > -- 
> > 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 https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

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

Re: [sqlalchemy] How to make escaped column names in sqlalchemy

[Mike Bayer]

a column name isn't "escaped", it's quoted.   The quoting logic in a 
compiler goes through compiler.preparer.quote(column.name), if the name 
needs to be quoted it will come back as such.




On 06/14/2016 05:16 AM, Вадим Гухман wrote:

Hi, I'm trying to create a small library with utils and compilers for
sqlalchemy that extends sqlalchemy default functionality.
Right now it has two compilers. One for Date and one for merge-like
statement.
I have a problem with on duplicate key update statement in mysql that
I've summarized in this issue.

https://github.com/purpleP/sqlalchemy-utils/issues/1

The key problem here is that I don't know which part of sqlalchemy
should I use to produce parts resulting sql (like escaped column names
in this case). So although it's all already implemented somewhere in
sqlalchemy I'm not sure how to use that. I've tried to dig into
sqlalchemy code, but it uses too many levels of indirection for me to
understand quickly, so I've decided that it's better to ask here.

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


--
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] nullable dates

[Mike Bayer]

Let's do an MCVE:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
end_date = Column(Date(),nullable=True)

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)


s = Session(e)
s.add(A(end_date=None))
s.commit()

print s.query(A.end_date).all()


on the Python side, the output is (after it checks for the table etc):

CREATE TABLE a (
id INTEGER NOT NULL AUTO_INCREMENT,
end_date DATE,
PRIMARY KEY (id)
)


2016-06-14 08:39:17,680 INFO sqlalchemy.engine.base.Engine ()
2016-06-14 08:39:17,699 INFO sqlalchemy.engine.base.Engine COMMIT
2016-06-14 08:39:17,701 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-06-14 08:39:17,702 INFO sqlalchemy.engine.base.Engine INSERT INTO a 
(end_date) VALUES (%s)

2016-06-14 08:39:17,702 INFO sqlalchemy.engine.base.Engine (None,)
2016-06-14 08:39:17,703 INFO sqlalchemy.engine.base.Engine COMMIT
2016-06-14 08:39:17,708 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-06-14 08:39:17,708 INFO sqlalchemy.engine.base.Engine SELECT 
a.end_date AS a_end_date

FROM a
2016-06-14 08:39:17,709 INFO sqlalchemy.engine.base.Engine ()
[(None,)]

it's not actually possible to load a date of -00-00 in Python, so 
making sure on the MySQL side:


[classic@photon2 sqlalchemy]$ mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input 
statement.


MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> select * from a;
++--+
| id | end_date |
++--+
|  1 | NULL |
++--+
1 row in set (0.00 sec)



so you'd want to check is:

1. does this test case do something different for you ?   Can you modify 
it to show your result?


2. what version of MySQL / MariaDB is this?

3. what kinds of settings do you have for SQL_MODE , storage engine, 
etc.   Is this definitely the type "DATE" ?  (TIMESTAMP acts more 
unusually for example)


4. what database driver ?  (what version?)

5. etc. etc.


I can tell you right off that SQLAlchemy doesn't do anything with MySQL 
dates, they are passed through straight to the driver.


Looking over stackoverflow the main reason for this -00-00 thing is 
people inserting invalid dates as strings.   The MySQL python drivers 
should not be able to do that.









On 06/14/2016 07:20 AM, Marco Correia wrote:

Hi,

I have a date column which is optional, therefore I created it like this:

end_date = Column(Date(),nullable=True)

Apparently, if I do not specify the date, the database (mysql) stores
"-00-00". This is a problem later, when I do queries using clauses
like the following:

or_(Job.end_date.is_(None),Job.end_date>=date)

I find it a bit odd that when using sqlalchemy something that gets
stored as None is retrieved as -00-00, so I guess I'm making some
mistake.

Trying to find a solution to this problem led me to do

end_date = Column(Date(),nullable=True,server_default=text("NULL"))

but it makes no difference.

Can someone help? Thanks!

Marco

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


--
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] Bug with join?

[Drachenfels]

>
> > Article has a foreign key to Video and Video has a foreign key to Tag so 
> these can be joined either way. 
>

This is a bit information I missed to be honest. I forgot that while 
Article has direct join to Video, Video can be joined to Article via Tag. I 
checked my relations so many times and I didn't notice it.
 

> If you want joins to work with relationships alone you should consider 
> those using 
> inspect(Class).relationships to find the relationships you'd like to use 
> for each linkage. 
>
 
Lucky for me, my code is not that mad. I only wrap some things to reduce 
amount of repetition, however due to wrapper work semi-autonomously not 
every request has exactly same join order. And because order of joins in 
this case fixed/caused error I was a bit confused.

Thanks a lot of shedding light on the topic. Now I can sleep lightly 
knowing that mix fix is actually fix for my own mistake.

Cheers.

-- 
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] nullable dates

[Marco Correia]

Hi,

I have a date column which is optional, therefore I created it like this:

end_date = Column(Date(),nullable=True)

Apparently, if I do not specify the date, the database (mysql) stores 
"-00-00". This is a problem later, when I do queries using clauses like 
the following:

or_(Job.end_date.is_(None),Job.end_date>=date)

I find it a bit odd that when using sqlalchemy something that gets stored 
as None is retrieved as -00-00, so I guess I'm making some mistake. 

Trying to find a solution to this problem led me to do

end_date = Column(Date(),nullable=True,server_default=text("NULL"))

but it makes no difference.

Can someone help? Thanks!

Marco

-- 
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 to make escaped column names in sqlalchemy

[Вадим Гухман]

Hi, I'm trying to create a small library with utils and compilers for 
sqlalchemy that extends sqlalchemy default functionality. 
Right now it has two compilers. One for Date and one for merge-like 
statement. 
I have a problem with on duplicate key update statement in mysql that I've 
summarized in this issue. 

https://github.com/purpleP/sqlalchemy-utils/issues/1 

The key problem here is that I don't know which part of sqlalchemy should I 
use to produce parts resulting sql (like escaped column names in this 
case). So although it's all already implemented somewhere in sqlalchemy I'm 
not sure how to use that. I've tried to dig into sqlalchemy code, but it 
uses too many levels of indirection for me to understand quickly, so I've 
decided that it's better to ask here. 

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