[sqlalchemy] Migrating databases: Alembic vs. Liquibase, Flyway, ??
I'm gradually working toward getting serious about controlled DB migration (version control for DBs), and have been following the Alembic announcements with interest. (We're already using SA successfully in a Twisted environment, just making sure that all DB access is encapsulated in deferreds.) Recently, I came across mention of two projects that look similar: Liquibase (http://www.liquibase.org/index.html) and Flyway (http://flywaydb.org/). Both of them look to be based on Java, but that aside, I'd be interested to know how they compare in terms of features, maturity, etc. Thanks for any info, -- Don Dwiggins Advanced Publishing Technology -- 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] Deadlock error in mssql DB
I've been successfully using SA 0.8.0 under Windows with SQL Server 2008
server. However, the following occurred recentlywhen starting up the
application (but only once -- restarting succeeded):
File Subhandler.pyo, line 138, in getTable
File sqlalchemy\schema.pyo, line 333, in __new__
File sqlalchemy\schema.pyo, line 397, in _init
File sqlalchemy\schema.pyo, line 425, in _autoload
File sqlalchemy\engine\base.pyo, line 1603, in run_callable
File sqlalchemy\engine\base.pyo, line 1126, in run_callable
File sqlalchemy\engine\default.pyo, line 258, in reflecttable
File sqlalchemy\engine\reflection.pyo, line 463, in reflecttable
File LowerCaseInspector.pyo, line 34, in get_foreign_keys
File sqlalchemy\engine\reflection.pyo, line 321, in get_foreign_keys
File string, line 1, in lambda
File sqlalchemy\engine\reflection.pyo, line 49, in cache
File sqlalchemy\dialects\mssql\base.pyo, line 1050, in wrap
File sqlalchemy\dialects\mssql\base.pyo, line 1059, in _switch_db
File sqlalchemy\dialects\mssql\base.pyo, line 1458, in get_foreign_keys
File sqlalchemy\engine\base.pyo, line 664, in execute
File sqlalchemy\engine\base.pyo, line 764, in _execute_clauseelement
File sqlalchemy\engine\base.pyo, line 878, in _execute_context
File sqlalchemy\engine\base.pyo, line 871, in _execute_context
File sqlalchemy\engine\default.pyo, line 320, in do_execute
DBAPIError: (Error) ('40001', '[40001] [Microsoft][ODBC SQL Server
Driver][SQL Server]Transaction (Process ID 60) was deadlocked on lock
resources with another process and has been chosen as the deadlock
victim. Rerun the transaction. (1205) (SQLExecDirectW)') u'SELECT
[C].[COLUMN_NAME], [R].[TABLE_SCHEMA], [R].[TABLE_NAME],
[R].[COLUMN_NAME], [REFERENTIAL_CONSTRAINTS_1].[CONSTRAINT_NAME],
[REFERENTIAL_CONSTRAINTS_1].[MATCH_OPTION],
[REFERENTIAL_CONSTRAINTS_1].[UPDATE_RULE],
[REFERENTIAL_CONSTRAINTS_1].[DELETE_RULE] \nFROM
[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [C],
[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [R],
[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] AS
[REFERENTIAL_CONSTRAINTS_1] \nWHERE [C].[TABLE_NAME] = ? AND
[C].[TABLE_SCHEMA] = ? AND [C].[CONSTRAINT_NAME] =
[REFERENTIAL_CONSTRAINTS_1].[CONSTRAINT_NAME] AND
[R].[CONSTRAINT_NAME] =
[REFERENTIAL_CONSTRAINTS_1].[UNIQUE_CONSTRAINT_NAME] AND
[C].[ORDINAL_POSITION] = [R].[ORDINAL_POSITION] ORDER BY
[REFERENTIAL_CONSTRAINTS_1].[CONSTRAINT_NAME], [R].[ORDINAL_POSITION]'
(u'dbo_corporatedata', u'dbo')
The statement (in Subhandler, line 138) that triggered this was:
return Table(tableName, self.meta, autoload=True)
where tableName is one of the existing tables in the DB, which is
always accessed at startup. Apparently, the statement is querying the
information schema, while (I guess) some other process had it locked.
I suppose Ican wrap some exception handling code around this, butI'm
curious why a Select would get into a deadlock.
--
Don Dwiggins
Advanced Publishing Technology
--
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/groups/opt_out.
[sqlalchemy] Re: Error message: ArgumentError: Could not determine dialect for 'mssql+pyodbc'.
On 4/9/13 2:41 PM, Michael Bayer wrote: and this used to work? if you're using 0.7, it uses a fairly primitive system based on __import__(). you'd want to make sure py2exe is putting every .py file under dialects/ into the final package. Thanks, I'll look into that. Yes, I'm using 0.7.9; should I upgrade to 0.8.0? On Apr 9, 2013, at 3:35 PM, Don Dwiggins ddwigg...@advpubtech.com mailto:ddwigg...@advpubtech.com wrote: I have an applicationusing SA that I distributein compiled form, using py2exe. This has been working well, but I recently ran into a problem. I do a create_engine with the string mssql+pyodbc://%(UserName)s:%(Password)s@%(DSN)s mssql+pyodbc://%%28UserName%29s:%%28Password%29s@%%28DSN%29s (the parameters are filled in using a % operator). This works without a problem when I run the source code. However, when I freeze it, I get ArgumentError: Could not determine dialect for 'mssql+pyodbc'. (Running on the same machine, with the same environment.) In case it might be relevant: I've recently moved my development from a Windows XP machine to a Windows 7 environment on a 64-bit machine. However, I'm using 32-bit Python, and generating 32-bit executables. -- Don Dwiggins Advanced Publishing Technology -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: Error message: ArgumentError: Could not determine dialect for 'mssql+pyodbc'.
On 4/10/13 8:27 AM, Don Dwiggins wrote: On 4/9/13 2:41 PM, Michael Bayer wrote: and this used to work? if you're using 0.7, it uses a fairly primitive system based on __import__(). you'd want to make sure py2exe is putting every .py file under dialects/ into the final package. Thanks, I'll look into that. Yes, I'm using 0.7.9; should I upgrade to 0.8.0? OK, I've tried several configurations of setup.py, and I've also upgraded to 0.8.0. I'm still getting the same error. In case it'll help, here's the traceback: 2013-04-11 00:19:28+0100 [-] File boot_service.py, line 185, in module 2013-04-11 00:19:28+0100 [-] File win32serviceutil.pyo, line 609, in HandleCommandLine 2013-04-11 00:19:28+0100 [-] File win32serviceutil.pyo, line 449, in DebugService 2013-04-11 00:19:28+0100 [-] File win32serviceutil.pyo, line 806, in SvcRun 2013-04-11 00:19:28+0100 [-] File RCAServer.pyo, line 444, in SvcDoRun 2013-04-11 00:19:28+0100 [-] File RCAServer.pyo, line 358, in setupReactor 2013-04-11 00:19:28+0100 [-] File Subscriber.pyo, line 59, in __init__ 2013-04-11 00:19:28+0100 [-] File Subhandler.pyo, line 330, in __init__ 2013-04-11 00:19:28+0100 [-] File Subhandler.pyo, line 37, in __init__ 2013-04-11 00:19:28+0100 [-] File sqlalchemy\engine\__init__.pyo, line 338, in create_engine 2013-04-11 00:19:28+0100 [-] File sqlalchemy\engine\strategies.pyo, line 50, in create 2013-04-11 00:19:28+0100 [-] File sqlalchemy\engine\url.pyo, line 123, in get_dialect 2013-04-11 00:19:28+0100 [-] sqlalchemy.exc.ArgumentError: Could not determine dialect for 'mssql+pyodbc'. In options, I set packages to ['sqlalchemy.dialects']. (I've also put that in includes, and tried ['sqlalchemy.dialects.mssql', 'sqlalchemy.dialects.mssql.pyodbc'] and got the same traceback. I'm willing to instrument url.py to get more information, but I'm not sure what to include. Any ideas or leads appreciated. On Apr 9, 2013, at 3:35 PM, Don Dwiggins ddwigg...@advpubtech.com mailto:ddwigg...@advpubtech.com wrote: I have an applicationusing SA that I distributein compiled form, using py2exe. This has been working well, but I recently ran into a problem. I do a create_engine with the string mssql+pyodbc://%(UserName)s:%(Password)s@%(DSN)s mssql+pyodbc://%%28UserName%29s:%%28Password%29s@%%28DSN%29s (the parameters are filled in using a % operator). This works without a problem when I run the source code. However, when I freeze it, I get ArgumentError: Could not determine dialect for 'mssql+pyodbc'. (Running on the same machine, with the same environment.) In case it might be relevant: I've recently moved my development from a Windows XP machine to a Windows 7 environment on a 64-bit machine. However, I'm using 32-bit Python, and generating 32-bit executables. -- Don Dwiggins Advanced Publishing Technology -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Error message: ArgumentError: Could not determine dialect for 'mssql+pyodbc'.
I have an applicationusing SA that I distributein compiled form, using py2exe. This has been working well, but I recently ran into a problem. I do a create_engine with the string mssql+pyodbc://%(UserName)s:%(Password)s@%(DSN)s (the parameters are filled in using a % operator). This works without a problem when I run the source code. However, when I freeze it, I get ArgumentError: Could not determine dialect for 'mssql+pyodbc'. (Running on the same machine, with the same environment.) In case it might be relevant: I've recently moved my development from a Windows XP machine to a Windows 7 environment on a 64-bit machine. However, I'm using 32-bit Python, and generating 32-bit executables. -- Don Dwiggins Advanced Publishing Technology -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Using Alembic to maintain versions of SPs and other objects?
It appears that, at least currently, Alembic only directly manages tables (although I guess one could include SQL code in the upgrade/downgrade functions to add/delete/change SPs, user defined types, functions, etc. Am I right in this? If so, do you have any plans or thoughts about versioning other schema objects? -- Don Dwiggins Advanced Publishing Technology -- 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] MS SQL: getting values of stored procedure OUT parameters
I'm writing code that I'd like to be dialect-free as much as possible; I'll be using at least MS SQL and PostgreSQL databases with equivalent schemas. In the MS DBs, there are stored procedures that have OUT parameters. The only way that I've found in some web searching to get the values of these parameters is to code direct SQL execs, followed by Selects of the output values. Has anyone come up with a better way? Thanks, -- Don Dwiggins Advanced Publishing Technology -- 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: Alembic question
Michael, Thanks for the prompt and helpful reply. Alembic does look promising for my application; I'll put it on my agenda (probably won't get to it soon, thought). It's a challenge that you have databases out in the wild that are not in sync. To the degree that those differences are important to the migration scripts you'll be writing, you'll want to get those systems in sync first - depending on the situation you might want to write scripts for each customer's database individually, each of which will have the job of getting that database into shape for the primary set of migration scripts. It's not quite as bad as I might have implied. There are only a few different rev levels, and I can recreate them locally with copies of the DBs. The differences tend to be a few tables and occasionally SPs and UDFs. We already have some control over this: I have a folder of scripts under version control that get applied when going to a new release (new/changed SPs, table changes, changes to the data in configuration tables), so I pretty much know what's in each DB by the rev number of the folder that was used. Again, thanks, -- Don Dwiggins Advanced Publishing Technology -- 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] Alembic question
I've looked at Migrate with a particular application in mind (that I won't be able to get started on for a while), and now I see that Alembic is its successor. So, hoping to be able to use Alembic, and maybe influence its development, a couple of questions: I have a large, fairly complex MSSQL schema, with hundreds of tables and SPs, views, triggers, etc. I'd like to put it under change management. Is this feasible with Alembic? If so, what would be involved in getting started? Also, I'm dealing with multiple customer databases using this schema, and not all at the same revision level. Would it be feasible to use Alembic in this kind of situation? To add a wrinkle: we're starting work to port the schema to PostgreSQL. Could we manage the MSSQL and PostgreSQL implementations in parallel with Alembic? Thanks, -- Don Dwiggins Advanced Publishing Technology -- 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: Gracefully recovering from MySQL failures in a connection pool
On 1/7/2011 7:52 AM, Michael Bayer wrote: the disconnect detection has to be implemented individually for each DBAPI for each backend. pymssql is not very frequently used so this would appear to be a missing message. We currently intercept: Error 10054, Not connected to any MS SQL server, Connection is closed not sure why you're getting something different - those values were added in March 2010, ultimately from an end-user, to support the new rewrite of pymssql. FYI (Having nothing to do specifically with pymssql or SA), I ran into a similar problem with a server containing a connection pool. While the server was idle, the DBA restarted the SQL Server. The next access through one of the pooled connections got an error message that was very much nonspecific. After some digging, I was unable to identify any way to distinguish this case from other errors. I wound up gving up on the pool, and creating a new connection for each request. -- Don Dwiggins Advanced Publishing Technology -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: How to cache SQLAlchemy results?
On 3/2/2010 10:37 AM, Michael Bayer wrote: Boda Cydo wrote: Hello, I have a problem that I don't know how to approach. Here it is: Suppose I have a mapped class `Article` and I have a mapped class `Author`. `Article.author` is a relation on `Author` that returns the author of the `Article`. How do I make `Article.author` to cache the value in memcached? Use the beaker caching recipe introduced at: http://www.sqlalchemy.org/docs/examples.html#module-beaker_caching For posterity, it looks like the URL has changed to http://www.sqlalchemy.org/docs/orm/examples.html?highlight=caching#beaker-caching. -- Don Dwiggins Advanced Publishing Technology -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: is sqlalchemy-migrate the right way to go?
This thread is of interest to me as well, although the problem I'm facing is somewhat different. Just to expand the space a little, here's my situation: - A legacy database schema with hundreds of tables and procedures. - An application that accesses the database, with occasional updates delivered to customers (not all of them will upgrade at once) -- each update needs to update the database as well as part of the installation (with live data existing in the database, of course). - Customers may add some of their own tables and procs, and have custom versions of some standard procs. (Maintaining those is the customer's problem, although we do help.) To bring some sanity to this, I've created a folder hierarchy of scripts and some programs to apply them to create or update a DB (I call it the Standard Database). This hierarchy is under version control. Still, though, it's too unwieldy to distribute and manage, and doesn't solve all the problems. Currently, we just have a folder of scripts associated with each update and run them against the customer's DB. This leaves us with the duplicate script problem, of course; sigh. I'd like to turn the Standard Database into an application that would do the kinds of things being discussed here, in particular upgrading a DB as appropriate, while respecting both the live data and local modifications. Part of this would be something like a DB diff that could let the user know what needs to be updated. Just a brain dump at this point, -- Don Dwiggins Advanced Publishing Technology -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: David Bolen on SA and Twisted
David Bolen wrote: ... nice clear explanation snipped ... Basically the granularity of the sharing of the database thread (and SA's connection to the database) is that of the callable you pass to the database thread to execute. As long as you implement those callables so they can run independently, you should be fine. Make sense? Excellent sense; thanks much. -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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] David Bolen on SA and Twisted
I'm considering using the SA SQL expression facility in a Twisted server, replacing some SQL creation code that's vulnerable to SQL injection attacks. Doing some exploration on the intersection of Twisted and SA, I came across a message by David Bolen in February of 2007, describing a simple database class that contained a background thread for execution. I'd like to know if that work, or some successor of it, has proven useful and may be worth adopting. (I've looked at sAsync, but it appears that the site it was hosted on, foss.eepatents.com, has gone on to that great cloud in the sky.) Thanks, -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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] ResultProxy: nextset?
I've started playing somewhat seriously with the SQL expression subset of SA as a replacement for some direct SQL code (and as a possible first step to heavier use of SA). As part of this, I took a query containing several selects, which naturally returns several result sets. In my current code (using pyodbc), I use cursor.nextset to collect all the results. (I'm collecting several lists that will be used to populate combo boxes in the front end. I could use several queries, but I thought why make multiple trips to the DB?) I tried this with SA, using text(), putting the result object into a variable. I was able to fetchall() to get the first set, but the object lacks a nextset. Fooling around, I found that I could do result.cursor.cursor.nextset(), but attempting to fetchall dies; apparently, the underlying cursor has been closed. I have no specific request at this point, except to suggest an extension to ResultProxy to handle this part of the DBAPI cursor API as well. For my purposes, I'll either live with multiple SA execute's or just drop down to pyodbc for this. Thanks for listening, -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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: David Bolen on SA and Twisted
David, thanks for the quick reply. Well, the server using it, in a slightly modified version from that message, remains in production, and has been continuously since July of 2007. So it's certainly worked for its intended purpose for me - that is, offloading the SA database I/O to a dedicated thread to avoid any possible blocking in the main Twisted reactor thread. That's good to hear. Probably the biggest change between my message and putting it in production was to stop using the ORM. Yes, I've read other cautionary tales about mixing Twisted and the ORM. Fortunately, I don't need it for my immediate needs. I always considered it a solution to a very specific usage pattern that worked for my particular case, but not necessarily something generalized enough for inclusion elsewhere. It's pretty brute force; no ORM support (well, or be very careful if using the ORM), and each operation queued to the database thread needs to be self-contained (since ops from different twisted deferred chains could interleave over the same database connection). But within those constraints it works just fine. Hmmm, Could you elaborate a bit about the self-contained? I do have some cases where I fire off a deferredList with several (independent) queries in it. Might that be problematic? As you can see from that older message, there's not too much involved. It's basically a standard worker thread pattern using queues, with only a single worker thread, some knowledge about the SA engine and the use of Twisted Deferreds to return the result. Yes, it is nice and simple and clean. It shouldn't be hard to work with, and extend if needed. Thanks again, -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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: Replacing existing object with a changed copy
Marcin Krol wrote: if you're looking for state between requests you can use an HTTP session for that *SMACK* forehead... Right.. Mental block.. and lightweight objects are great for those since they are easily serializable and use minimal space. Im a little confused, did you originally intend to persist state in the database between requests ? Well sort of - I wanted to store the 'throwaway' object in db, I didn't think of using http session for that purpose. you said you didn't want to call commit ? I don't want to call commit on original object until user presses Save. It sounds like your user is doing the editing in a JS-powered app in the browser. Would it work for you to leave the under edit version in the browser until the user presses Save (in session cookies and/or DOM objects)? For one thing, this will reduce the HTTP traffic, since there's only the one load of the original data, and one save request. -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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: sqlalchemy and json
alex wrote:
ok, and what about {1:{id:1,name:Alex},2:
{id:2,name:Nick}} format?
I try to
i=0
result = list()
for row in session.query(...).all():
d = row.__dict__.copy()
del d['_sa_instance_state']
result.append(i)
result.append(d)
i = i + 1
but it gives
{ 1, {id:1,name:Alex}, 2, {id:2,name:Nick}}
It's giving you just what you ask for: for every row, append two things
to the list: an integer followed by a dictionary for the row. (I'm
dubious about the outer curly braces, though; when I try a similar chunk
of code, I get [...].)
If you want a dictionary whose keys are the values of i, define
result initially as result = {}, then in the loop, replace the
appends by result[i] = d. If you just want a list of the row
dictionaries, drop all the statements involving i.
--
Don Dwiggins
Advanced Publishing Technology
--~--~-~--~~~---~--~~
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: MSSQL Reflection Error
Greg wrote: I think I found one that might help. Where can I upload this HTML file it generated? In case you don't get a better answer: open the file in your browser, then cut paste the text the browser displays (or take a screenshot of the browser window and upload that as a gif or jpg). -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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: MSSQL Reflection Error
Greg wrote: Unfortunately, I'm connecting remotely via iODBC and do not have said tools. I'm running Ubuntu Linux, if you know of any way to get the same thing prettied up, let me know and I will. Ahh, sorry; I've never had occasion to work with MSSQL through Linux. Maybe the best approach would be to use a Python shell to run the sp_columns proc and format the results yourself. (Or, you might check whether there are already some decent Linux GUI tools like Query Analyzer.) FWIW, -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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: MSSQL Reflection Error
Greg wrote: As a disclaimer, this is a legacy system in which I have no control over, so what you are about to see is pretty disturbing :) http://pastebin.com/m10d49ac1 The formatting is pretty crazy, I'm hoping you can make use of this. If you have the MSSQL tools handy, try this: - Open Query Analyzer, connect to the server - Press F8 to get the Object Browser (unless it's already open - Go to the database and table in question. - Right click on the table name - Select Script object to new window as; in the submenu choose Create You should have a reasonably formatted table creation script, including indices and FKs. HTH, -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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: sAsync in complex project
zipito wrote: Good day community. I don't know whether it is a good place to asc. Probably as good as any. You might try on the Twisted mailing list (or newsgroup gmane.comp.python.twisted) as well. Happy hunting, -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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: Reflecting tables with foreign keys
Michael Bayer wrote: hi don - heres a script using SQLite which illustrates how the foreign key reflection works. This same sort of thing should be working on MS-SQL as well but I dont have access to an MS-SQL server here to test. If the example below is not working for MS-SQL, please file a trac ticket - we have some MS-SQL developers who can take a look. OK, thanks. I tried it with MySQL, and it worked fine, so it does seem to be mssql-specific. I've filed Ticket #979. (I can work around this without too much trouble.) # reflect an entire DB meta3 = MetaData(engine) meta3.reflect() This worked on a large mssql DB -- took several minutes to autoload 760 tables and views. I don't think I'll be doing that very often. 8^) -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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] Avoiding reflection (was: Re: Reflecting tables with foreign keys)
Rick Morrison wrote: Ugg, I am not a big table reflection fan:-( I am in the middle of readying a presentation, so unless someone else wants to jump on this, I'll take a look at this later on this week, along with integrating a reflection speed-up patch I remember from a while back. Thanks. I'm willing to use something other than reflection, if there's a good way I can avoid having to duplicate my schema (or significant chunks of it) in SA declarations. I'd like to stick to the DRY principle if at all possible. (I'm dealing with a legacy DB, and can't really take SA declarations as ground truth.) OTOH, I have the schema scripted out in some SQL files (for configuration management purposes). If it turns out to be worth the effort, I can imagine writing a script to parse them to construct the equivalent SA Table declarations and keep them up to date with schema changes. Any ideas along these lines? -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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] Reflecting tables with foreign keys
I'm running SA 0.4.3 on Win XP, accessing a MSSQL database using pyodbc, and I want to autoload some table definitions. In the Metadata documentation, I read: Note that if a reflected table has a foreign key referencing another table, the related Table object will be automatically created within the MetaData object if it does not exist already. This doesn't seem to work. When I reflect a table with FKs, it comes in OK, and the metadata seems to know about the FK, but eval'ing 'referenced_table' in meta.tables (as in the example) returns False. Also, reflecting the referenced table without the autoload=True parameter doesn't work -- the table I get is empty (but it does come in OK if I use autoload). I also tried using meta.table_iterator, and got a message like Could not find table xxx with which to generate a foreign key. The table its asking for is indeed referenced, and if I autoload it (and all the other referenced tables), the iterator works fine. I can understand that it might not be a good idea, when autoloading a table, to try to bring in the entire FK tree with it; in my database, doing that on some tables would bring in several dozen other tables. I'm really just trying to reconcile what I read in the docs with how the code works. Thanks for any good words, -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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: executing stored procedure which returns rows
Rick Morrison wrote: Only rarely is there only one way to do something in MSSQL ;-) Stored procedures can also be called simply by name, omitting the EXEC: EXEC procedure_foo parms or procedure_foo parms True, as long as the call is the first statement in the batch; otherwise, you need the exec. and I believe they can also be called from within a subquery: select * from (procedure_foo) No, but mssql has the concept of table-valued user defined function, so you could have something like select * from dbo.foo(@var) -- think of it as a parameterized view. I agree that something like the returns_results hint might be a good way to go. -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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: schema changes
[EMAIL PROTECTED] wrote: This is getting into a big area: the problem of version control/configuration management for databases its not any bigger than any other configuration management of something structured that is deployed in the field... as long it consists of pieces and these pieces can go/combine wrong... I found it bigger because I couldn't find any ready-made tools for DB versioning (let along diff'ing), and that in a DB, there's a mix of structural elements and data elements that need to be sorted out (it's kind of like doing a version upgrade on a running program without disturbing the program's state 8^). If I've missed something, and this problem has been well and completely solved, I'd be delighted to hear of it. -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
