Re: [sqlalchemy] Issues with Type conversions with raw queries
On Mon, Jun 4, 2018 at 5:28 PM, Fokko Driesprong wrote:
> Hi All,
>
> I'm working on making PyHive compatible with SQLAlchemy 1.2.8:
> https://github.com/Fokko/PyHive/tree/fd-fix-tests
>
> Now I run into some problems which is I can't figure out. Hopefully there is
> anyone who has more experience with this than me. The dbapi_type_map has
> been deprecated in 1.2 and now I'm unable to parse the types with raw
> queries. What does work:
>
> tbl = Table('one_row_complex', MetaData(bind=engine), autoload=True)
> rows = tbl.select().execute().fetchone()
> self.assertEqual(list(rows[0]), _ONE_ROW_COMPLEX_CONTENTS)
>
> What doesn't work:
>
> row = connection.execute('SELECT * FROM one_row_complex').fetchone()
> self.assertEqual(row, _ONE_ROW_COMPLEX_CONTENTS)
the dbape_type_map was a hack that was only used by the Oracle dialect
due to the really awkward way cx_Oracle acted with LOB objects. It
was never used for things like date conversions and decimals - if you
use SQLite with raw SQL you will get back strings for dates and floats
for decimals too. SQLAlchemy only applies typing behavior when you
specify it with table metadata. In the above case, you can use
text("SELECT * FROM one_row_complex").columns(*tbl.c) to apply the
types, assuming tbl.c's columns are in the same order as they would be
from "*".
There's ways to re-introduce the ability to inject types into the
result set but this would have to be re-proposed into the new
architecture, as this was never a real "feature".
>
> I found out that the following routine isn't invoked when running the raw
> sql:
> https://github.com/Fokko/PyHive/blob/master/pyhive/sqlalchemy_hive.py#L229-L258
>
> Does anyone know how to convert the datetime and the decimal when running
> the raw SQL? I when through all the documentation and tried a lot (as you
> might see in the pull-request), but I couldn't get it to work.
>
> Please let me know,
>
> Kind regards, Fokko
>
> --
> 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 -
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] Issues with Type conversions with raw queries
Hi All,
I'm working on making PyHive compatible with SQLAlchemy
1.2.8: https://github.com/Fokko/PyHive/tree/fd-fix-tests
Now I run into some problems which is I can't figure out. Hopefully there
is anyone who has more experience with this than me. The dbapi_type_map has
been deprecated in 1.2 and now I'm unable to parse the types with raw
queries. What does work:
tbl = Table('one_row_complex', MetaData(bind=engine), autoload=True)
rows = tbl.select().execute().fetchone()
self.assertEqual(list(rows[0]), _ONE_ROW_COMPLEX_CONTENTS)
What doesn't work:
row = connection.execute('SELECT * FROM one_row_complex').fetchone()
self.assertEqual(row, _ONE_ROW_COMPLEX_CONTENTS)
I found out that the following routine isn't invoked when running the raw
sql:
https://github.com/Fokko/PyHive/blob/master/pyhive/sqlalchemy_hive.py#L229-L258
Does anyone know how to convert the datetime and the decimal when running
the raw SQL? I when through all the documentation and tried a lot (as you
might see in the pull-request), but I couldn't get it to work.
Please let me know,
Kind regards, Fokko
--
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: SQLAlchemy keeps dropping sessions
In theory what you're claiming should be fine... but there's always a chance that what you *think* you're doing isn't really what you're doing. I suggest creating a tiny one-file flask app that mimics your behavior and reproduces the result -- then share it here. Your example on StackOverflow doesn't show how the sessions/request/app is setup or anything is invoked. There's a good chance you're doing everything right and this is a weird RDS specific thing that Mike hasn't seen before. But there's also a decent chance you've done something wrong or have an anti-pattern somewhere that is exacerbating this issue. Over the years a handful of people have posted random problems, and it just ends up being a small misuse in a configuration or setup creating a cascade of hard-to-diagnose errors down the line. -- 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: I tried many different ways to get the sum of the differences of two columns in a table with sqlalchemy, but always end up with errors:
Further information:
If I remove total_seconds() call, using whens={'SUCCESS': self.model.end_time
- self.model.start_time}, then I'll get the following error:
- File
"/usr/local/lib/python2.7/site-packages/flask_admin/contrib/sqla/view.py",
line *1038*, in get_list
query = query.all()
- File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
line *2703*, in all
return list(self)
- File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",
line *90*, in instances
util.raise_from_cause(err)
- File "/usr/local/lib/python2.7/site-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/site-packages/sqlalchemy/orm/loading.py",
line *78*, in instances
for row in fetch]
- File
"/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/sqltypes.py",
line *1717*, in process
return value - epoch
TypeError: unsupported operand type(s) for -: 'Decimal' and
'datetime.datetime'
On Monday, June 4, 2018 at 12:00:30 PM UTC-4, Hongqi Jia wrote:
>
> Here is how I define the query:
>
> _query = self.session.query(self.model.type,
> func.sum(case(value=self.model.final_status, whens={'SUCCESS':
> (self.model.end_time - self.model.start_time).total_seconds()}, else_=None)
>
>
> Here the end_time and start_time are columns of DateTime type.
>
>
> Here is the error I got. Desperately need help here, and really appreciate
> any suggestions!
>
>
> Error message:
>
>
>
>- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line *1997*,
> in __call__
>
>return self.wsgi_app(environ, start_response)
>
>- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line
>*1985*, in wsgi_app
>
>response = self.handle_exception(e)
>
>- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line
>*1540*, in handle_exception
>
>reraise(exc_type, exc_value, tb)
>
>- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line
>*1982*, in wsgi_app
>
>response = self.full_dispatch_request()
>
>- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line
>*1614*, in full_dispatch_request
>
>rv = self.handle_user_exception(e)
>
>- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line
>*1517*, in handle_user_exception
>
>reraise(exc_type, exc_value, tb)
>
>- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line
>*1612*, in full_dispatch_request
>
>rv = self.dispatch_request()
>
>- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line
>*1598*, in dispatch_request
>
>return self.view_functions[rule.endpoint](**req.view_args)
>
>- File "/usr/local/lib/python2.7/site-packages/flask_admin/base.py",
>line *69*, in inner
>
>return self._run_view(f, *args, **kwargs)
>
>- File "/usr/local/lib/python2.7/site-packages/flask_admin/base.py",
>line *368*, in _run_view
>
>return fn(self, *args, **kwargs)
>
>- File
>"/usr/local/lib/python2.7/site-packages/flask_admin/model/base.py",
>line *1882*, in index_view
>
>view_args.search, view_args.filters, page_size=page_size)
>
>- File
>"/usr/local/lib/python2.7/site-packages/flask_admin/contrib/sqla/view.py",
>line *997*, in get_list
>
>query = self.get_query()
>
>- File "/app/src/main/python/hermes_reporter/admin.py", line *344*, in
>get_query
>
>whens={'SUCCESS': (_model.updated_on - _model.created_on).total_seconds()},
>
>- File
>"/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
>line *682*, in __getattr__
>
>key)
>
>
> AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object
> has an attribute 'total_seconds'
>
>
--
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] I tried many different ways to get the sum of the differences of two columns in a table with sqlalchemy, but always end up with errors:
Here is how I define the query:
_query = self.session.query(self.model.type,
func.sum(case(value=self.model.final_status, whens={'SUCCESS':
(self.model.end_time - self.model.start_time).total_seconds()}, else_=None)
Here the end_time and start_time are columns of DateTime type.
Here is the error I got. Desperately need help here, and really appreciate any
suggestions!
Error message:
- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line *1997*,
in __call__
return self.wsgi_app(environ, start_response)
- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line *1985*,
in wsgi_app
response = self.handle_exception(e)
- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line *1540*,
in handle_exception
reraise(exc_type, exc_value, tb)
- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line *1982*,
in wsgi_app
response = self.full_dispatch_request()
- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line *1614*,
in full_dispatch_request
rv = self.handle_user_exception(e)
- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line *1517*,
in handle_user_exception
reraise(exc_type, exc_value, tb)
- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line *1612*,
in full_dispatch_request
rv = self.dispatch_request()
- File "/usr/local/lib/python2.7/site-packages/flask/app.py", line *1598*,
in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
- File "/usr/local/lib/python2.7/site-packages/flask_admin/base.py",
line *69*, in inner
return self._run_view(f, *args, **kwargs)
- File "/usr/local/lib/python2.7/site-packages/flask_admin/base.py",
line *368*, in _run_view
return fn(self, *args, **kwargs)
- File "/usr/local/lib/python2.7/site-packages/flask_admin/model/base.py",
line *1882*, in index_view
view_args.search, view_args.filters, page_size=page_size)
- File
"/usr/local/lib/python2.7/site-packages/flask_admin/contrib/sqla/view.py",
line *997*, in get_list
query = self.get_query()
- File "/app/src/main/python/hermes_reporter/admin.py", line *344*, in
get_query
whens={'SUCCESS': (_model.updated_on - _model.created_on).total_seconds()},
- File
"/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
line *682*, in __getattr__
key)
AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object
has an attribute 'total_seconds'
--
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] Re: SQLAlchemy keeps dropping sessions
On Sun, Jun 3, 2018 at 11:42 PM, rvd wrote: > Hi Mike, > > Thanks for the quick reply. I am using Version 9.6.6 (RDS). I just realized Amazon offers an RDS for Postgresql. This is definitely something RDS-specific, like an idle timeout or something, though I can't find any google results for this. Not using any > proxy. Because this was a rather blocking issue, I have been utilizing a > terrible workaround (namely creating an engine every single request, and > using that session) with some success - obviously not ideal though. I have > tried reducing the pool_recycle parameter to 60 seconds; I came across the > ping thing as well during my research - will give that a try too and let you > know. Thank you a ton again for the suggestions, they are very much > appreciated. > > rvd > > On Sunday, June 3, 2018 at 7:17:55 PM UTC-7, rvd wrote: >> >> Hi all, >> >> I am writing a Flask API that needs to communicate with many datastores, >> Postgres on AWS RDS being one of them. I want to avoid Flask-SQLAlchemy >> (trying to reduce package dependence); I think the standard SQLAlchemy >> library should suffice. I have described the issue thoroughly here (code is >> here as well). But here is the synopsis. When I use a Session object in my >> app, for the first few requests, it works great. Then, if I wait for a while >> and fire off another request, I get a Psycopg2 error about the server >> closing off the connection unexpectedly. This is rather crippling. I have >> spent a lot of time going through SO, asked in the SQLAlchemy IRC channel, >> and tried many tutorials, and this seems to be a standard valid pattern, so >> I am not sure what is going wrong. Should I not be removing the session? >> Should I be using the @app.teardown_appcontext decorator (it seems that if I >> just remove the sessions myself as I am doing now, I shouldn't have to)? >> Should I be using a connection pool instead (it seems that QueuePool is >> enabled under the hood by default anyway)? If it helps, I am using the >> latest version of all packages, Python 3.6.5, and Postman to send the >> requests to the server, which is running at localhost:5000. Any advice would >> be much appreciated - thanks in advance! >> >> Best, >> rvd > > -- > 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 - 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] Re: SQLAlchemy keeps dropping sessions
On Sun, Jun 3, 2018 at 11:42 PM, rvd wrote: > Hi Mike, > > Thanks for the quick reply. I am using Version 9.6.6 (RDS). Not using any > proxy. Because this was a rather blocking issue, I have been utilizing a > terrible workaround (namely creating an engine every single request, and > using that session) with some success - obviously not ideal though. I have > tried reducing the pool_recycle parameter to 60 seconds; I came across the > ping thing as well during my research - will give that a try too and let you > know. Thank you a ton again for the suggestions, they are very much > appreciated. you never need to create engine per request, pooling is turned off entirely using NullPool: http://docs.sqlalchemy.org/en/latest/core/pooling.html#switching-pool-implementations > > rvd > > On Sunday, June 3, 2018 at 7:17:55 PM UTC-7, rvd wrote: >> >> Hi all, >> >> I am writing a Flask API that needs to communicate with many datastores, >> Postgres on AWS RDS being one of them. I want to avoid Flask-SQLAlchemy >> (trying to reduce package dependence); I think the standard SQLAlchemy >> library should suffice. I have described the issue thoroughly here (code is >> here as well). But here is the synopsis. When I use a Session object in my >> app, for the first few requests, it works great. Then, if I wait for a while >> and fire off another request, I get a Psycopg2 error about the server >> closing off the connection unexpectedly. This is rather crippling. I have >> spent a lot of time going through SO, asked in the SQLAlchemy IRC channel, >> and tried many tutorials, and this seems to be a standard valid pattern, so >> I am not sure what is going wrong. Should I not be removing the session? >> Should I be using the @app.teardown_appcontext decorator (it seems that if I >> just remove the sessions myself as I am doing now, I shouldn't have to)? >> Should I be using a connection pool instead (it seems that QueuePool is >> enabled under the hood by default anyway)? If it helps, I am using the >> latest version of all packages, Python 3.6.5, and Postman to send the >> requests to the server, which is running at localhost:5000. Any advice would >> be much appreciated - thanks in advance! >> >> Best, >> rvd > > -- > 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 - 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.
