[sqlalchemy] calling LENGTH() in SQLite?
Perhaps I have been up too many hours, but my syntax foo is fizzling. Given the following class, I want to compute the string length of "position" instead of storing it as another attribute which can get out of sync. eg. class Position(Base): __tablename__ = 'position' id = Column(INTEGER, primary_key=True) timestamp = Column(TIMESTAMP, nullable=False) position = Column(TEXT, unique=True, nullable=False) So to get all positions of string length 2, the following is not working: from sqlalchemy.sql.expression import func # ... for position, in session.query(Position.position).filter(func.length(Position.position == 2): print(position) Any insight offered would certainly be appreciated. 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/CAKeNXXtURxf-eXNaXn1woYE_q9i%3DggeopN%2B_%3D9hQb3FWfqqyLQ%40mail.gmail.com.
[sqlalchemy] Re: How to catch exceptions (the best practice)
On Saturday, July 27, 2019 at 4:53:06 PM UTC-4, Nestor Diaz wrote: > > it then raise an exception, however I can not > catch the exception, even if I set up a try-catch block, however if I > add a DBSession.flush() inside the try, the sql sentence is executed and > therefore I can catch the exception. > ... The question is: Do I have to 'flush()' everytime to be sure the sql > sentence is executed, or there is a better way, or which way do you > suggest to manage the exceptions ? This is because in your specific application, you are using the `pyramid_tm` package. `pyramid_tm` simplifies transaction based application development by wrapping your pyramid views in a 'tween' , where 'BEGIN' is issued before your view executes and a ROLLBACK or COMMIT is issued after your view executes. The integrity error is raised by the database, so it is only raised by your explicit 'flush' or the implicit flush which pyramid_tm invokes during the 'commit' in it's tween /after/ your view executes. The pyramid_tm docs have a section with dealing with errors, which goes into the 'custom error page' pattern that Mike brought up, along with some other patterns that involve making your error information persist. https://docs.pylonsproject.org/projects/pyramid_tm/en/latest/ -- 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/242592cd-3c20-4fdd-be8f-ff12d549f708%40googlegroups.com.
Re: [sqlalchemy] loss of precision when retrieving DATETIME2 column from MSSQL
On Mon, Jul 29, 2019, at 1:49 PM, peter bell wrote:
> A belated thank you for your response.
>
> This worked fine for individual tables but I got an unexpected result (at
> least, unexpected to me) when using this approach with the union or union_all
> functions.
>
> The TypeDecorator was only applied to the first table in the union /
> union_all. I'm sure I can workaround this (but just thought I'd let you know)
>
> Example code below.
>
> Regards,
> Peter
>
>
> from sqlalchemy import (create_engine, TypeDecorator, String, Integer, event,
> MetaData, cast)
> from sqlalchemy.dialects.mssql import DATETIME2
> from sqlalchemy.schema import (Table, Column)
>
> # TypeDecorator to cast DATETIME2 columns to String
> class StringDate(TypeDecorator):
> impl = DATETIME2
>
> def column_expression(self, col):
> return cast(col, String)
>
> # event listener to apply StringDate on Table reflection
> def listen_for_reflect (inspector, table, column_info):
> "receive a column reflect event"
> if isinstance(column_info['type'],DATETIME2):
> column_info['type'] = StringDate
>
> event.listen(Table,'column_reflect',listen_for_reflect)
>
> engine = create_engine("some_db_url")
>
> # create test tables (to mimic mssql temporal tables)
> meta = MetaData()
> Table('t', meta,
> Column('Id', Integer, primary_key=True),
> Column('SysVerStart', DATETIME2),
> Column('SysVerEnd', DATETIME2)
> )
>
> Table('t_history', meta,
> Column('Id', Integer),
> Column('SysVerStart', DATETIME2),
> Column('SysVerEnd', DATETIME2)
> )
> # create tables in our database
> meta.create_all(engine)
this would be an entirely new SQLAlchemy issue unrelated to reflection which is
that TypeEngine.column_expression is not applied to subsequent SELECTs in a
UNION even those SELECTs are at the top level of the query.
https://github.com/sqlalchemy/sqlalchemy/issues/4787 is added.
Workaround is to SELECT from your SELECT, which is what the ORM does in any
case but w/ Core you need to add this:
print(t.select().union_all(t_history.select()).select())
>
> # generate select statements using table reflection
> meta.clear()
> t = Table('t', meta, autoload=True, autoload_with=engine)
> t_history = Table('t_history', meta, autoload=True, autoload_with=engine)
>
> print('** StringDate TypeDecorator applied as expected to t
> :')
> print(t.select())
>
> print('** StringDate TypeDecorator applied as expected to
> t_history :')
> print(t_history.select())
>
> print('** StringDate TypeDecorator only applied to the first
> table in a union_all :')
> print(t.select().union_all(t_history.select()))
>
> print('** StringDate TypeDecorator only applied to the first
> table in a union :')
> print(t_history.select().union(t.select()))
>
>
> On Thursday, 18 July 2019 21:42:16 UTC+3, Mike Bayer wrote:
>>
>>
>> On Thu, Jul 18, 2019, at 1:27 PM, peter bell wrote:
>> >> It seems that you would like to retrieve this value as a string so that
>> >> you can have precision that's not supported by Python datetime
>> >
>> > Yes. If a table contains DATETIME2 columns, I would like to return all
>> > those columns as a string.
>> >
>> > I was able to achieve that by applying your StringDate class explicitly to
>> > the 'created' column using table reflection :
>> >
>> > t = Table('t', meta, Column('created', StringDate),autoload=True,
>> > autoload_with=engine)
>> >
>> > Is there a way to apply such a transformation to all DATETIME2 columns by
>> > default, without explicitly naming them, as above ?
>>
>> there are mulitple contexts in which you may be concerned with when you say
>> "by default". if the autoload case is the one you want to address, then you
>> can use the column_reflect event:
>>
>> https://docs.sqlalchemy.org/en/13/core/events.html?highlight=column_reflect#sqlalchemy.events.DDLEvents.column_reflect
>>
>>
>> from sqlalchemy.schema import Table
>> from sqlalchemy import event
>>
>> def listen_for_reflect(inspector, table, column_info):
>> "receive a column_reflect event"
>> if isinstance(column_info['type'], DATETIME2):
>> column_info['type'] = StringDatetime
>>
>> event.listen(
>> Table,
>> 'column_reflect',
>> listen_for_reflect)
>>
>>
>>
>>
>> >
>> > br
>> > Peter
>> >
>> >
>> > On Thursday, 18 July 2019 18:30:44 UTC+3, Mike Bayer wrote:
>> >>
>> >>
>> >> On Thu, Jul 18, 2019, at 7:56 AM, peter bell wrote:
>> >>> You are correct - it seems the issue is in pyodbc
>> >>
>> >> but the pyodbc issue was fixed over a year ago. It seems that you would
>> >> like to retrieve this value as a string so that you can have precision
>> >> that's not supported by Python datetime, so that is not what the pyodbc
>> >> issue addresses.
>> >>
>> >> For a canonical solution that won't break if pymssql ever changes this,
>> >> you should use CAST:
>> >>
>> >> stmt = text("SELECT CAST(datecol AS VARCHAR) FROM t")
Re: [sqlalchemy] loss of precision when retrieving DATETIME2 column from MSSQL
A belated thank you for your response.
This worked fine for individual tables but I got an unexpected result (at
least, unexpected to me) when using this approach with the union or
union_all functions.
The TypeDecorator was only applied to the first table in the union /
union_all. I'm sure I can workaround this (but just thought I'd let you
know)
Example code below.
Regards,
Peter
from sqlalchemy import (create_engine, TypeDecorator, String, Integer,
event, MetaData, cast)
from sqlalchemy.dialects.mssql import DATETIME2
from sqlalchemy.schema import (Table, Column)
# TypeDecorator to cast DATETIME2 columns to String
class StringDate(TypeDecorator):
impl = DATETIME2
def column_expression(self, col):
return cast(col, String)
# event listener to apply StringDate on Table reflection
def listen_for_reflect (inspector, table, column_info):
"receive a column reflect event"
if isinstance(column_info['type'],DATETIME2):
column_info['type'] = StringDate
event.listen(Table,'column_reflect',listen_for_reflect)
engine = create_engine("some_db_url")
# create test tables (to mimic mssql temporal tables)
meta = MetaData()
Table('t', meta,
Column('Id', Integer, primary_key=True),
Column('SysVerStart', DATETIME2),
Column('SysVerEnd', DATETIME2)
)
Table('t_history', meta,
Column('Id', Integer),
Column('SysVerStart', DATETIME2),
Column('SysVerEnd', DATETIME2)
)
# create tables in our database
meta.create_all(engine)
# generate select statements using table reflection
meta.clear()
t = Table('t', meta, autoload=True, autoload_with=engine)
t_history = Table('t_history', meta, autoload=True, autoload_with=engine)
print('** StringDate TypeDecorator applied as expected to t
:')
print(t.select())
print('** StringDate TypeDecorator applied as expected to
t_history :')
print(t_history.select())
print('** StringDate TypeDecorator only applied to the
first table in a union_all :')
print(t.select().union_all(t_history.select()))
print('** StringDate TypeDecorator only applied to the
first table in a union :')
print(t_history.select().union(t.select()))
On Thursday, 18 July 2019 21:42:16 UTC+3, Mike Bayer wrote:
>
>
>
> On Thu, Jul 18, 2019, at 1:27 PM, peter bell wrote:
> >> It seems that you would like to retrieve this value as a string so that
> you can have precision that's not supported by Python datetime
> >
> > Yes. If a table contains DATETIME2 columns, I would like to return all
> those columns as a string.
> >
> > I was able to achieve that by applying your StringDate class explicitly
> to the 'created' column using table reflection :
> >
> > t = Table('t', meta, Column('created', StringDate),autoload=True,
> autoload_with=engine)
> >
> > Is there a way to apply such a transformation to all DATETIME2 columns
> by default, without explicitly naming them, as above ?
>
> there are mulitple contexts in which you may be concerned with when you
> say "by default". if the autoload case is the one you want to address, then
> you can use the column_reflect event:
>
>
> https://docs.sqlalchemy.org/en/13/core/events.html?highlight=column_reflect#sqlalchemy.events.DDLEvents.column_reflect
>
>
> from sqlalchemy.schema import Table
> from sqlalchemy import event
>
> def listen_for_reflect(inspector, table, column_info):
> "receive a column_reflect event"
> if isinstance(column_info['type'], DATETIME2):
> column_info['type'] = StringDatetime
>
> event.listen(
> Table,
> 'column_reflect',
> listen_for_reflect)
>
>
>
>
> >
> > br
> > Peter
> >
> >
> > On Thursday, 18 July 2019 18:30:44 UTC+3, Mike Bayer wrote:
> >>
> >>
> >> On Thu, Jul 18, 2019, at 7:56 AM, peter bell wrote:
> >>> You are correct - it seems the issue is in pyodbc
> >>
> >> but the pyodbc issue was fixed over a year ago. It seems that you would
> like to retrieve this value as a string so that you can have precision
> that's not supported by Python datetime, so that is not what the pyodbc
> issue addresses.
> >>
> >> For a canonical solution that won't break if pymssql ever changes this,
> you should use CAST:
> >>
> >> stmt = text("SELECT CAST(datecol AS VARCHAR) FROM t")
> >> stmt = stmt.columns(created=String)
> >>
> >> if you want to work with the SQL expression language you can make this
> cast automatic using column_expression documented at
> https://docs.sqlalchemy.org/en/13/core/custom_types.html#applying-sql-level-bind-result-processing
>
> >>
> >> from sqlalchemy import TypeDecorator, String
> >>
> >> class StringDate(TypeDecorator):
> >> impl = DATETIME2
> >>
> >> def column_expression(self, col):
> >> return cast(col, String)
> >>
> >>
> >>
> >>
> >>
> >>>
> >>> I installed pymssql and used that when creating the sqlalchemy engine
> object.
> >>>
> >>> The DATETIME2 column is now mapped to a
Re: [sqlalchemy] How to catch exceptions (the best practice)
On Sat, Jul 27, 2019, at 4:53 PM, Nestor Diaz wrote:
> Hello everybody.
>
> I am experimenting with sqlalchemy and pyramid, following the tutorial
> steps at:
>
> https://docs.pylonsproject.org/projects/pyramid/en/latest/quick_tutorial/databases.html
>
> The example above is a wiki, with a set of pages, every page have a
> 'title' and a 'body' and the 'title' have a 'unique' constraint at
> database level.
>
> Let's suppose that I added two pages, i.e. pages =
> [{id:1,title:'titleA',body:'bodyA'}, {id:2,title:'titleB',body:'bodyB'}
> , then I decide to update id number 2 and set title to 'titleA' which is
> the same as id number 1, it then raise an exception, however I can not
> catch the exception, even if I set up a try-catch block, however if I
> add a DBSession.flush() inside the try, the sql sentence is executed and
> therefore I can catch the exception.
>
> I am copying the code with the 'Uncaught Exception' and the code with
> the 'Caught Exception'.
>
> The question is: Do I have to 'flush()' everytime to be sure the sql
> sentence is executed, or there is a better way, or which way do you
> suggest to manage the exceptions ?
Usually you wouldn't have a catch that is specific to an error like that, your
pyramid application would have a generic error page routine that occurs for all
requests, and it would occur normally at the point at which the Session is
committed. It looks like you essentially want a custom error page scheme here
which I would think would be something that happens more at the pyramid level,
e.g. in your controller you add some kind of token "use_this_error_page" so
that Pyramid's usual error page routine can direct to this special page.
When people do catch IntegrityError specifically, it is often because they want
to try to insert a row and then switch to an UPDATE if the row already exists,
and in this pattern you normally want to use a savepoint, but that doesn't seem
like what's happening here.
Ultimately, for the code given, the answer is yes, you would need to flush to
emit the SQL at this specific place if that's what you need, but I would seek
out cleaner patterns from the Pyramid community.
>
> Thanks for your time and attention.
>
> This is the python code:
>
> [...]
>
> # Change the content and redirect to the view
>
> try:
> from sqlalchemy.exc import IntegrityError
> page.title = appstruct['title']
> page.body = appstruct['body']
> DBSession.flush()
> url = self.request.route_url('wikipage_view', uid=uid)
> return HTTPFound(url)
> except IntegrityError as exc:
> import traceback
> traceback.print_exc()
> error = colander.Invalid(wiki_form.schema)
> error['title'] = exc.args[0]
> wiki_form.error = error
> wiki_form.widget.handle_error(wiki_form, error)
> rendered_form = wiki_form.render()
> return dict(page=page,
> form=rendered_form)
>
>
> [...]
>
> The tracebacks:
>
> 1. Uncaught Exception:
>
> 2019-07-27 01:29:41,231 INFO [sqlalchemy.engine.base.Engine][waitress]
> UPDATE wikipages SET title=? WHERE wikipages.uid = ?
> 2019-07-27 01:29:41,231 INFO [sqlalchemy.engine.base.Engine][waitress]
> ('titleA', 2)
> 2019-07-27 01:29:41,232 INFO [sqlalchemy.engine.base.Engine][waitress]
> ROLLBACK
> 2019-07-27 01:29:41,306 ERROR [pyramid_debugtoolbar][waitress] Uncaught
> sqlalchemy.exc.IntegrityError at http://127.0.0.1:6543/2/edit
> traceback url:
> http://127.0.0.1:6543/_debug_toolbar/313430303936383730393639343136/exception
> Traceback (most recent call last):
> File
> "/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
> line 1244, in _execute_context
> cursor, statement, parameters, context
> File
> "/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/default.py",
> line 550, in do_execute
> cursor.execute(statement, parameters)
> sqlite3.IntegrityError: UNIQUE constraint failed: wikipages.title
>
> The above exception was the direct cause of the following exception:
>
> Traceback (most recent call last):
> File
> "/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/pyramid_debugtoolbar/toolbar.py",
> line 257, in toolbar_tween
> response = _handler(request)
> File
> "/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/pyramid_debugtoolbar/panels/performance.py",
> line 58, in resource_timer_handler
> result = handler(request)
> File
> "/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/pyramid_tm/__init__.py",
> line 171, in tm_tween
> reraise(*exc_info)
> File
> "/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/pyramid_tm/compat.py",
> line 36, in reraise
> raise value
> File
> "/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/pyramid_tm/__init__.py",
> line 152, in tm_tween
> return _finish(request, manager.commit, response)
> File
> "/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/pyramid_tm/__init__.py",
> line 96, in _finish
> reraise(*exc_info)
> File
>
