Re: [sqlalchemy] (SQLite) "Outside" auto-locking based on SQLAlchemy Events
On Nov 12, 2012, at 5:33 PM, Torsten Landschoff wrote:
>
> BTW, I found one offender that breaks running database upgrades with my
> locking schemes:
>
> from sqlalchemy import *
> from sqlalchemy.pool import *
>
> engine = create_engine("sqlite:home/torsten/some.db",
> poolclass=AssertionPool)
> conn = engine.connect()
> metadata = MetaData(conn, reflect=True)
>
> This results in the following backtrace here:
>
>raise AssertionError("connection is already checked out" + suffix)
> AssertionError: connection is already checked out at:
> File "demo.py", line 5, in
>conn = engine.connect()
>
> I would have expected it to reflect using the connection passed to the
> MetaData constructor.
if so then that's a bug, added http://www.sqlalchemy.org/trac/ticket/2604 to
take a look at that.
--
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.
Re: [sqlalchemy] (SQLite) "Outside" auto-locking based on SQLAlchemy Events
Hi Michael,
On 11/09/2012 11:36 PM, Michael Bayer wrote:
> On Nov 8, 2012, at 5:01 PM, Torsten Landschoff wrote:
>> My first tests with the SQLAlchemy core where promising, but when using
>> the ORM I get a bunch of deadlocks where it seems like the session opens
>> two connections A and B where A locks B out.
> The Session never does this, assuming just one Engine associated with it. It
> acquires one Connection from the Engine, holds onto it and uses just that
> connection, until commit() at which point the connection is released to the
> pool.
Okay, thanks, maybe the error was elsewhere then.
> SQLite supports a "SERIALIZABLE" mode of isolation, in conjunction with a
> workaround for a pysqlite bug
> (http://docs.sqlalchemy.org/en/rel_0_7/dialects/sqlite.html#serializable-transaction-isolation)
> which might be what you're looking for, though I generally try to steer
> users away from any usage of SQLite that depends on high concurrency (see
> "High Concurrency" at http://sqlite.org/whentouse.html).
I do not consider an application that downloads new records once per
hour concurrently to the GUI "high concurrency". And that background
process is not really a problem either, as long as I just lock the
database all the time. This makes the gui freeze for a couple of minutes
though.
Therefore I am looking for a solution that will make background and main
thread cooperate wrt. database access.
BTW: The main issue is not concurrency in itself. SQLite just uses
filesystem locking which are basically spin locks. So as long as the
background thread updates the database it has a high probability to
reacquire the lock after each transaction while the GUI thread will fail
to hit the slots where the db is not locked.
> To diagnose this code, you'd need to make use of the tools available - which
> includes connection pool logging, engine logging, and possibly usage of
> custom pools like sqlalchemy.pool.AssertionPool which ensures that only one
> connection is used at any time.
Thanks for the pointer to AssertionPool. I already use the others.
BTW, I found one offender that breaks running database upgrades with my
locking schemes:
from sqlalchemy import *
from sqlalchemy.pool import *
engine = create_engine("sqlite:home/torsten/some.db",
poolclass=AssertionPool)
conn = engine.connect()
metadata = MetaData(conn, reflect=True)
This results in the following backtrace here:
$ python demo.py
Traceback (most recent call last):
File "demo.py", line 6, in
metadata = MetaData(conn, reflect=True)
File
"/opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/schema.py",
line 2363, in __init__
self.reflect()
File
"/opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/schema.py",
line 2497, in reflect
connection=conn))
File
"/opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 2504, in table_names
conn = self.contextual_connect()
File
"/opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 2490, in contextual_connect
self.pool.connect(),
File
"/opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/pool.py",
line 224, in connect
return _ConnectionFairy(self).checkout()
File
"/opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/pool.py",
line 387, in __init__
rec = self._connection_record = pool._do_get()
File
"/opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/pool.py",
line 911, in _do_get
raise AssertionError("connection is already checked out" + suffix)
AssertionError: connection is already checked out at:
File "demo.py", line 5, in
conn = engine.connect()
I would have expected it to reflect using the connection passed to the
MetaData constructor.
Greetings, Torsten
--
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff
Office Dresden
Tel: +49-(0)351-4519587
Fax: +49-(0)351-4519561
mailto:torsten.landsch...@dynamore.de
http://www.dynamore.de
Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz
--
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.
Re: [sqlalchemy] All column names renames: best practice?
On Nov 12, 2012, at 5:32 AM, Petr Blahoš wrote:
> Hi all,
>
> the company I worked for has decided to change a RDBMS behind our ERP.
> The side effect of this is that the columns will no longer be prefixed with t$
> but with t_ instead. I do not want to change all the occurences of column
> names in my code. I should also mention, that I use only selects, and no ORM.
>
> So what I did was I made a new dialect as a subclass of mssql.pyodbc and
> I overrode execution context and statement compiler. In statement compiler's
> visit_select I simply replace "t$" with "t_" in the select returned from the
> parent,
> and in execution context's get_result_proxy I return a proxy whose row
> proxy's
> keymap is slightly updated (don't worry, I am attaching the code).
>
> My question: Is this the right way to do it?
Dialects only exist to handle the task of interacting with a given
DBAPI/database pair, and are not intended to be extensible for the purposes of
satisfying particular use cases. SQLAlchemy supports an event API that can
easily provide for search-and-replace features like these. Just use
before_cursor_execute() along with retval=True:
http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=before_cursor_execute#sqlalchemy.events.ConnectionEvents.before_cursor_execute
for the result side, there are several places this might be intercepted:
1. in after_cursor_execute(), you can modify the ".description" attribute on
the cursor to match the changes in label name.
2. if the pyodbc cursor is disallowing modification of .description, alter the
"context" passed to after_cursor_execute():
a. wrapping the immutable cursor with a wrapper that provides a new
.description,
b. patching on a get_result_proxy() method with a new ResultProxy subclass
that overrides _cursor_description()
3. or use the after_execute() event, where you're passed the ResultProxy which
you could then change in place -
you could re-establish the "metadata" via "result._metadata =
ResultMetaData(result, make_new_metadata(cursor.description))".
4. or given the ResultProxy in after_execute(), do the same rewriting of the
keymap that you're doing now.
But I'd probably not be using that approach either.Column objects support a
"key" field so that they need not be referenced in code in the same way the
relational database does; one of the primary purposes of Column is to allow
symbolic names to prevent the issue of needing to "change all occurrences" of
any schema-related name in code:
my_table = Table('some_name', metadata, Column('t$somename', Integer,
key='somename'))
generation of a "key" like the above can be automated using a simple function:
def column(name, *arg, **kw):
key = name.replace('t$', '', name)
kw.setdefault('key', key)
return Column(name, *arg, **kw)
my_table = Table('some_name', metadata, column('t$somename', Integer))
if OTOH you're using table reflection, you can use the column_reflect event,
which provides a dictionary where you can place a new "key":
@event.listens_for(Table, 'column_reflect')
def evt(inspector, table, column_info):
key = column_info['name'].replace('t$', column_info['name'])
column_info['key'] = key
http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=column_reflect#sqlalchemy.events.DDLEvents.column_reflect
note that the "inspector" argument above is new in 0.8 - if in 0.7, the
arguments are just "table", and "column_info".
> Thanks in advance
>
> --
> Petr
>
> ( attaching the code of the dialect - same as this:
> https://gist.github.com/4058539 )
>
>
> --
> 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/-/nXkzhvJiwysJ.
> 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.
>
--
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] All column names renames: best practice?
Hi all,
the company I worked for has decided to change a RDBMS behind our ERP.
The side effect of this is that the columns will no longer be prefixed with
*t$*
but with* t_* instead. I do not want to change all the occurences of column
names in my code. I should also mention, that I use only selects, and no
ORM.
So what I did was I made a new dialect as a subclass of mssql.pyodbc and
I overrode execution context and statement compiler. In statement
compiler's
visit_select I simply replace "t$" with "t_" in the select returned from
the parent,
and in execution context's get_result_proxy I return a proxy whose row
proxy's
keymap is slightly updated (don't worry, I am attaching the code).
My question: Is this the right way to do it?
Thanks in advance
--
Petr
( attaching the code of the dialect - same as this:
https://gist.github.com/4058539 )
--
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/-/nXkzhvJiwysJ.
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.
import copy
import sqlalchemy.engine.base
import sqlalchemy.dialects.mssql.base
import sqlalchemy.dialects.mssql.pyodbc
class SQLABaanRowProxy(sqlalchemy.engine.base.RowProxy):
def __init__(self, parent, row, processors, keymap):
"""
For all keys starting with "t_" adds also a "t$..." key.
"""
km = copy.deepcopy(keymap)
for (k, v) in keymap.iteritems():
if isinstance(k, basestring):
if k.startswith("t_"):
km["t$" + k[2:]] = v
super(SQLABaanRowProxy, self).__init__(parent, row, processors, km)
class SQLABaanResultProxy(sqlalchemy.engine.base.ResultProxy):
_process_row = SQLABaanRowProxy
class SQLABaanCompiler(sqlalchemy.dialects.mssql.base.MSSQLCompiler):
def visit_select(self, select, **kwargs):
"""
Brute-force replace of "t$" to "t_...":w
"""
ret = super(SQLABaanCompiler, self).visit_select(select, **kwargs)
ret = ret.replace("t$", "t_")
return ret
class SQLABaanExecutionContext(sqlalchemy.dialects.mssql.pyodbc.MSExecutionContext_pyodbc):
def get_result_proxy(self):
if self._result_proxy:
return self._result_proxy #can never happen as we do only selects
else:
return SQLABaanResultProxy(self)
class SQLABaan(sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc):
execution_ctx_cls = SQLABaanExecutionContext
statement_compiler = SQLABaanCompiler
dialect = SQLABaan
