Re: [sqlalchemy] PostgreSQL 9.1 on the horizon, cool new stuff
Thanks for the 'heads-up' Eric :-) ! Nothing to see here, move right along ! Except... Couple of interesting additions coming up in PostgreSQL 9.1 (still in beta) for anyone who's interested. Release notes: http://developer.postgresql.org/pgdocs/postgres/release-9-1.html A couple of selected items I found of interest: * New support for CREATE UNLOGGED TABLE -- a new type of table that is sort of in between temporary tables and ordinary tables. They are not crash-safe as they are not written to the write-ahead log and are not replicated if you have replication set up, but the tradeoff is they can be written to a lot faster. Could use these to speed up testing, or in other non-production scenarios where crashproofness is not a concern. * New support for Synchronous replication -- primary master waits for a standby to write the transaction information to disk before acknowledging the commit. This behavior can be enabled or disabled on a per-transaction basis. Also a number of new settings related to keeping a 'hot standby'. * They added a true serializable transaction isolation level. Previously, asking for serializable isolation guaranteed only that a single MVCC snapshot would be used for the entire transaction, which allowed certain documented anomalies. The old snapshot isolation behavior will now be accessible by using the repeatable read isolation level. --This one might be particularly interesting for SQLAlchemy-- * INSERT, UPDATE, and DELETE will now be allowed in WITH clauses; these commands can use RETURNING to pass data up to the containing query. While not strictly necesary, this can improve the clarity of SQL emitted by eliminating some nested sub-SELECTs. There is other cool stuff you can accomplish with this such as deleting rows from one table according to a WHERE clause inside of a WITH...RETURNING, and inserting the same rows into another table in the same statement. The recursive abilities of WITH statements can also be used now to perform useful maneuvers like recursive DELETEs in tree structures (as long as the data-modifying part is outside of the WITH clause). * New support for per-column collation settings (yawn... but someone somewhere needs this while migrating or something) * New support for foreign tables -- allowing data stored outside the database to be used like native postgreSQL-stored data (read-only). * Enum types can now be added to programmatically (i don't know if they can be removed from) via ALTER TYPE * Added CREATE TABLE IF NOT EXISTS syntax -- seems like SA's DDL machinery might want to use that in the checkfirst=True case to eliminate the separate check operation? A minor matter, but nice. * Added transaction-level advisory locks (non-enforced, application- defined) similar to existing session-level advisory locks. * Lots more (a dump + restore will be required between 9.0 and 9.1) -- 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. -- 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] Re: Can't get sqlalchemy to backup or restore MSSQL database
The database is part of a desktop accounting application running on
Windows. I'm writing a program to interface with it, to automate
adding documents.
The idea was that it would be run once a month to add a bunch of
invoices. The backup would run before this operation, in case
something unexpected goes wrong.
Reducing the scope of the program, and dealing with backups separately
does sound like a better idea.
On Jul 12, 7:44 am, Eric Ongerth ericonge...@gmail.com wrote:
I think more common practice is just to use shell scripts (whether in
scheduled tasks / cron jobs or manually) for backup. But I don't know
MSSQL specifically. I just have rarely heard of anyone trying to
accomplish their backup with SQLAlchemy as part of the chain of
command.
On Jul 11, 2:06 pm, Maciej Filip Szkodziñski
maciej.szkodzin...@gmail.com wrote:
Hello,
I'm trying to backup a database running on SQL Server 2008 Express.
I'm using pyodbc as the driver.
This backup code executes happily, however no file is written to the
provided path. I've also tried placing an empty file in the path, and
only 2KB of data gets written to it.
eng = create_engine(mssql+pyodbc://%s:%s@%s % (uid, pwd, server))
eng.execute('BACKUP DATABASE test TO DISK=?',
backupFilePath)
# sqlalchemy.engine.base.ResultProxy object at 0x015868F0
os.path.isfile(backupFilePath)
# False
I am able to backup the database with the same parameters in 'bare'
pyodbc.
Here's a more verbose version of both, sqlalchemy and pyodbc, backup
code:http://pastebin.com/6x1RRTqz
I've also tried restoring an existing backup with sqlalchemy. Again, I
get the ResultProxy, but the newly 'restored' database is stuck in
perpetual 'Restoring...' state, and trying to use it results in:
# Database 'test' cannot be opened. It is in the middle of a restore.
I had a similar problem with bare pyodbc, and googling suggested that
this loop is required for the backup/restore operation to continue and
finish:
while backupCursor.nextset():
pass
where backupCursor is the one returned by execute('BACKUP...').
Is there a different way of doing backups via sqlalchemy, or some way
around this, or should I stick with bare pyodbc for backups?
--
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] relationship problem
Hello,
I am trying to use sqlalchemy with oracle, here is my code:
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, mapper, relationship
class Activite(object):
pass
class Famprod(object):
pass
engine = create_engine('oracle://login/paswd@db', echo=True)
metadata = MetaData(engine)
tActivite = Table('ACTIVITE', metadata,
autoload=True)
mapper(Activite, tActivite)
tFamprod = Table('FAMPROD', metadata,
autoload=True)
mapper(Famprod, tFamprod)
Famprod.activite = relationship(Activite)
Session = sessionmaker(bind=engine)
session = Session()
famprod = session.query(Famprod).get((ED, 15))
print famprod.activite
and i get this error:
AttributeError: 'RelationshipProperty' object has no attribute
'parent'
The table famprod has a composite key, one of the key columns is the
key of activite.
Is there something wrong with my code ?
I have tried to manually define the tFamprod's keys and foreign key
without succes.
Thank you.
--
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] Re: Can't get sqlalchemy to backup or restore MSSQL database
On Jul 12, 2011, at 8:27 AM, Maciej Filip Szkodziński wrote:
The database is part of a desktop accounting application running on
Windows. I'm writing a program to interface with it, to automate
adding documents.
The idea was that it would be run once a month to add a bunch of
invoices. The backup would run before this operation, in case
something unexpected goes wrong.
Reducing the scope of the program, and dealing with backups separately
does sound like a better idea.
engine.execute() is equivalent to:
conn = dbapi.connect(...)
cursor = conn.cursor()
cursor.execute(statement)
possibly call conn.commit() here if the statement qualifies for autocommit
return cursor via ResultProxy()
cursor.close()/conn.close() when ResultProxy is closed, automatic if no
cursor.description was present.
your examples with pyodbc indicate pyodbc is a little conflicted how to handle
this. The nonstandard conn.execute() call seems to fail if Pyodbc's
autocommit (note this is entirely distinct from the SQLAlchemy engine
autocommit feature) is not enabled, yet your SQLAlchemy script didn't throw
this error when using cursor.execute() (where again the pyodbc autocommit flag
remains at false unless you modified it yourself).
If the commit call is required then you'd need to be using
engine.connect().execution_options(autocommit=True).execute(my statement).
The BACKUP command doesn't trigger autocommit, so I'm not sure how your SQL log
ended up generating a COMMIT statement.
On Jul 12, 7:44 am, Eric Ongerth ericonge...@gmail.com wrote:
I think more common practice is just to use shell scripts (whether in
scheduled tasks / cron jobs or manually) for backup. But I don't know
MSSQL specifically. I just have rarely heard of anyone trying to
accomplish their backup with SQLAlchemy as part of the chain of
command.
On Jul 11, 2:06 pm, Maciej Filip Szkodziñski
maciej.szkodzin...@gmail.com wrote:
Hello,
I'm trying to backup a database running on SQL Server 2008 Express.
I'm using pyodbc as the driver.
This backup code executes happily, however no file is written to the
provided path. I've also tried placing an empty file in the path, and
only 2KB of data gets written to it.
eng = create_engine(mssql+pyodbc://%s:%s@%s % (uid, pwd, server))
eng.execute('BACKUP DATABASE test TO DISK=?',
backupFilePath)
# sqlalchemy.engine.base.ResultProxy object at 0x015868F0
os.path.isfile(backupFilePath)
# False
I am able to backup the database with the same parameters in 'bare'
pyodbc.
Here's a more verbose version of both, sqlalchemy and pyodbc, backup
code:http://pastebin.com/6x1RRTqz
I've also tried restoring an existing backup with sqlalchemy. Again, I
get the ResultProxy, but the newly 'restored' database is stuck in
perpetual 'Restoring...' state, and trying to use it results in:
# Database 'test' cannot be opened. It is in the middle of a restore.
I had a similar problem with bare pyodbc, and googling suggested that
this loop is required for the backup/restore operation to continue and
finish:
while backupCursor.nextset():
pass
where backupCursor is the one returned by execute('BACKUP...').
Is there a different way of doing backups via sqlalchemy, or some way
around this, or should I stick with bare pyodbc for backups?
--
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.
--
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] relationship problem
mik wrote:
Hello,
I am trying to use sqlalchemy with oracle, here is my code:
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, mapper, relationship
class Activite(object):
pass
class Famprod(object):
pass
engine = create_engine('oracle://login/paswd@db', echo=True)
metadata = MetaData(engine)
tActivite = Table('ACTIVITE', metadata,
autoload=True)
mapper(Activite, tActivite)
tFamprod = Table('FAMPROD', metadata,
autoload=True)
mapper(Famprod, tFamprod)
Famprod.activite = relationship(Activite)
Session = sessionmaker(bind=engine)
session = Session()
famprod = session.query(Famprod).get((ED, 15))
print famprod.activite
and i get this error:
AttributeError: 'RelationshipProperty' object has no attribute
'parent'
The table famprod has a composite key, one of the key columns is the
key of activite.
Is there something wrong with my code ?
I have tried to manually define the tFamprod's keys and foreign key
without succes.
Thank you.
I think your problem is here:
mapper(Famprod, tFamprod)
Famprod.activite = relationship(Activite)
You can't add relationship properties to mapped classes, unless they
were set up with the declarative extension
(http://www.sqlalchemy.org/docs/orm/extensions/declarative.html).
Without declarative, the code should look something like this:
mapper(Famprod, tFamprod, properties={
'activite': relationship(Activite),
})
(http://www.sqlalchemy.org/docs/orm/relationships.html#one-to-many)
Hope that helps,
Simon
--
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] with_comment() ?
On Jul 11, 2011, at 5:46 PM, Tarek Ziadé wrote: Hello We're using with_hint() to add comments to sql queries, but that'll be work only with selects. I guess I can do a compiler extension to be able to add a /* comment */ in my queries, but I was wondering: unless I missed something, wouldn't it be useful to have a with_comment() method to be able to add a comment to a select, insert or update statement ? It would be useful yes, as well as available on Query as we've had some requests for this. Assuming the format of SQL comments doesn't change much across backends, it would be an easy patch (with unit tests of course). Still unanswered is what commenting would be desirable for INSERT/UPDATE/DELETE emitted during a flush, if any. -- 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] with_comment() ?
On Tue, Jul 12, 2011 at 4:46 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Jul 11, 2011, at 5:46 PM, Tarek Ziadé wrote: Hello We're using with_hint() to add comments to sql queries, but that'll be work only with selects. I guess I can do a compiler extension to be able to add a /* comment */ in my queries, but I was wondering: unless I missed something, wouldn't it be useful to have a with_comment() method to be able to add a comment to a select, insert or update statement ? It would be useful yes, as well as available on Query as we've had some requests for this. Assuming the format of SQL comments doesn't change much across backends, it would be an easy patch (with unit tests of course). I'll give it a look then.. Still unanswered is what commenting would be desirable for INSERT/UPDATE/DELETE emitted during a flush, if any. I would say no in my use case. For us, the main reason to comment queries is to track them easily in the sql logs, even if the query text changes over time -- we don't rely on automatic queries, sessions etc, Cheers -- Tarek Ziadé | http://ziade.org -- 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] which is the current version for mission critical applications on production
Hi Krishnakant - 0.7.1 is the current stable production release which is where the focus of development also lies. 0.6 is in maintenance releases at this point. 0.7 has numerous behavioral and performance advantages over 0.6 and is already used in production environments. On Jul 9, 2011, at 3:22 PM, Krishnakant Mane wrote: Hello all. I have a very quick and short question. which is the current production release of sqlalchemy. I mean this in terms of performance and reliability. let me narrow down the choices as per my knowledge. is it 0.6 or 0.7? And if 0.6 then which minor version? Happy hacking. Krishnakant. -- 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. -- 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] correlate between ORM and non-ORM expression
Hi Michael et al, I can not find a way to express my query in SQLAlchemy. What I want to do is to load all ORM-mapped objects for which another query can not yield any result (not exists): select * from entry where not exists (select 1 from lookup where lookup.skipped_id = entry.id) For my application, the subquery is a bit more complicated (it's a join over two tables). In any case, I can not correlate the ORM query with the sql query. The attached example prints the following query (SQLAlchemy 0.6.8): SELECT entry.id AS entry_id, entry.content AS entry_content FROM entry WHERE NOT (EXISTS (SELECT 1 FROM lookup)) How can I correlate the subquery in this context? There is a bunch of correlate methods (Query.correlate, Select.correlate) but I do not really understand how to make use of it here. Do you have a hint for me? 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. from sqlalchemy import * from sqlalchemy.orm import * metadata = MetaData() entry_table = Table(entry, metadata, Column(id, Integer, primary_key=True), Column(content, String) ) lookup_table = Table(lookup, metadata, Column(skipped_id, Integer, ForeignKey(entry_table.c.id))) class Entry(object): pass mapper(Entry, entry_table) engine = create_engine(sqlite:///, echo=True) metadata.create_all(engine) Session = sessionmaker(engine) session = Session() for v in (1, 3, 7, 9): session.execute(lookup_table.insert().values(skipped_id=v)) session.query(Entry).filter( not_(exists(1, from_obj=lookup_table) .correlate(entry_table))).all()
[sqlalchemy] Re: Can't get sqlalchemy to backup or restore MSSQL database
Oops, I was trying it with
engine.connect().execution_options(autocommit=True).execute as well,
and didn't notice there was a difference in the echo when editing the
pastebin. There's of course no COMMIT at the end with eng.execute().
I've updated pastebin with both versions.
The behaviour doesn't change though. In both cases, if the file
doesn't exist, it doesn't exist after finishing the BACKUP. If an
empty file already exists, _even without the COMMIT_, the server still
writes 2KB to it.
As for the autocommit error, the server complains about doing a backup
within a transaction. I'm guessing sqlalchemy's engine.execute()
doesn't automatically start a transaction, while pyodbc's
conn.execute() without autocommit does start a transaction.
I originally got the backup code from:
http://ryepup.unwashedmeme.com/blog/2010/08/26/making-sql-server-backups-using-python-and-pyodbc/
The author seems to have more knowledge on how SQL Server does
backups. To quote:
If you try to run a BACKUP via pyodbc, the cursor.execute() call starts and
finishes with no error, but the backup doesn’t get made. With help from
CubicWeb‘s post MS SQL Server Backuping gotcha, I learned that BACKUP and
RESTOREs over ODBC trigger some kind of asynchronous / multiple result set
mode.
I'm guessing sqlalchemy closes the cursor before the server can finish
the backup?
On Jul 12, 4:25 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 12, 2011, at 8:27 AM, Maciej Filip Szkodziński wrote:
The database is part of a desktop accounting application running on
Windows. I'm writing a program to interface with it, to automate
adding documents.
The idea was that it would be run once a month to add a bunch of
invoices. The backup would run before this operation, in case
something unexpected goes wrong.
Reducing the scope of the program, and dealing with backups separately
does sound like a better idea.
engine.execute() is equivalent to:
conn = dbapi.connect(...)
cursor = conn.cursor()
cursor.execute(statement)
possibly call conn.commit() here if the statement qualifies for autocommit
return cursor via ResultProxy()
cursor.close()/conn.close() when ResultProxy is closed, automatic if no
cursor.description was present.
your examples with pyodbc indicate pyodbc is a little conflicted how to
handle this. The nonstandard conn.execute() call seems to fail if Pyodbc's
autocommit (note this is entirely distinct from the SQLAlchemy engine
autocommit feature) is not enabled, yet your SQLAlchemy script didn't throw
this error when using cursor.execute() (where again the pyodbc autocommit
flag remains at false unless you modified it yourself).
If the commit call is required then you'd need to be using
engine.connect().execution_options(autocommit=True).execute(my statement).
The BACKUP command doesn't trigger autocommit, so I'm not sure how your SQL
log ended up generating a COMMIT statement.
On Jul 12, 7:44 am, Eric Ongerth ericonge...@gmail.com wrote:
I think more common practice is just to use shell scripts (whether in
scheduled tasks / cron jobs or manually) for backup. But I don't know
MSSQL specifically. I just have rarely heard of anyone trying to
accomplish their backup with SQLAlchemy as part of the chain of
command.
On Jul 11, 2:06 pm, Maciej Filip Szkodziñski
maciej.szkodzin...@gmail.com wrote:
Hello,
I'm trying to backup a database running on SQL Server 2008 Express.
I'm using pyodbc as the driver.
This backup code executes happily, however no file is written to the
provided path. I've also tried placing an empty file in the path, and
only 2KB of data gets written to it.
eng = create_engine(mssql+pyodbc://%s:%s@%s % (uid, pwd, server))
eng.execute('BACKUP DATABASE test TO DISK=?',
backupFilePath)
# sqlalchemy.engine.base.ResultProxy object at 0x015868F0
os.path.isfile(backupFilePath)
# False
I am able to backup the database with the same parameters in 'bare'
pyodbc.
Here's a more verbose version of both, sqlalchemy and pyodbc, backup
code:http://pastebin.com/6x1RRTqz
I've also tried restoring an existing backup with sqlalchemy. Again, I
get the ResultProxy, but the newly 'restored' database is stuck in
perpetual 'Restoring...' state, and trying to use it results in:
# Database 'test' cannot be opened. It is in the middle of a restore.
I had a similar problem with bare pyodbc, and googling suggested that
this loop is required for the backup/restore operation to continue and
finish:
while backupCursor.nextset():
pass
where backupCursor is the one returned by execute('BACKUP...').
Is there a different way of doing backups via sqlalchemy, or some way
around this, or should I stick with bare pyodbc for backups?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to
Re: [sqlalchemy] correlate between ORM and non-ORM expression
On Tue, 2011-07-12 at 17:47 +0200, Torsten Landschoff wrote: How can I correlate the subquery in this context? There is a bunch of correlate methods (Query.correlate, Select.correlate) but I do not really understand how to make use of it here. Okay, I found it: correlate does not automatically use the foreign keys to add a where clause for correlation. This is left to the caller. Adding .where(entry_table.c.id==lookup_table.c.skipped_id) to my example make it work. Thanks! 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] Re: Can't get sqlalchemy to backup or restore MSSQL database
On Jul 12, 2011, at 12:44 PM, Maciej Filip Szkodziński wrote: Oops, I was trying it with engine.connect().execution_options(autocommit=True).execute as well, and didn't notice there was a difference in the echo when editing the pastebin. There's of course no COMMIT at the end with eng.execute(). I've updated pastebin with both versions. The behaviour doesn't change though. In both cases, if the file doesn't exist, it doesn't exist after finishing the BACKUP. If an empty file already exists, _even without the COMMIT_, the server still writes 2KB to it. As for the autocommit error, the server complains about doing a backup within a transaction. I'm guessing sqlalchemy's engine.execute() doesn't automatically start a transaction, while pyodbc's conn.execute() without autocommit does start a transaction. DBAPI doesn't have a BEGIN, a connection is always in a transaction, unless a non-standard autocommit flag like that of PyODBCs is used. PyODBC should be starting a transaction the moment the first statement is emitted. There should not be a behavioral difference between conn.execute() (which is a non-standard convenience method specific to PyODBC) and cursor.execute() (the DBAPI method), though this is definitely possible. In any case this is well in the range of PyODBC quirks and you should check out the pyodbc site/mailing list/tracker for more details on this. If you try to run a BACKUP via pyodbc, the cursor.execute() call starts and finishes with no error, but the backup doesn’t get made. With help from CubicWeb‘s post MS SQL Server Backuping gotcha, I learned that BACKUP and RESTOREs over ODBC trigger some kind of asynchronous / multiple result set mode. I'm guessing sqlalchemy closes the cursor before the server can finish the backup? the two terms asynchronous multiple result set don't really go together here. multiple result sets refers to a cursor that has multiple results via cursor.nextset(). SQLA currently doesn't support this mode of usage. If SQLA's closing of the cursor is the issue here then just use the PyODBC connection directly.engine.raw_connection() or engine.connect().connection will give you one from the pool. If you flip any autocommit flags you'd want to set them back when complete, or alternatively remove the connection from the pool using connection.detach() (SQLAlchemy method added to the DBAPI connection). the asynchronous background process concept sounds a little more sketchy here, if that were the case I would think the BACKUP command has some way to check on the status of the operation before commit() occurs, but that's really not how SQL commands usually work - standard behavior is to block until complete. On Jul 12, 4:25 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jul 12, 2011, at 8:27 AM, Maciej Filip Szkodziński wrote: The database is part of a desktop accounting application running on Windows. I'm writing a program to interface with it, to automate adding documents. The idea was that it would be run once a month to add a bunch of invoices. The backup would run before this operation, in case something unexpected goes wrong. Reducing the scope of the program, and dealing with backups separately does sound like a better idea. engine.execute() is equivalent to: conn = dbapi.connect(...) cursor = conn.cursor() cursor.execute(statement) possibly call conn.commit() here if the statement qualifies for autocommit return cursor via ResultProxy() cursor.close()/conn.close() when ResultProxy is closed, automatic if no cursor.description was present. your examples with pyodbc indicate pyodbc is a little conflicted how to handle this. The nonstandard conn.execute() call seems to fail if Pyodbc's autocommit (note this is entirely distinct from the SQLAlchemy engine autocommit feature) is not enabled, yet your SQLAlchemy script didn't throw this error when using cursor.execute() (where again the pyodbc autocommit flag remains at false unless you modified it yourself). If the commit call is required then you'd need to be using engine.connect().execution_options(autocommit=True).execute(my statement). The BACKUP command doesn't trigger autocommit, so I'm not sure how your SQL log ended up generating a COMMIT statement. On Jul 12, 7:44 am, Eric Ongerth ericonge...@gmail.com wrote: I think more common practice is just to use shell scripts (whether in scheduled tasks / cron jobs or manually) for backup. But I don't know MSSQL specifically. I just have rarely heard of anyone trying to accomplish their backup with SQLAlchemy as part of the chain of command. On Jul 11, 2:06 pm, Maciej Filip Szkodziñski maciej.szkodzin...@gmail.com wrote: Hello, I'm trying to backup a database running on SQL Server 2008 Express. I'm using pyodbc as the driver. This backup code executes happily, however no file is written to the provided
Re: [sqlalchemy] which is the current version for mission critical applications on production
On 12/07/11 20:34, Michael Bayer wrote: Hi Krishnakant - 0.7.1 is the current stable production release which is where the focus of development also lies. 0.6 is in maintenance releases at this point. In that case will I have to change my code if I want to shift from 0.6 to 0.7? I use Pylons as my web application framework. In addition the major projecct I am working on is using sqlalchemy version 0.6.3 in its core engine. The core engine sends and recieves xml rpc messages. It then uses sqlalchemy to talk with the database in postgresql. I plan to use a lot of expression api so I will like to know overall what all changes will i have to make in my code which uses 0.6. Happy hacking. Krishnakant. -- 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] which is the current version for mission critical applications on production
On Jul 12, 2011, at 1:57 PM, Krishnakant Mane wrote: On 12/07/11 20:34, Michael Bayer wrote: Hi Krishnakant - 0.7.1 is the current stable production release which is where the focus of development also lies. 0.6 is in maintenance releases at this point. In that case will I have to change my code if I want to shift from 0.6 to 0.7? I use Pylons as my web application framework. In addition the major projecct I am working on is using sqlalchemy version 0.6.3 in its core engine. The core engine sends and recieves xml rpc messages. It then uses sqlalchemy to talk with the database in postgresql. I plan to use a lot of expression api so I will like to know overall what all changes will i have to make in my code which uses 0.6. Happy hacking. Krishnakant. if you're on 0.6, you'd move up to 0.6.8 to get the latest fixes and such, and you can stay on 0.6 for the time being. Moving to 0.7 requires little to no changes to calling code. But you would need to fully test your 0.6 application on 0.7 before moving into production. There are very few backwards incompatible changes overall, details at http://www.sqlalchemy.org/trac/wiki/07Migration -- 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. -- 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] which is the current version for mission critical applications on production
On 12/07/11 23:36, Michael Bayer wrote: On Jul 12, 2011, at 1:57 PM, Krishnakant Mane wrote: On 12/07/11 20:34, Michael Bayer wrote: Hi Krishnakant - 0.7.1 is the current stable production release which is where the focus of development also lies. 0.6 is in maintenance releases at this point. In that case will I have to change my code if I want to shift from 0.6 to 0.7? I use Pylons as my web application framework. In addition the major projecct I am working on is using sqlalchemy version 0.6.3 in its core engine. The core engine sends and recieves xml rpc messages. It then uses sqlalchemy to talk with the database in postgresql. I plan to use a lot of expression api so I will like to know overall what all changes will i have to make in my code which uses 0.6. Happy hacking. Krishnakant. if you're on 0.6, you'd move up to 0.6.8 to get the latest fixes and such, and you can stay on 0.6 for the time being. Moving to 0.7 requires little to no changes to calling code. But you would need to fully test your 0.6 application on 0.7 before moving into production. There are very few backwards incompatible changes overall, details at http://www.sqlalchemy.org/trac/wiki/07Migration So is 0.6.8 updated with the performance bennifits that we get in 0.7? Happy ahcking. Krishnakant. -- 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] which is the current version for mission critical applications on production
On Jul 12, 2011, at 2:30 PM, Krishnakant Mane wrote: On 12/07/11 23:36, Michael Bayer wrote: On Jul 12, 2011, at 1:57 PM, Krishnakant Mane wrote: On 12/07/11 20:34, Michael Bayer wrote: Hi Krishnakant - 0.7.1 is the current stable production release which is where the focus of development also lies. 0.6 is in maintenance releases at this point. In that case will I have to change my code if I want to shift from 0.6 to 0.7? I use Pylons as my web application framework. In addition the major projecct I am working on is using sqlalchemy version 0.6.3 in its core engine. The core engine sends and recieves xml rpc messages. It then uses sqlalchemy to talk with the database in postgresql. I plan to use a lot of expression api so I will like to know overall what all changes will i have to make in my code which uses 0.6. Happy hacking. Krishnakant. if you're on 0.6, you'd move up to 0.6.8 to get the latest fixes and such, and you can stay on 0.6 for the time being. Moving to 0.7 requires little to no changes to calling code. But you would need to fully test your 0.6 application on 0.7 before moving into production. There are very few backwards incompatible changes overall, details at http://www.sqlalchemy.org/trac/wiki/07Migration So is 0.6.8 updated with the performance bennifits that we get in 0.7? no it's not, by latest fixes i meant bugfixes that have been identified as back-portable to 0.7, which have been many, but generally do not include performance enhancements which are based on deeper architectural changes. -- 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.
