[sqlalchemy] sorting a list of tables from a database based on their dependency order
I'm doing this: sengine = create_enging(blah..blah) smeta = MetaData(bind=sengine) meta.reflect(sengine) tables = smeta.tables.keys() to get a list of tables in a database, but unfortunately the table list are not in their dependency order. For example: A is depending on B (has a foreign key reference to B), B is depending on C, the correct table list should be [A,B,C]. Yet the smeta.tables.keys() will only return a unsorted list. Is there an API or a code snippet to show me how to sort the returned list on their dependency order ? I have this dumb code snippet,which I think is really inefficient. sorted_table = [] #=== # sort all table list on their dependency order #=== while len(sorted_table) != len(tables): for table_name in tables: if table_name in sorted_table: continue table = Table(table_name, smeta, autoload=True) if len(table.foreign_keys) == 0: sorted_table.append(table_name) else: for foreign_key in table.foreign_keys: if foreign_key.target_fullname.split(.)[0] in sorted_table: pass else:break else: sorted_table.append(table_name) Thanks Regards Tony -- 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] sorting a list of tables from a database based on their dependency order
On 07/11/2011 02:37 PM, Tony wrote: I'm doing this: sengine = create_enging(blah..blah) smeta = MetaData(bind=sengine) meta.reflect(sengine) tables = smeta.tables.keys() to get a list of tables in a database, but unfortunately the table list are not in their dependency order. For example: A is depending on B (has a foreign key reference to B), B is depending on C, the correct table list should be [A,B,C]. Yet the smeta.tables.keys() will only return a unsorted list. Is there an API or a code snippet to show me how to sort the returned list on their dependency order ? I have this dumb code snippet,which I think is really inefficient. sorted_table = [] #=== # sort all table list on their dependency order #=== while len(sorted_table) != len(tables): for table_name in tables: if table_name in sorted_table: continue table = Table(table_name, smeta, autoload=True) if len(table.foreign_keys) == 0: sorted_table.append(table_name) else: for foreign_key in table.foreign_keys: if foreign_key.target_fullname.split(.)[0] in sorted_table: pass else:break else: sorted_table.append(table_name) Thanks Regards Tony SQLAlchemy provides this via smeta.sorted_tables. -Conor -- 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] with_comment() ?
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 ? Cheers Tarek -- 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] hybrid, relationships and inheritance.
Hi Michael, thanks for your reply. I did get it working with the @property/hybrid_property, but it turns out what I really needed was a custom collection class. At least, that seems to be working in a cleaner fashion. I'm still fumbling around in the dark a little bit, so we'll see how it goes. SQL Alchemy keeps surprising me with features, it's very cool. Cheers, James. Assuming you don't need that (class level behavior), you don't really need @hybrid_property either. You can just use Python's standard @property. If you *did* want that, it would be a little tricky, probably would need a custom comparator. -- 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] Can't get sqlalchemy to backup or restore MSSQL database
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] Re: Can't get sqlalchemy to backup or restore MSSQL database
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.
