[sqlalchemy] Download a database's definitions?
Hi there, Just wondering if there is some tool out there that will just connect to an existing database, do a bunch of magic, and output a file with the Table and Column objects all nice and defined for me? That way I can go about using column names in select statements, ect? The larger problem I'm trying to solve is that I want to connect to a Moodle database and I insist on using python, and I also insist on using sqlalchemy. :) Adam -- 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.
Re: [sqlalchemy] Download a database's definitions?
if you actually want code generation there is sqlacodegen: https://pypi.python.org/pypi/sqlacodegen/1.1.4 On Apr 26, 2014, at 5:24 AM, Adam Morris adam.morris@gmail.com wrote: Hi there, Just wondering if there is some tool out there that will just connect to an existing database, do a bunch of magic, and output a file with the Table and Column objects all nice and defined for me? That way I can go about using column names in select statements, ect? The larger problem I'm trying to solve is that I want to connect to a Moodle database and I insist on using python, and I also insist on using sqlalchemy. :) Adam -- 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. -- 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] Relationship depending on a field in join table.
Hi, I've been to the IRC channel and gotten alot of help from inklesspen, but I can't seem to figure this out. I have these tree tables; Users - iduser - name Companies - idcompany - name CompaniesUsers - companyid - userid - owner (TINYINT|Boolean) Now in my Company class I want users to select all users that are not owners. Something like this; *select * from users u where u.iduser in (select userid from companiesusers cu where cu.companyid = 1 and cu.owner = 0);* I've tried playing around with association_proxy and relationships but I want seems to properly add the two together. Here is a sample of one of my tries; https://gist.github.com/anonymous/11293742 Would be grateful if anyone could give me some hints, and I would love an example. The examples in the docs are great, but they don't explain how do use them together. -- 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] sqlite3 recursivity
hi all! as some already know, sqlite3 version 3.8.x (i'm not quite sure if it's 3.8.x, i might be wrong), but it has now support for recursivity using the with operator: https://sqlite.org/lang_with.html [1] well - probably mike can answear this better - will sqla provide basic support for it, like we already have in postgres using cte? or in the near future we'll only find 3rd part implementations, since it will take time for the world to use this version up? best regards, richard. Links: -- [1] https://sqlite.org/lang_with.html -- 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.
Re: [sqlalchemy] Relationship depending on a field in join table.
On Apr 26, 2014, at 10:59 AM, Peder Husom pederhu...@gmail.com wrote: Hi, I've been to the IRC channel and gotten alot of help from inklesspen, but I can't seem to figure this out. I have these tree tables; Users - iduser - name Companies - idcompany - name CompaniesUsers - companyid - userid - owner (TINYINT|Boolean) Now in my Company class I want users to select all users that are not owners. Something like this; select * from users u where u.iduser in (select userid from companiesusers cu where cu.companyid = 1 and cu.owner = 0); I've tried playing around with association_proxy and relationships but I want seems to properly add the two together. Here is a sample of one of my tries; https://gist.github.com/anonymous/11293742 Would be grateful if anyone could give me some hints, and I would love an example. The examples in the docs are great, but they don't explain how do use them together. the query can be emitted as follows: subq = session.query(CompaniesUser.id).filter(CompaniesUser.companyid == 1).filter(CompaniesUser.owner == 0) session.query(User).filter(User.id.in_(subq)) Were you hoping to have this effect? some_company = Session.query(Companies).first() some_company.non_user_owners ? So the easiest way to get this query off of Companies immediately is just to use a @property (see http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#building-query-enabled-properties): class Companies(...): @property def non_user_owners(self): return object_session(self).query(User) same query If OTOH you do in fact want this query to take the current Company.id into account, this would be simple using primaryjoin/secondaryjoin/secondary, it just requires that the IN is unwrapped into a regular join criterion. Assume the name of the CompaniesUsers table is companiesusers: class Companies(...): non_user_owners = relationship(Users, primaryjoin=Companies.id == companiesusers.c.companyid, secondary=companiesusers, secondaryjoin=and_(companiesusers.c.userid=Users.id, companiesusers.owner == 0) ) join conditions are documented at http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#configuring-how-relationship-joins. Just curious, when you say don't explain how to use them together, is that including this documentation section? What I've done here is adapt your cases to examples which are already present in that section - what exactly is missing? -- 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.
Re: [sqlalchemy] sqlite3 recursivity
what happens if you just try it? the syntax looks entirely standard. On Apr 26, 2014, at 2:10 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: hi all! as some already know, sqlite3 version 3.8.x (i'm not quite sure if it's 3.8.x, i might be wrong), but it has now support for recursivity using the with operator: https://sqlite.org/lang_with.html well - probably mike can answear this better - will sqla provide basic support for it, like we already have in postgres using cte? or in the near future we'll only find 3rd part implementations, since it will take time for the world to use this version up? best regards, richard. -- 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. -- 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.
Re: [sqlalchemy] Relationship depending on a field in join table.
On Apr 26, 2014, at 2:23 PM, Michael Bayer mike...@zzzcomputing.com wrote: If OTOH you do in fact want this query to take the current Company.id into account, this would be simple using primaryjoin/secondaryjoin/secondary, it just requires that the IN is unwrapped into a regular join criterion. Assume the name of the CompaniesUsers table is companies users: sorry, this is an editing problem, this sentence should read: If OTOH you want a full blown relationship(), which grants the ability to automatically compose this join condition into a larger query as well as eager loading, this would be simple (rest of sentence follows) -- 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.
Re: [sqlalchemy] update where using the ORM
On Saturday, April 26, 2014 12:29:50 AM UTC+1, Michael Bayer wrote: On Apr 25, 2014, at 6:54 PM, Tim Kersten t...@io41.com javascript: wrote: This is an unusual use case because it seems like you’d like to outright ignore the row if it doesn’t match? or are you throwing an exception if you don’t get the expected count? Yes, I'm ignoring the row if it doesn't match. One use case for this: A misbehaving piece of code set a bad value in a column in many rows. The code was fixed, and thus when customers used that code in future the values would be updated to good values. A data migration to automatically adjust the bad values would have the potential to overwrite one that's just been altered by a customer, and this is what I'm trying to avoid, with as minimal an impact on the running system. I'd only like to avoid doing so while still using the ORM and was wondering if there's a way to do this without having a version column? I can think of some potentially very exotic ways of injecting this behavior with cursor_execute() events, but it would be quite awkward. The ORM still might complain when it sees zero rows updated. But the real problem is if you want the ORM to update a row, then have it ignored, now you’ve totally got the wrong data in memory. I’m not sure how your application is constructed that A. you want to use the ORM but B. you don’t care if it has the wrong data loaded after it thinks it’s synchronized.If you just need some simplistic CRUD interface (e.g. object.save()) you can build those on top of Core pretty easily. The ORM has a specific usage model and this goes counter to that, hence there’s a Core. In my initial the sql expression example no updates happen because no matching rows are found and thus they're ignored, however it's not important that they're ignored, just that no update can happen. I'd be happy to have a way to do this via the ORM if that raises an exception instead. I guess what I've been looking for is here is the exact same behaviour as the version col id behaviour, except there is no version id column. Instead there are one or more columns used in a similar manor instead, basically a mechanism to add additional expressions. If zero rows are matched, an exception is raised, similar to if a version column was updated by another transaction. Session.set_update_where(my_instance, MyModel.name == 'old_value') The resulting behaviour would be identical to using a version col id, but only for this transaction and the instance passed to the update_where() method, and instead of UPDATE ... WHERE pk = %s AND version = %s you'd have UPDATE ... WHERE pk = %s AND name = %s. An alternative approach might be to do infer all changed column values and add their old values to the WHERE part before a flush. This behaviour would need to be turned on for a table or perhaps just for an instance. The above approaches provide the same behaviour that version col id does, but has different trade offs. Advantages of using the version col id approach instead of the above: - Smaller SQL queries as the WHERE clause contains only the PK and the version column, vs all lots of columns and their old values. Advantages of using the above approach instead of version col id: - Much finer grain changes possible without raising an exception, provided that the various transactions all modify different columns. (i.e. txn A changes the 'name' col, and txn B changes the 'description' col, both would be accepted, where as with the version col id one of the transactions would have failed.) I don't know half as much as I'd like about how queries are run in the DB, so my suggested alternative approaches above may have serious performance implications other than increasing SQL traffic. Thank you very much for your feedback. For now I'll focus on adding version columns to most of my tables and that should solve not only my current issues but will also protect the system from the lost update issues in general. -- 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.
Re: [sqlalchemy] update where using the ORM
On Apr 26, 2014, at 3:26 PM, Tim Kersten t...@io41.com wrote: The resulting behaviour would be identical to using a version col id, but only for this transaction and the instance passed to the update_where() method, and instead of UPDATE ... WHERE pk = %s AND version = %s you'd have UPDATE ... WHERE pk = %s AND name = %s. This is where it would have to go: https://bitbucket.org/zzzeek/sqlalchemy/src/146fbf6d26a8c4140a47aeb03131fdf81007b9a2/lib/sqlalchemy/orm/persistence.py?at=master#cl-308 where you can see that logic is wired to a single expression, which could be a SQL expression that gathers up lots of columns, but the expression is fixed. It isn't derivable from all the attributes that have changed, and the logic here would need to be expanded into a much more elaborate, complicated, and non-performant system to support this case. For a feature to be added, it must attain a certain ratio of impact on complexity to how many people will actually use it. If the feature is very simple and non-intrusive, we can often add it even if only one person needs it. If the feature is very complex, we can add it only if this is an obvious need by a significant percentage of users. in many cases we add event hooks in areas that are to allow expansion of capabilities, but in the case of persistence, we already have before_update() and after_update(), adding more hooks into the construction of the actual SQL would be very complex and extremely specific to the mechanics; it would be brittle, unstable and difficult to use. IMHO the two existing approaches have no downsides: 1. repeatable read isolation (which can be set on a per-session or per-transaction basis. Why not just use it?) 2. version columns, including version columns that can be *timestamps*. There is no need to go through an expensive (think TEXT/BLOB columns) and error prone (think floating points) comparison of every column if the UPDATE of a stale row is to be avoided - stale just means our timestamp of the row is earlier than the timestamp that's present. Advantages of using the above approach instead of version col id: - Much finer grain changes possible without raising an exception, The behavior where version misses are to be ignored is also quite unusual and I've never known anyone to want silent failure of an UPDATE statement like that. An entity update has a specific intent which is to target that entity; this is at the core of what an ORM is trying to do. -- 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] Binding base classes to different engines
My project requires querying an externally-managed database as well as a
project-specific database. What I've been doing to date is copying the
external database (which changes very infrequently) into the
project-specific database so I only need one engine and one dbsession. I'm
now trying to correct this monstrosity by binding the external-specific
classes to the external engine, still using a single session.
Per agronholm's suggestion on IRC, I'm attempting to do this via a base
class for each database in order to avoid having to individually bind each
class to the correct engine. Not quite sure how to proceed, though, because
'binds' isn't accepting my keys when those keys are empty base classes.
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (
scoped_session,
sessionmaker
)
from zope.sqlalchemy import ZopeTransactionExtension
Base = declarative_base()
DBSession =
scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
class DB1(Base):
__abstract__ = True
class DB2(Base):
__abstract__ = True
class SomeInternalClass(DB1):
__tablename__ = 'table_in_db1'
class SomeExternalClass(DB2):
__tablename__ = 'table_in_db2'
db1_engine = engine_from_config(settings, 'sqlalchemy.db1.')
db2_engine = engine_from_config(settings, 'sqlalchemy.db2.')
DBSession.configure(binds={DB1:db1_engine, DB2:db2_engine})
This raises an exception: sqlalchemy.exc.NoInspectionAvailable: No
inspection system is available for object of type class
'sqlalchemy.ext.declarative.api.DeclarativeMeta'
But when I keep the same class structure and switch back to the original
bind, it works:
DBSession.configure(bind=db1_engine)
Of course, that means I'm back to using the monster database again. So
what's wrong this?
DBSession.configure(binds={DB1:db1_engine, DB2:db2_engine})
--
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] Re: Binding base classes to different engines
Not sure if __abstract__ is the way to go. Should I instead be creating
mixins?
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative.html#mixin-and-custom-base-classes
On Saturday, April 26, 2014 5:07:23 PM UTC-4, Brian Findlay wrote:
My project requires querying an externally-managed database as well as a
project-specific database. What I've been doing to date is copying the
external database (which changes very infrequently) into the
project-specific database so I only need one engine and one dbsession. I'm
now trying to correct this monstrosity by binding the external-specific
classes to the external engine, still using a single session.
Per agronholm's suggestion on IRC, I'm attempting to do this via a base
class for each database in order to avoid having to individually bind each
class to the correct engine. Not quite sure how to proceed, though, because
'binds' isn't accepting my keys when those keys are empty base classes.
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (
scoped_session,
sessionmaker
)
from zope.sqlalchemy import ZopeTransactionExtension
Base = declarative_base()
DBSession =
scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
class DB1(Base):
__abstract__ = True
class DB2(Base):
__abstract__ = True
class SomeInternalClass(DB1):
__tablename__ = 'table_in_db1'
class SomeExternalClass(DB2):
__tablename__ = 'table_in_db2'
db1_engine = engine_from_config(settings, 'sqlalchemy.db1.')
db2_engine = engine_from_config(settings, 'sqlalchemy.db2.')
DBSession.configure(binds={DB1:db1_engine, DB2:db2_engine})
This raises an exception: sqlalchemy.exc.NoInspectionAvailable: No
inspection system is available for object of type class
'sqlalchemy.ext.declarative.api.DeclarativeMeta'
But when I keep the same class structure and switch back to the original
bind, it works:
DBSession.configure(bind=db1_engine)
Of course, that means I'm back to using the monster database again. So
what's wrong this?
DBSession.configure(binds={DB1:db1_engine, DB2:db2_engine})
--
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] Re: Binding base classes to different engines
Continuing to troubleshoot. This produces the same exception:
DBSession.configure(binds={DB1: db1_engine, DB2: db1_engine})
Note that I'm binding both classes to the original engine. I thought it
would be the same as the working config:
DBSession.configure(bind=db1_engine)
--
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.
Re: [sqlalchemy] Re: Binding base classes to different engines
the answer is simple in that the binds argument to Session precedes both
Declarative as well as the advent of the __abstract__ keyword; it typically
expects actual Mapper objects or classes from which a Mapper can be pulled
directly.
For now, you can instead override get_bind() to do whatever you want it to do:
class MySession(Session):
def get_bind(self, mapper=None, clause=None):
if mapper is not None:
if mapper.class_.issubclass(DB1):
return bind1
elif mapper.class_.issubclass(DB2):
return bind2
return super(MySession, self).get_bind(mapper=mapper, clause=clause)
https://bitbucket.org/zzzeek/sqlalchemy/issue/3035/overhaul-sessionbinds-to-support-classes
is added for this.
On Apr 26, 2014, at 5:21 PM, Brian Findlay brian.m.find...@gmail.com wrote:
Continuing to troubleshoot. This produces the same exception:
DBSession.configure(binds={DB1: db1_engine, DB2: db1_engine})
Note that I'm binding both classes to the original engine. I thought it would
be the same as the working config:
DBSession.configure(bind=db1_engine)
--
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.
--
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.
Re: [sqlalchemy] Re: Binding base classes to different engines
Thanks, Mike. Will check this 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. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Binding base classes to different engines
I'm almost certainly exposing my level of ignorance here, but does this
mean I could just replace
DBSession.configure(binds={DB1:db1_engine, DB2:db2_engine})
with
DBSession.configure(class_=MySession)
?
I suppose I could even use
DBSession = scoped_session(sessionmaker(class_=MySession,
extension=ZopeTransactionExtension()))
--
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.
Re: [sqlalchemy] Re: Binding base classes to different engines
On Apr 26, 2014, at 11:13 PM, Brian Findlay brian.m.find...@gmail.com wrote:
I'm almost certainly exposing my level of ignorance here, but does this mean
I could just replace
DBSession.configure(binds={DB1:db1_engine, DB2:db2_engine})
with
DBSession.configure(class_=MySession)
?
I suppose I could even use
DBSession = scoped_session(sessionmaker(class_=MySession,
extension=ZopeTransactionExtension()))
had to check, but yes sessionmaker() accepts a custom session class as class_.
--
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.
