date:20141221

Re: [sqlalchemy] the right way to use multiple backend servers

2014-12-21 Thread Michael Bayer

I’d recommend looking into HAProxy:   http://www.haproxy.org/   it’s designed 
explicitly for the task of proxying a single address to multiple backend 
servers, providing failover when a node goes down.you’d not have to 
implement this complexity in your application layer.


Huabin Zheng  wrote:

> Hi buddies,
> 
> I want to configure multiple backend servers in sqlalchemy to solve 
> single point fault. But it seems that there's no direct way. Pooling document 
>  gives me some hints 
> that I can give it a custom connection Creator, so I can set multiple 
> backends in the Creator and try the next backend if the previous failed. BUT, 
> I found that dialect, pool, and engine are tightly bindings, so there may be 
> problems in my solution.
> 
> So what's the write way to use multiple backend servers? Thanks.
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com 
> .
> To post to this group, send email to sqlalchemy@googlegroups.com 
> .
> Visit this group at http://groups.google.com/group/sqlalchemy 
> .
> For more options, visit https://groups.google.com/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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] the right way to use multiple backend servers

2014-12-21 Thread Huabin Zheng

Hi buddies,

I want to configure multiple backend servers in sqlalchemy to solve 
single point fault. But it seems that there's no direct way. Pooling 
document  gives me 
some hints that I can give it a custom connection Creator, so I can set 
multiple backends in the Creator and try the next backend if the previous 
failed. BUT, I found that dialect, pool, and engine are tightly bindings, 
so there may be problems in my solution.

So what's the write way to use multiple backend servers? Thanks.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the handling of unique keys/indexes

2014-12-21 Thread SF Markus Elfring

> There is no way to use the second form while being able to record the moment
> each parameter set is used, unless the DBAPI itself provides additional hooks
> for logging at this level.  However, this logging would defeat some of the
> purpose of executemany(), which is that of processing many parameter sets
> at maximum speed.

Thanks for your helpful advice.

Regards,
Markus

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the handling of unique keys/indexes

2014-12-21 Thread Michael Bayer



SF Markus Elfring  wrote:

>>> Should I get the exception "sqlalchemy.exc.IntegrityError" directly
>>> after I attempted to insert a second record set with unique attributes
>>> into a SQLite table?
>> I don’t have a stack trace here to see what the nature of the issue is
>> but it is likely that the INSERT is proceeding using DBAPI executemany(),
> 
> Yes. - It seems that this method was used in my use case.
> 
> 
>> which receives the full set of records in one batch before any
>> communication with the database is established.
> 
> Can it happen then that an error will be reported for a single SQL statement
> which was submitted within an unit of more database commands?
> 
> 
>> SQLAlchemy doesn’t have access to at what point each individual series
>> of parameters are invoked as the interface is too coarse-grained.
> 
> Do you know any attempts to make the affected error reporting more precise?


Here are the two options we have for invoking a statement:

try:
cursor.execute(stmt, params)   # single row
except IntegrityError:
   # …

try:
   cursor.executemany(stmt, [params, params, params, params, …])   # many rows
except IntegrityError:
   # …

There is no way to use the second form while being able to record the moment 
each parameter set is used, unless the DBAPI itself provides additional hooks 
for logging at this level.   However, this logging would defeat some of the 
purpose of executemany(), which is that of processing many parameter sets at 
maximum speed.

The SQLAlchemy Session tries to use executemany() as often as it can within a 
flush() procedure; it can be used any time there are more than one row to be 
INSERTED where we already have the primary key value available.

If you’d like to operate on individual rows, I guess I wasn’t specific enough 
from my instruction to use SAVEPOINT, you should flush individually:

for obj in all_my_objects:
session.add(obj)
try:
with session.begin_nested():
session.flush()
except IntegrityError:
# deal with this error, don’t add obj, or however it is you intend 
to deal with existing rows






> 
> Regards,
> Markus
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the handling of unique keys/indexes

2014-12-21 Thread SF Markus Elfring

>> Should I get the exception "sqlalchemy.exc.IntegrityError" directly
>> after I attempted to insert a second record set with unique attributes
>> into a SQLite table?
> I don’t have a stack trace here to see what the nature of the issue is
> but it is likely that the INSERT is proceeding using DBAPI executemany(),

Yes. - It seems that this method was used in my use case.


> which receives the full set of records in one batch before any
> communication with the database is established.

Can it happen then that an error will be reported for a single SQL statement
which was submitted within an unit of more database commands?


> SQLAlchemy doesn’t have access to at what point each individual series
> of parameters are invoked as the interface is too coarse-grained.

Do you know any attempts to make the affected error reporting more precise?

Regards,
Markus

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the handling of unique keys/indexes

2014-12-21 Thread Michael Bayer

SF Markus Elfring  wrote:

>> SQLAlchemy sends to the log the statement and parameters it is to send
>> to the DBAPI cursor.execute() method, *before* it actually does so.
>> This so that if the DBAPI throws an exception, as is the case here,
>> one can see what instructions were sent to it which were the immediate
>> cause of this error.
> 
> Thanks for your explanation.
> 
> Do I need to consider any more fine-tuning for my database session?
> 
> 
>> The mechanism of a UNIQUE constraint is that this is a database-level
>> construct, so the backend database is tasked with checking this
>> this condition and reporting on it at statement execution time.
> 
> Should I get the exception "sqlalchemy.exc.IntegrityError" directly
> after I attempted to insert a second record set with unique attributes
> into a SQLite table?

I don’t have a stack trace here to see what the nature of the issue is but it 
is likely that the INSERT is proceeding using DBAPI executemany(), which 
receives the full set of records in one batch before any communication with the 
database is established.  SQLAlchemy doesn’t have access to at what point each 
individual series of parameters are invoked as the interface is too 
coarse-grained.

The two general techniques for dealing with unique constraints are to either 
SELECT ahead of time the rows that you know to be dealing with into a 
collection, such that you can check within this collection ahead of time for 
the existing row before proceeding, or invoking the INSERT of rows one at a 
time, catching each IntegrityError inside of a SAVEPOINT 
(http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#using-savepoint
 describes the Session’s API for SAVEPOINT).   Note that the SQLite driver has 
a bug with SAVEPOINT which you need to apply the technique at 
http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#pysqlite-serializable 
in order to overcome.

> 
> Regards,
> Markus

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the handling of unique keys/indexes

2014-12-21 Thread SF Markus Elfring

> SQLAlchemy sends to the log the statement and parameters it is to send
> to the DBAPI cursor.execute() method, *before* it actually does so.
> This so that if the DBAPI throws an exception, as is the case here,
> one can see what instructions were sent to it which were the immediate
> cause of this error.

Thanks for your explanation.

Do I need to consider any more fine-tuning for my database session?


> The mechanism of a UNIQUE constraint is that this is a database-level
> construct, so the backend database is tasked with checking this
> this condition and reporting on it at statement execution time.

Should I get the exception "sqlalchemy.exc.IntegrityError" directly
after I attempted to insert a second record set with unique attributes
into a SQLite table?

Regards,
Markus

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the handling of unique keys/indexes

2014-12-21 Thread Michael Bayer

SF Markus Elfring  wrote:

> Now I stumble on an error message like the following.
> ...
>cursor.executemany(statement, parameters)
> sqlalchemy.exc.IntegrityError: (IntegrityError) UNIQUE constraint failed: 
> positions.function, 
> ...
> 
> 
> The message might be appropriate in principle for my concrete use case.
> But I observe that the constraint violation is reported a bit
> too late because I got the impression from corresponding debug
> output that three rows were added to the shown table here
> with unique attributes.
> 
> I would appreciate your explanations and further advices.

SQLAlchemy sends to the log the statement and parameters it is to send to the 
DBAPI cursor.execute() method, *before* it actually does so.  This so that if 
the DBAPI throws an exception, as is the case here, one can see what 
instructions were sent to it which were the immediate cause of this error.

The mechanism of a UNIQUE constraint is that this is a database-level 
construct, so the backend database is tasked with checking this condition and 
reporting on it at statement execution time.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] ResourceClosedError on Postgres

2014-12-21 Thread Michael Bayer

you are using ZopeTransactionExtension so you should not be calling 
session.rollback(). Please read the docs for ZopeTransactionExtension at 
https://pypi.python.org/pypi/zope.sqlalchemy.



alchemy1  wrote:

> Also my DBSession is defined like this
> 
> DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
> 
> And I've enabled pyramid_tm in my development.ini file for Pyramid.
> 
> 
> On Sunday, December 21, 2014 1:09:58 AM UTC+1, alchemy1 wrote:
> I'm getting this error with the following code. Unfortunately none of the 
> code in the stacktrace is my code, it's all SQLAlchemy stuff, so I can't tell 
> where I'm causing the problem.
> 
> Traceback (most recent call last):
>   File 
> "/home/user/.virtualenvs/project/lib/python3.4/site-packages/pyramid_debugtoolbar-2.2.2-py3.4.egg/pyramid_debugtoolbar/panels/performance.py",
>  line 57, in resource_timer_handler
> result = handler(request)
>   File 
> "/home/user/.virtualenvs/project/lib/python3.4/site-packages/pyramid-1.5.1-py3.4.egg/pyramid/tweens.py",
>  line 21, in excview_tween
> response = handler(request)
>   File 
> "/home/user/.virtualenvs/project/lib/python3.4/site-packages/pyramid_tm-0.8-py3.4.egg/pyramid_tm/__init__.py",
>  line 92, in tm_tween
> reraise(*exc_info)
>   File 
> "/home/user/.virtualenvs/project/lib/python3.4/site-packages/pyramid_tm-0.8-py3.4.egg/pyramid_tm/compat.py",
>  line 13, in reraise
> raise value
>   File 
> "/home/user/.virtualenvs/project/lib/python3.4/site-packages/pyramid_tm-0.8-py3.4.egg/pyramid_tm/__init__.py",
>  line 80, in tm_tween
> manager.commit()
>   File 
> "/home/user/.virtualenvs/project/lib/python3.4/site-packages/transaction-1.4.3-py3.4.egg/transaction/_manager.py",
>  line 111, in commit
> return self.get().commit()
>   File 
> "/home/user/.virtualenvs/project/lib/python3.4/site-packages/transaction-1.4.3-py3.4.egg/transaction/_transaction.py",
>  line 280, in commit
> reraise(t, v, tb)
>   File 
> "/home/user/.virtualenvs/project/lib/python3.4/site-packages/transaction-1.4.3-py3.4.egg/transaction/_compat.py",
>  line 55, in reraise
> raise value
>   File 
> "/home/user/.virtualenvs/project/lib/python3.4/site-packages/transaction-1.4.3-py3.4.egg/transaction/_transaction.py",
>  line 271, in commit
> self._commitResources()
>   File 
> "/home/user/.virtualenvs/project/lib/python3.4/site-packages/transaction-1.4.3-py3.4.egg/transaction/_transaction.py",
>  line 417, in _commitResources
> reraise(t, v, tb)
>   File 
> "/home/user/.virtualenvs/project/lib/python3.4/site-packages/transaction-1.4.3-py3.4.egg/transaction/_compat.py",
>  line 55, in reraise
> raise value
>   File 
> "/home/user/.virtualenvs/project/lib/python3.4/site-packages/transaction-1.4.3-py3.4.egg/transaction/_transaction.py",
>  line 394, in _commitResources
> rm.tpc_vote(self)
>   File 
> "/home/user/.virtualenvs/project/lib/python3.4/site-packages/zope.sqlalchemy-0.7.5-py3.4.egg/zope/sqlalchemy/datamanager.py",
>  line 103, in tpc_vote
> self.tx.commit()
>   File 
> "/home/user/.virtualenvs/project/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4-linux-x86_64.egg/sqlalchemy/orm/session.py",
>  line 375, in commit
> self._assert_active(prepared_ok=True)
>   File 
> "/home/user/.virtualenvs/project/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4-linux-x86_64.egg/sqlalchemy/orm/session.py",
>  line 223, in _assert_active
> raise sa_exc.ResourceClosedError(closed_msg)
> sqlalchemy.exc.ResourceClosedError: This transaction is closed
> 
> 
> Here I try to add a user, and when I flush it if I get an IntegrityError it 
> means the user already exists.
> In this case I search for another object Action. If I get NoResultFound I 
> create a new one and try to insert it.
> And it's here that the exception happens. Any idea how I can fix this?
> 
> 
> DBSession.add(user)
> try:
> DBSession.flush()
> except sqlalchemy.exc.IntegrityError as e:
> # User already exists
> DBSession.rollback()
> user = DBSession.query(User).filter(User.email == email).one()
> try:
> action = DBSession.query(Action).filter(Action.name == name, 
> Action.param == None).one()
> except sqlalchemy.orm.exc.NoResultFound:
> action = create_new_action()
> DBSession.add(action)
> 
> return HTTPFound(location=request.route_url('page'))
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com 
> .
> To post to this group, send email to sqlalchemy@googlegroups.com 
> .
> Visit this group at http://groups.google.com/group/sqlalchemy 
> .
> For more options, visit https://groups.goo

Re: [sqlalchemy] the right way to use multiple backend servers

[sqlalchemy] the right way to use multiple backend servers

Re: [sqlalchemy] Checking the handling of unique keys/indexes

Re: [sqlalchemy] Checking the handling of unique keys/indexes

Re: [sqlalchemy] Checking the handling of unique keys/indexes

Re: [sqlalchemy] Checking the handling of unique keys/indexes

Re: [sqlalchemy] Checking the handling of unique keys/indexes

Re: [sqlalchemy] Checking the handling of unique keys/indexes

Re: [sqlalchemy] ResourceClosedError on Postgres

9 matches

Site Navigation

Mail list logo

Footer information