[sqlalchemy] Auto-generated Code?
Is there a way or lib to help us generating python* code *of all of the tables and relations automatically from a existing database? Thanks. --~--~-~--~~~---~--~~ 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: Auto-generated Code?
Dnia 2009-10-23, Pt o godzinie 15:00 +0800, Stone Puzzle pisze: Is there a way or lib to help us generating python code of all of the tables and relations automatically from a existing database? http://turbogears.org/2.1/docs/main/Utilities/sqlautocode.html Tomasz Jezierski Tefnet http://www.tefnet.pl --~--~-~--~~~---~--~~ 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: Auto-generated Code?
I have tried it, it's great, thanks! On Fri, Oct 23, 2009 at 3:19 PM, Tefnet Developers - Tomasz Jezierski develop...@tefnet.pl wrote: Dnia 2009-10-23, Pt o godzinie 15:00 +0800, Stone Puzzle pisze: Is there a way or lib to help us generating python code of all of the tables and relations automatically from a existing database? http://turbogears.org/2.1/docs/main/Utilities/sqlautocode.html Tomasz Jezierski Tefnet http://www.tefnet.pl --~--~-~--~~~---~--~~ 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: not expected generated update query values
Thanks a lot, Michael!
On Oct 23, 4:09 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Oct 22, 2009, at 3:26 PM, sector119 wrote:
Something strange, Michael.. All bindparams are different. Compliller
should not add to SET all params if values() has bindparam args, no?
where() have:
bindparam('commit_date'), bindparam('serial'), bindparam
('office_id')
values() have:
bindparam('rollback_date'), bindparam('rollback_time'), bindparam
('rollback_user_id')
OK i have this fully repaired in trunk r6428. if you use a bindparam
() with a column name, it will be honored as is and won't be
implicitly added to the SET or VALUES clause of an update or insert.
All bindparams are different.
params={'commit_date':'2009-10-22', 'serial':1, 'office_id':1,
'rollback_date':'2009-10-22', 'rollback_time':'11:12:15',
'rollback_user_id':1, 'foobar':1, 'sum':111})
UPDATE transactions SET serial=%(serial)s, office_id=%(office_id)s,
sum=%(sum)
s, commit_date=%(commit_date)s, rollback_date=%(rollback_date)s,
rollback_time=%(rollback_time)s, rollback_user_id=%(rollback_user_id)
s
WHERE transactions.commit_date = %(commit_date)s AND
transactions.serial = %(serial)s AND transactions.office_id = %
(office_id)s
On 22 окт, 19:45, Michael Bayer mike...@zzzcomputing.com wrote:
sector119 wrote:
though likely cleaner to pass the exact set of parameters desired.
How to pass that params if I use bindparam at where() and values(),
but I don't want to update colums that are at where() clause, only
at
values() ?
if you are using bindparam() objects, you'd given them all distinct
names.
--~--~-~--~~~---~--~~
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] own compiler visit_update method
Hi!
Is it possible to easily add tables to FROM clause, now I use
following code to append additional table names but it look like a
hack..
at PGCompiler.visit_update() I see self.stack.append({'from': set
([update_stmt.table])}) where and how it's used, may be using this I
can append some elements to the FROM ?
def visit_update(self, update_stmt):
text = super(PGCompiler, self).visit_update(update_stmt)
if 'postgresql_from' in update_stmt.kwargs:
text = self._append_from(text, update_stmt)
return text
def _append_from(self, text, stmt):
return text.replace(' WHERE', ' FROM ' + string.join([table.name for
table in stmt.kwargs['postgresql_from']], ', ') + ' WHERE')
--~--~-~--~~~---~--~~
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 import ARRAY from dialects.postgresql
Is there something wrong with ARRAY type in SA 0.6? no dialects/ dialect/__init__.py has ARRAY at __all__ !? --~--~-~--~~~---~--~~ 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 import ARRAY from dialects.postgresql
It's on trunk. Elso can't import orm from sqlalchemy :/ On Oct 23, 6:00 pm, sector119 sector...@gmail.com wrote: Is there something wrong with ARRAY type in SA 0.6? no dialects/ dialect/__init__.py has ARRAY at __all__ !? --~--~-~--~~~---~--~~ 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 import ARRAY from dialects.postgresql
sector119 wrote: It's on trunk. Elso can't import orm from sqlalchemy :/ On Oct 23, 6:00 pm, sector119 sector...@gmail.com wrote: Is there something wrong with ARRAY type in SA 0.6? no dialects/ dialect/__init__.py has ARRAY at __all__ !? ARRAY type is only within postgresql. You'd have to import it from that dialect specifically: from sqlalchemy.dialects.postgresql import ARRAY --~--~-~--~~~---~--~~ 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: own compiler visit_update method
sector119 wrote:
Hi!
Is it possible to easily add tables to FROM clause, now I use
following code to append additional table names but it look like a
hack..
at PGCompiler.visit_update() I see self.stack.append({'from': set
([update_stmt.table])}) where and how it's used, may be using this I
can append some elements to the FROM ?
def visit_update(self, update_stmt):
text = super(PGCompiler, self).visit_update(update_stmt)
if 'postgresql_from' in update_stmt.kwargs:
text = self._append_from(text, update_stmt)
return text
def _append_from(self, text, stmt):
return text.replace(' WHERE', ' FROM ' + string.join([table.name for
table in stmt.kwargs['postgresql_from']], ', ') + ' WHERE')
we don't have FROM support in UPDATE. You want to use the compiler
extension to create this construct yourself:
http://www.sqlalchemy.org/docs/06/reference/ext/compiler.html
--~--~-~--~~~---~--~~
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: Saved Queries (or text representation of the SQL)
I would like to save a number of these in a database so size is
important (the serialized select() was somewhat large...) so I would
like to get the string representation of the raw SQL directly useable
by sqlalchemy if possible. As I have in my examples, the str(select)
doesn't seem directly useable as it is missing the parameters -
upper(host_status.site) = %(upper_1)s instead of upper
(host_status.site) = 'LCO' for example. Is there a way to get the
raw SQL text just as it is sent to the database and ready for reuse by
sqlalchemy (execute(text(SQLtext) )? Or do I have to construct my
own by doing string replaces on the parameters with the parameters as
found in .context? Seems like the raw SQL has to be available at some
point but don't know if the user has access to it.
Thanks!
Jeff
On Oct 22, 7:06 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Oct 22, 2009, at 6:33 PM, jeff wrote:
I would like to allow user's to save favorite queries in my Python
app. Is there a way to find out the SQL statement as a string that
can be then reused (e.g. Engine.execute(text(savedQueryText) ) )?
Or is there another solution to this need?
your best bet would be to serialize them using the serializer extension.
http://www.sqlalchemy.org/docs/05/reference/ext/serializer.html?highl...
the examples are ORM centric but you can use select() constructs too -
the session argument is optional.
otherwise if you just want the raw SQL string, just call str
(statement). the string SQL is less wieldy being sent back into
SQLAlchemy though.
I generate a select object to execute. The string representations
don't appear in a format that can be readily reused (or are missing
the parameter values):
str(query)
'SELECT host_status.host_name, host_status.version,
host_status.host_id, host_status.sys_uptime, host_status.host_uptime,
host_status.load_avg, count(%(param_1)s) AS CMs,
host_status.db_size, host_status.db_status, host_status.update_time
\nFROM host_status, cm_status \nWHERE upper(host_status.site) = %
(upper_1)s AND host_status.host_name = cm_status.host_name GROUP BY
host_status.host_name, host_status.version, host_status.host_id,
host_status.sys_uptime, host_status.host_uptime, host_status.load_avg,
host_status.db_size, host_status.db_status, host_status.update_time
ORDER BY host_status.host_name ASC'
str(self.execute(query))
2009-10-22 16:19:42,642 INFO sqlalchemy.engine.base.Engine.0x...67b0
SELECT host_status.host_name, host_status.version,
host_status.host_id, host_status.sys_uptime, host_status.host_uptime,
host_status.load_avg, count(%(param_1)s) AS CMs,
host_status.db_size, host_status.db_status, host_status.update_time
FROM host_status, cm_status
WHERE upper(host_status.site) = %(upper_1)s AND host_status.host_name
= cm_status.host_name GROUP BY host_status.host_name,
host_status.version, host_status.host_id, host_status.sys_uptime,
host_status.host_uptime, host_status.load_avg, host_status.db_size,
host_status.db_status, host_status.update_time ORDER BY
host_status.host_name ASC
2009-10-22 16:19:42,642 INFO sqlalchemy.engine.base.Engine.0x...67b0
{'param_1': 'cm_status.host_name', 'upper_1': 'LCO'}
'sqlalchemy.engine.base.ResultProxy object at 0x01D33F90'
Thanks!
--~--~-~--~~~---~--~~
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
Don Dwiggins d...@dondwiggins.net writes: 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? My guess is that your independent comment is essentially the same as my self-contained. The point I was making was that each time you delegate a request to the database thread, it queues up with other requests (which may be from different deferred chains) and is then executed FIFO from the queue. In this context, a request is the callable that will be executed within the database thread context, so it can can still have multiple individual SA database operations, all as part of the same request. From the SA perspective, everything executes in the single background thread via the same database connection and engine. So you don't want two separate requests to that thread to have any coupling - the simplest example of which would be them requiring that they both execute in the same transaction. You can't guarantee the integrity of that, at least with my current implementation, since some other request might hit the queue in between the original requests, and then get caught up in the transaction. So for example, (warning non-tested pseudo code), if you're using a deferredList to start and retrieve results from functions a and b, and assuming db is the SA database object, the following could in theory be risky, if the a_* or b_* callbacks were coupled in some way. def mainfunc(): d = deferredList([a(), b()]) return d def a(): d = db.run(a_first_func) d.addCallback(a_process_first_result) d.addCallback(db.run, a_second_func) d.addCallback(a_process_second_result) return d def b(): d = db.run(b_first_func) d.addCallback(b_process_first_result) d.addCallback(db.run, b_second_func) d.addCallback(b_process_second_result) return d That's because at the database layer, this could (and probably would in this example) execute as: a_first_func b_first_func a_second_func b_second_func One way to restructure this, depending on what the functions involved do, would be (I've kept the function calls separate just for clarity): def mainfunc(): d = deferredList([a(), b()]) return d def _a_dbop(): r1 = a_first_func() r2 = a_process_first_result(r1) r3 = a_second_func(r2) return a_process_second_result(r3) def a(): d = db.run(_a_dbop) return d def _b_dbop(): r1 = b_first_func() r2 = b_process_first_result(r1) r3 = b_second_func(r2) return b_process_second_result(r3) def b(): d = db.run(_b_dbop) return d Now the first_func/second_func's in each case always run together within a single database thread request. In some cases this can even simplify things since what used to be a deferred callback chain becomes inline code since you're already in a separate thread (eseentially like a deferToThread). Of course, the above may not be practical if the *_process_*_result functions need access to your Twisted code, in which case you're just back to being careful not to require a particular execution order among the various database callables. In practice I haven't found this to be a particularly onerous restriction in my own code, especially when sticking to the SA SQL layer. 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? -- David --~--~-~--~~~---~--~~ 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: Saved Queries (or text representation of the SQL)
On Fri, Oct 23, 2009 at 12:45 PM, jeff jeff.sie...@seagate.com wrote:
I would like to save a number of these in a database so size is
important (the serialized select() was somewhat large...) so I would
like to get the string representation of the raw SQL directly useable
by sqlalchemy if possible. As I have in my examples, the str(select)
doesn't seem directly useable as it is missing the parameters -
upper(host_status.site) = %(upper_1)s instead of upper
(host_status.site) = 'LCO' for example. Is there a way to get the
raw SQL text just as it is sent to the database and ready for reuse by
sqlalchemy (execute(text(SQLtext) )? Or do I have to construct my
own by doing string replaces on the parameters with the parameters as
found in .context? Seems like the raw SQL has to be available at some
point but don't know if the user has access to it.
This might be part of your answer:
Here is an arbitrary query in my database (I use ORM, but I'm sure you can
do equivalent with SQL expression language):
qry = sess.query(L.ListName,L.Description,LI.Item).\
join(LI).order_by(L.ListName,LI.Item).\
filter(L.ListName.startswith('SP'))
I can get the SQL as:
sql=qry.statement.compile()
string_sql = str(sql)
print string_sql
SELECT Lists.ListName, Lists.Description, ListItems.Item
FROM Lists JOIN ListItems ON Lists.ListName = ListItems.ListName
WHERE Lists.ListName LIKE :ListName_1 || '%%' ORDER BY
Lists.ListName, ListItems.Item
parameters are available as:
params = sql.params
print params
{u'ListName_1': 'SP'}
Now, save string_sql and params (you might need to get creative about
saving the dictionary) in your database. Later you can retrieve them and:
conn = whatever to get a good connection to database
results = conn.execute(text(string_sql), params).fetchall()
This approach has all the limitations of using text() as described in the
documentation. To me, the most important is that I have lost any knowledge
about the nature of each column. I do not know that the first column is
Lists.ListName. Maybe there is an attribute on the result set that allows
me to discover that information, but I don't know what it is.
Hope this helps a little
--~--~-~--~~~---~--~~
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: Saved Queries (or text representation of the SQL)
jeff wrote: I would like to save a number of these in a database so size is important (the serialized select() was somewhat large...) using serializer() ? really? if you do a naive dumps() with plain pickle, yes the serialize would be huge. so I would like to get the string representation of the raw SQL directly useable by sqlalchemy if possible. As I have in my examples, the str(select) doesn't seem directly useable as it is missing the parameters - upper(host_status.site) = %(upper_1)s instead of upper (host_status.site) = 'LCO' for example. it uses bind parameters, yup. Is there a way to get the raw SQL text just as it is sent to the database and ready for reuse by sqlalchemy (execute(text(SQLtext) )? yes, compile it using the default compiler so that bind parameter strings come out as :param format - str(mystatement.compile()). then also serialize the parameters on that object, mystatement.compile().params. Use them both later when constructing your text() object (text() accepts bindparams). you still will have problems though since you aren't maintaining the type information of result columns. So you might also want to serialize [(c.key, c.type) for c in myselect.c] and send that back into text() via the typemap argument. Much easier to use serializer. --~--~-~--~~~---~--~~ 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: Saved Queries (or text representation of the SQL)
On Fri, Oct 23, 2009 at 1:47 PM, Michael Bayer mike...@zzzcomputing.comwrote: Much easier to use serializer. I agree with that. --~--~-~--~~~---~--~~ 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: Shallow copying
On Fri, Oct 23, 2009 at 12:40 PM, Joril jor...@gmail.com wrote: Hi everyone! I'm trying to automatically build a shallow copy of a SA-mapped object.. At the moment my function is just: newobj = src.__class__() for prop in class_mapper(src.__class__).iterate_properties: setattr(newobj, prop.key, getattr(src, prop.key)) but I'm having troubles with lazy relations... Obviously getattr triggers the lazy loading, but since I don't need their values right away, I'd like to just copy the this should be lazy loaded-state of the attribute... Is this possible? Many thanks for your time! I did something similar. I iterated on class_mapper().columns to get the attributes to populate. That approach skipped all the relations, and in my case was exactly what I wanted Something like this (untested): newobj = src.__class__() for col in object_mapper(src).columns: setattr(newobj, col.name, getattr(src, col.name)) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
