[sqlalchemy] Re: composite primary key/postgres
So part of the problem is postgresql is autoincrementing where you do not want it to do so? I thought postgresql only autoincrements where your column is of type 'serial'. Is that not true? Or if so, you could use type 'integer' instead of 'serial'. There is also the possibility that the combination of column type 'integer' and 'nullable=False is handled as identical to 'serial', i.e. autoincrement. I think I've seen that before but I'm not sure. On May 12, 2:57 pm, [EMAIL PROTECTED] wrote: hi. i have a sort-of multicolumn m2m association table, where the primary key is composed of all the links. At least 1 link (actualy, 2) is always present, but never all. so i am defining all of those columns with primary_key=True, nullable=True. which is fine in sqlite, but doesnot work in postgres - it autoincrements those columns without value. how can i fix this? would a default_value=0 - or something - work? (now as i look at it, at least as declaration, the whole primary key seems nullable - is this wrong?) ciao svil --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Multiple SQLAlchemy DB usage in TurboGears
Michael: Thanks, I've gone the per-request route for the moment. The reason we're behind on SQLA versions is that the TurboGears we started on a year ago used that particular version; we do have TurboGears upgrade plans in place, so that'll be sorted out soon enough. Barry: I've left the Visit information in a database-per-customer as the Visit table relies on the User table (which we need for other joins), and I don't feel like ensuring that it only ever used the primary key as an integer (and therefore breaking the foreign key relationship). Thanks for the comments re. Identity though. Bryn On May 9, 2:54 am, Barry Hart [EMAIL PROTECTED] wrote: The identity and visit stuff is pluggable, i.e. you can replace the existing components without hackery; just write your own and specify which one to use in the application .cfg file. This probably sounds more intimidating than it is; the code is really pretty straightforward and you can use their implementations as a starting point. You might consider using a single (separate) database to store all visit information for *all* the customers combined. Your visit schema could have an additional 'customer' column to distinguish which database to use for everything else. Barry - Original Message From: Michael Bayer [EMAIL PROTECTED] To: sqlalchemy@googlegroups.com Sent: Thursday, May 8, 2008 5:05:22 PM Subject: [sqlalchemy] Re: Multiple SQLAlchemy DB usage in TurboGears On May 8, 2008, at 11:15 AM, Bobby Impollonia wrote: I'd try bypassing their SQLA integration altogether if thats possible It isn't possible if you are relying on the turbogears identity system (cookie-based visitor tracking and access control). that sounds like a particular set of mappings and classes. theres no issue using those; I was talking about their Session integration. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] View this page sqlalchemy Insert
Click on http://groups.google.com/group/sqlalchemy/web/sqlalchemy-insert - or copy paste it into your browser's address bar if that doesn't work. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] SQL mass-delete
I'd like to delete all Transactions contained in an account hierarchy without loading any transaction into memory, just DB work with the SQL DELETE request constructed by SA. The query that defines the transactions is: Session.query(Transaction).join(['entries','account','root'], aliased=True).filter_by(account_id=1).all() How can I use it to construct and execute the DELETE statement? Thanks for any help. jean-philippe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL mass-delete
On May 13, 2008, at 9:08 AM, jean-philippe dutreve wrote: I'd like to delete all Transactions contained in an account hierarchy without loading any transaction into memory, just DB work with the SQL DELETE request constructed by SA. The query that defines the transactions is: Session.query(Transaction).join(['entries','account','root'], aliased=True).filter_by(account_id=1).all() How can I use it to construct and execute the DELETE statement? To use the Query directly in 0.4 (as opposed to constructing the equivalent select() construct) is a little awkward: q = Session.query(Transaction).join(['entries','account','root'], aliased=True).filter_by(account_id=1) stmt = q.statement stmt.use_labels = False Session.execute(transaction_table.delete().where(exists([1], Transaction.id==stmt.c.id)) in 0.5 you can do this in a few ways: q = Session.query(Transaction.id).join(...).filter(...) Session.execute(table.delete().where(Transaction.id.in_(q.statement))) or if you're on Oracle which doesn't like IN (SELECT ...): ta = aliased(Transaction) q = Session.query(ta.id).join(...).filter(...) stmt = q.statement Session .execute(table.delete().where(exists(stmt.where(ta.id==Transaction.id))) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL mass-delete
On May 13, 2008, at 11:03 AM, jean-philippe dutreve wrote: I've this error: ProgrammingError: (ProgrammingError) subquery in FROM must have an alias HINT: For example, FROM (SELECT ...) [AS] foo. 'DELETE FROM winancial_integ.acc_transactions WHERE EXISTS (SELECT 1 \nFROM (SELECT winancial_integ.acc_transactions.transaction_id AS transaction_id, ... hi, then just call statement.alias() to make it into an alias. - mike --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Case insensitive queries
I've been digging around the archives, web and source code to figure
case insensitive queries using SA, and am posting these notes:
(a) for review and comment, and
(b) to provide some possible hints for others who may follow
For me, a big benefit of using SA is that it insulates me from
database specifics. Ideally, I wanted a way to handle case
insensitive queries that would work across all supported databases, or
at least SQLite, MySQL and Postgres. Previous messages on this list
[1][2] suggest that won't happen before SA 0.5 (with no guarantee of
that, I guess) [3]. There was a possible answer at [4], but I didn't
like the potential implications for query performance.
[1]
http://groups.google.com/group/sqlalchemy/browse_frm/thread/ce87bd74fa4311ba/9c06644a94b358b6?lnk=gstq=case+insensitive+query#9c06644a94b358b6
[2]
http://groups.google.com/group/sqlalchemy/browse_frm/thread/cc8c775dd54b7b9c/18e059753d819455?lnk=gstq=case+insensitive+query#18e059753d819455
[3] http://www.sqlalchemy.org/trac/ticket/487
[4]
http://groups.google.com/group/sqlalchemy/browse_frm/thread/a7b1c325c267bba8/6ef14c5957b59395?lnk=gstq=case+insensitive+query#6ef14c5957b59395
So the solution I've adopted, and tested with SQLite, is to use a
TypeDecorator class, thus:
[[
class CI_String(sqlalchemy.types.TypeDecorator):
Defines a case insensitive string type using SQLite dialect
options
TODO: extend case insensitive specification to support other
databases
impl = sqlalchemy.String
def get_col_spec(self):
return super(CI_String,self).get_col_spec()+ COLLATE NOCASE
]]
Which I use in a Table definition thus:
[[
def defineAffyLocalTable(tablename, metadata):
Return SQLAlchemy table for Affymetrix local data entry.
table = Table(tablename, metadata,
Column('recordid',Integer, Sequence('recordid_seq'),
primary_key=True),
Column('probeid', CI_String(25), index=True),
Column('aly_mean',Float),
Column('can_mean',Float),
Column('comr_mean', Float),
Column('topi_mean', Float),
Column('zaa_mean',Float),
Column('red_e_mean', Float),
Column('description', Text),
)
return table
]]
Might something like this be a basis for a fairly simple SA-common
type that can be implemented appropriately for each database?
#g
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL mass-delete
On May 13, 2008, at 11:28 AM, jean-philippe dutreve wrote: Ok thanks. Transaction_id is defined twice, one from transaction table another from entries: yeah can you just use a select() for this, for now ? Query in 0.4 really wasnt designed to be resused as a subquery. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL mass-delete
fine. thank you for your help. jean-philippe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Case insensitive queries
GK wrote:
I've been digging around the archives, web and source code to figure
case insensitive queries using SA, and am posting these notes:
(a) for review and comment, and
(b) to provide some possible hints for others who may follow
For me, a big benefit of using SA is that it insulates me from
database specifics. Ideally, I wanted a way to handle case
insensitive queries that would work across all supported databases, or
at least SQLite, MySQL and Postgres. Previous messages on this list
[1][2] suggest that won't happen before SA 0.5 (with no guarantee of
that, I guess) [3]. There was a possible answer at [4], but I didn't
like the potential implications for query performance.
[1]
http://groups.google.com/group/sqlalchemy/browse_frm/thread/ce87bd74fa4311ba/9c06644a94b358b6?lnk=gstq=case+insensitive+query#9c06644a94b358b6
[2]
http://groups.google.com/group/sqlalchemy/browse_frm/thread/cc8c775dd54b7b9c/18e059753d819455?lnk=gstq=case+insensitive+query#18e059753d819455
[3] http://www.sqlalchemy.org/trac/ticket/487
[4]
http://groups.google.com/group/sqlalchemy/browse_frm/thread/a7b1c325c267bba8/6ef14c5957b59395?lnk=gstq=case+insensitive+query#6ef14c5957b59395
So the solution I've adopted, and tested with SQLite, is to use a
TypeDecorator class, thus:
[[
class CI_String(sqlalchemy.types.TypeDecorator):
Defines a case insensitive string type using SQLite dialect
options
TODO: extend case insensitive specification to support other
databases
impl = sqlalchemy.String
def get_col_spec(self):
return super(CI_String,self).get_col_spec()+ COLLATE NOCASE
]]
Which I use in a Table definition thus:
[[
def defineAffyLocalTable(tablename, metadata):
Return SQLAlchemy table for Affymetrix local data entry.
table = Table(tablename, metadata,
Column('recordid',Integer, Sequence('recordid_seq'),
primary_key=True),
Column('probeid', CI_String(25), index=True),
Column('aly_mean',Float),
Column('can_mean',Float),
Column('comr_mean', Float),
Column('topi_mean', Float),
Column('zaa_mean',Float),
Column('red_e_mean', Float),
Column('description', Text),
)
return table
]]
Might something like this be a basis for a fairly simple SA-common
type that can be implemented appropriately for each database?
The generic character types in types.py should accept collate and
character set options. The only dialect with implementations for these
options currently is mysql, and some code the docstrings can be cherry
picked from msyql for use in types.py. DDL generation implementations
can go in for the other databases with collation support.
But that only gets partway to something like CI_String. SQLite is the
only database I know of with a workable across-the-board 'lower'
collation. I'm used to seeing collation tied to the character set of
the column storage, with more options than a simple binary upper/lower:
Latin1_General_BIN
Latin1_General_CI_AI_KS
Latin1_General_CS_AS_KS_WS
SQL_Latin1_General_CP1_CI_AS
utf8_general_ci
utf8_bin
utf8_unicode_ci
utf8_spanish_ci
utf8_swedish_ci
utf8_turkish_ci
The default character set used for column storage is usually unknown to
SA at DDL time, and, even if known, which collation to pick as
insensitive? Collations are database specific and don't always have a
'general' variant.
And then there are database such as Postgres that don't yet have
collation support and would need queries to be re-written to use lower(col).
I'd love to see easy insensitive query support in SA but I don't see an
obvious path forward.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Query filtering like isinstance()
On May 13, 2008, at 4:53 PM, Yannick Gingras wrote: If I want to query on containers and on area, I can simply do q = Container.query().filter(...) but, if I receive a query on Item and a base class, say either Item, Container or Area, how can I filter() my query to receive only the sub-items from this base class? filter on type_.in([area, container]) is one approach. Easier though is session.query(Container); it'll load from the join of items/ containers so you wouldn't get any non-Container objects. with_polymorphic() only speaks to what subclasses are eagerly loaded and will be available for filtering. Though its something to think about if base classes are not included in WP, should that change the base class; I'd say no, since you are reversing intent. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: DB Error handling in SQLAlchemy
On May 13, 2008, at 5:15 PM, Randall Nortman wrote: handling. I'm curious how SA handles this, especially transient errors where a retry is the right thing to do. For example, in several of my current sqlite apps (using bare DB-API), I get occasional locking errors (Database is locked), which happens sometimes with sqlite when there are concurrent processes accessing the DB. The right thing to do is retry, but it's a real pain to write *every* database operation with retry logic. Does SA handle retries in cases like this automatically? no, if a DBAPI error occurs, the error is thrown. We wrap the error in a DBAPI-neutral wrapper which has the same type as the underlying error (i.e. OperationalError, IntegrityError). Retries are definitely something a user-application needs to work out on its own; SQLAlchemy is not an application framework, just a library. (Admittedly, I think that more recent versions of pysqlite handle locked DBs better than the somewhat stale version I'm currently using, but I'm after the general principle rather than trying to solve that particular problem.) I dont think database is locked errors are all that common in practice. SQLite is supposed to block until the file is available. If you have a highly concurrent transactional application, SQLite is the wrong choice anyway. Aside from that, how in general does SA handle errors returned by the underlying DB? For most situations, you write your application such that they don't occur within normal use cases (i.e. dont use IntegrityError as a way to check if a row is present, select from the table explicitly instead; dont overuse locking and/or use a unit-of-work so that deadlocks don't occur). An error thrown by the DB should be considered an application failure. Does it automatically do a rollback and When using the ORM, if a flush() fails, a ROLLBACK is raised unconditionally, and the error is thrown. At that point, the end user must rollback the Session under normal usage (what Session calls transactional=True, or in 0.5 is called autocommit=False). This is actually not the most ideal behavior as it would be nice someday for a flush() to be able to make partial progress, raise the error without rolling back, and then be able to complete after the state of the Session has been corrected, its something we might do someday. However, some databases themselves prevent this from being doable; current versions of Postgres, for example, require that you ROLLBACK the transaction if an IntegrityError (the most common error) occurs - further statements will raise errors unconditionally until ROLLBACK. /or reconnect if necessary? Here's the one place we do something with a raised error. We do detect those errors which indicate a dropped connection, and in response to that we recycle the entire connection pool. The assumption here is that if a disconnect occurs, it is probably due to the database being restarted and it is harmless in any case for the connection pool being recycled. However, we still raise the error, albeit exactly once as opposed to once for each connection in the pool (this is a distinct advantage to the usage of a pool). In this case its virtually impossible for us to retry as when a conneciton is lost, an entire transaction is gone. SQLA is not capable of replaying the full script of SQL statements which occured within the transaction since it does not take on the complexity and overhead intrinsic in such an operation. But if you have a multithreaded web app, and you bounce the DB, you would get a minimum of failures. There is also a way to have connetions pre-ping before each usage (and recycle if connection lost), which is another way to prevent most disconnect errors. We have a connection pool events API which I believe some people have used to achieve this result. There's another option on the pool called pool_recycle=num of seconds which is used to deal with database client libraries (like MySQL's) that time out after a certain number of hours. So again, the emphasis here with reconnecting is to minimize the amount of errors thrown in the first place. If you're using the ORM layer is it able to still figure out what objects/fields are dirty if an error was encountered while trying to flush changes to disk? the unit of work executes its SQL within a transaction, but does not change the bookkeeping on any affected objects until the transaction is committed. In the 0.4 series, there is one exception to this, which is primary key and foreign key identifiers which are set during the transaction currently do not get rolled back on instances, which can create issues. In 0.4 we've generally recommended closing out the session when a flush error occurs (as a side note, this is the long-standing behavior of Hibernate as well which also cant rollback the state of
[sqlalchemy] How to specify NOLOCK queries in SA
Hi all. I'm running a Pylons App using SA, Elixir connecting to a SQL Server 2005 db via PYODBC. I'm getting a lot of database locks, the statements appear to be like this: SET FMTONLY ON select table1.id from table1 where 1=2 SET FMTONLY OFF I'm assuming that this is getting information about the model in preparation for another query (perhaps an insert). It returns the relevant column name, but no data , as 1 can obviously never equal 2. I would like to explicitly make this a NOLOCK select, to prevent these queries from generating locks, as these locks appear to be impacting our application performance server stability. Can anyone point me in the direction of where such a change could be made? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to specify NOLOCK queries in SA (mssql)
On May 13, 2008, at 9:46 PM, BruceC wrote: Hi all. I'm running a Pylons App using SA, Elixir connecting to a SQL Server 2005 db via PYODBC. I'm getting a lot of database locks, the statements appear to be like this: SET FMTONLY ON select table1.id from table1 where 1=2 SET FMTONLY OFF I cant locate the string FMTONLY within the MSSQL dialect at all. So this is not something SQLA is issuing. I would like to explicitly make this a NOLOCK select, to prevent these queries from generating locks, as these locks appear to be impacting our application performance server stability. Can anyone point me in the direction of where such a change could be made? our MSSQL devs might be able to help here but also you might want to see what the source of the locking is (i.e. some (Py)ODBC thing); SQLA is not issuing any locking. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: DB Error handling in SQLAlchemy
What I forgot to mention in all that, is that SQLA also supports SAVEPOINT very strongly. Using a SAVEPOINT-capable database opens up your choices for being able to flush(), hit an error, and then retry what you were doing, without a rollback of the overarching transaction needed. We call this a begin_nested(). 0.5 makes these very easy as when you issue begin_nested(), the session is synchronized with the DB transaction beforehand, so if an IntegrityError or such raises at that point, you just issue a rollback() straight to the last SAVEPOINT, the state of the session expires back to the last flush, and you're good to continue. So if you like to try things again this is a practice that should be considered. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
