[sqlalchemy] Reflect from one engine, use reflected tables in different engine
Hi, Is it possible to reflect from one engine and then use some of the tables reflected as base to create tables in a second, different engine? A possible way: A_engine = create_engine(postgresql+psycopg2://...A...) B_engine = create_engine(postgresql+psycopg2://...B...) A_metadata = MetaData(schema='A', bind=A_engine) B_metadata = MetaData(schema='B', bind=B_engine) A_metadata.reflect(bind=A_engine) A_table = A_metadata.tables['A_table'] A_table.metadata = B_metadata A_table.schema = 'B' But now, I'm not sure how to tell A_metadata that it has a A_table to care for. I've tried also with similar success using the 'autoload' and with the 'automap_base', instead of reflect. Any ideas? is this too hacky/crazy/stupid? If not, it is a better way I haven't found? The use case is that I'd like to reflect what are the tables from production database, and use them as 'templates' to create a new database for testing. That is, I want to be able to create in my test database with the actual reflection of what it is in production, and use SQLAlchemy to control the creation of fake data in it. Thanks in advance! Luis -- 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: mysql, use password from my.cnf file
Hi ,
Iam trying to use
from sqlalchemy.engine.url import URL
myDB = URL(drivername='mysql', host='localhost',
database='my_database_name',
query={ 'read_default_file' : '/path/to/.my.cnf' } )
engine = create_engine(name_or_url=
myDB)
but it is not working form me. The error I am getting is
sqlalchemy.exc.OperationalError: (OperationalError) (1045, Access denied
for user 'root'@'localhost' (using password: NO)) None None
This is occurring because SQLALCHEMY is not reading password from .my conf
file.
It is working fine if I give username and password as URL argument. The
my.conf file is
also correct which is working if pass it to mysql shell command.
Any comment on how to make SQLALCHEMY/MYSQL read password from file.
Thanks
Balaji
On Monday, September 29, 2008 at 2:48:18 PM UTC-7, Tom H wrote:
Depending on security preferences, it may be desirable to keep
database passwords out of code files.
One of the recommendations for MySQL is to keep the password in
a .my.cnf file accessible only to the user.
http://dev.mysql.com/doc/refman/5.0/en/password-security.html
To use the .my.cnf password in sqlalchemy, do something like the
following:
from sqlalchemy.engine.url import URL
myDB = URL(drivername='mysql', host='localhost',
database='my_database_name',
query={ 'read_default_file' : '/path/to/.my.cnf' } )
engine = create_engine(name_or_url=myDB)
# use the engine as usual, no password needed in your code file :)
This has been very useful to me, hopefully others find it helpful as
well.
--
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] Configuring a self-referential relationship with cumstom foreign conditions
Hello,
I am trying to create a declarative calss, which is self referential and
uses a custom primaryjoin condition, involving other columns like a
is_deleted column:
class Item(Base):
__tablename__ = items
uuid = Column(UUID(), primary_key=True)
is_deleted = Column(Boolean, default=False)
parent_id = Column(UUID(), ForeignKey('items.uuid'))
parent = relationship('Item', remote_side=[uuid],
back_populates=children)
children = relationship('Item',
primaryjoin=and_(Item.parent_id ==
Item.uuid, Item.is_deleted == False),
back_populates=parent)
Right now when asking for all non-deleted children (any_item.children) I
get all all Items, not matter their is_deleted value. I found, this is
due to the query rendering as:
SELECT . FROM items WHERE items.parent_id = 'abc33424dsfsdf' AND 0 =
false;
It seems like the Item.is_deleted == False is actually evaluated to 0 =
false. How can I make SQLA use items.is_deleted = 0 instead? I tried to
use remote() and foreign() but they seem to be meant for something else.
I am using the newest version with MySQL.
Thank you,
Sebastian
--
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] What's the idiomatic way of writing INSERT... ON DUPLICATE KEY UPDATE
Has support for MySQL INSERT... ON DUPLICATE KEY UPDATE been integrated into sqlalchemy more recently? Thanks, Vitaly -- 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] how fast can pure sqlalchemy perform?
On Friday 07 August 2015 10:05 PM, Claudio Freire wrote: On Fri, Aug 7, 2015 at 12:51 PM, Mike Bayer mike...@zzzcomputing.com wrote: On 8/7/15 11:05 AM, kk wrote: On Friday 07 August 2015 03:03 PM, Ladislav Lenart wrote: Hello. ORM is certainly slower. How much depends A LOT on your workload. For example bulk operations with ORM are an order of magnitude slower than raw SQL. On the other hand, SQLAlchemy Core let's you write generative SQL queries without ORM features which are as performant as raw SQL. So is is there some kind of a method to have some prepared sql statements in SQLAlchemy itself? I have seen that prepared statements in other languages like Java do a great job. That's totally a myth and you can see me ranting on this whole topic of explicit prepared statements == SPEED here: https://mail.python.org/pipermail/db-sig/2014-December/006147.html See the benchmark there. Whatever performance we get with prepared statements is vanishingly small and utterly dwarfed by the order-of-magnitude-greater latencies we get from Python. The DBAPI already has a great speed optimization in this area and it is known as executemany() - it applies only to CRUD statements, not SELECT, but SQLAlchemy uses executemany() very heavily and to great effect - the speed gains here are not so much due to prepared statements, as psycopg2 does not use them in any way, but due to the fact that we roll up lots of data into a single call that psycopg2 can run from pure compiled C code. It may be a myth most of the time, but there are cases where it is not. I had one case (in a whole decade of programming, so it is indeed very rare) in which a very complex query ran very fast, and planning time was the dominant cost (think 150ms for planning and 15ms for execution). For that query, preparing it explicitly saved a lot of runtime. Again, executemany helps when it's implemented with prepared statements. But I was using psycopg2 and it doesn't use prepared statements, so I had to prepare them explicitly myself. This was with SQLAlchemy 0.3 and I managed to do it just fine. Some black magic was needed of course (had to explicitly compile the query to SQL, bind parameters, and generate a PREPARE statement from it), but nothing too complex. Now, pg8000 does use prepared statements, so it may be as simple as using that driver if the need for prepared statements is there. You can even have two engines pointing to the same database and use pg8000 only for the queries that really need prepared statements. So SQLAlchemy (and DBAPI) has come a long way since I found that case I mentioned above. I'm sure today handling that case would have been even easier. Thanks for the details reply. Now I understand it better. So you mean to say executemany will give me the needed performance gain in a nutshell. Is that curect? Secondly, is executemany good at only Insert, or Update or both? And lastly if I have a big resultset through a select statement, more so from a view, what is the best approach to use if I decide not to use stored procedures? I will come with more questions before the decision is made so I may take some more of your valuable time. happy hacking. Krishnakant. -- 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] What's the idiomatic way of writing INSERT... ON DUPLICATE KEY UPDATE
On 8/7/15 5:25 PM, vitaly numenta wrote: Has support for MySQL INSERT... ON DUPLICATE KEY UPDATE been integrated into sqlalchemy more recently? no. Thanks, Vitaly -- 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. 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] how fast can pure sqlalchemy perform?
Dear all, I am planning to totally revamp my rdbms model and totally migrate to sqlalchemy. Right now we have a lot of stored procedurs for obvious performance benefits. However it is becoming more and more difficult to maintain the system and also difficult to migrate existing users when there are major changes to our software. Basically our stored procedures get created when the database is created in the deploy phase. So I wish to know how much performance I will loos if I totally switch to using ORM, specifically SQLAlchemy. I am also planning to use some thing like json columns in postgresql tables, so will it really matter with an ORM? Happy hacking. Krishnakant. -- 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] how fast can pure sqlalchemy perform?
Hello. ORM is certainly slower. How much depends A LOT on your workload. For example bulk operations with ORM are an order of magnitude slower than raw SQL. On the other hand, SQLAlchemy Core let's you write generative SQL queries without ORM features which are as performant as raw SQL. Overall SQLAlchemy is an excellent library to work with! For some numbers, see: http://docs.sqlalchemy.org/en/latest/faq/performance.html http://docs.sqlalchemy.org/en/latest/orm/examples.html#examples-performance HTH, Ladislav Lenart On 7.8.2015 11:16, kk wrote: Dear all, I am planning to totally revamp my rdbms model and totally migrate to sqlalchemy. Right now we have a lot of stored procedurs for obvious performance benefits. However it is becoming more and more difficult to maintain the system and also difficult to migrate existing users when there are major changes to our software. Basically our stored procedures get created when the database is created in the deploy phase. So I wish to know how much performance I will loos if I totally switch to using ORM, specifically SQLAlchemy. I am also planning to use some thing like json columns in postgresql tables, so will it really matter with an ORM? Happy hacking. Krishnakant. -- 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] how to tell which tables already joined in a query statement (or best way to dynamically build a query?)
On 8/7/15 10:35 AM, Brian Cherinka wrote: Hi, I'm trying to build an SQLalchemy ORM query dynamically based on user selected options. Some of these options come from the same table, but the user could select either one or both criteria to filter on. Since I don't know which options the user will select ahead of time, I have to join to the same table multiple times. However this throws an error ProgrammingError: (psycopg2.ProgrammingError) table name TableB specified more than once when I try to submit the query. How can I find out which tables have already been joined in a query? Or what's the best way to handle building a query based on multiple criteria? I'm using SQLalchemy 1.0.0. Here is my pseudo-code. Option 1. Option 2. Option 3. (any or all options can be selected, and they all come from the same joined table) // base table query = session.query(TableA) // add on new criteria if option 1: query = query.join(TableB).filter(TableB.option1 X ) if option 2: query = query.join(TableB).filter(TableB.option2 X ) if option 3: query = query.join(TableB).filter(TableB.option3 X ) However, when attempting query.all(), this throws the above error, if I have selected any two options. What I think it should be is something like this... //base query = session.query(TableA) //join query = query.join(TableB) // add on new criteria if option 1: query = query.filter(TableB.option1 X ) if option 2: query = query.filter(TableB.option2 X ) if option 3: query = query.filter(TableB.option3 X ) but I don't want to join to TableB if I don't have to. I have many different tables where this kind of situation applies, and it seems inefficient to join to all other tables just in case I may need to filter on something. This is a thing for which there is a plan to make this really clear and doable. But right now that is only a plan. It's not necessarily straightforward, in the totally open-ended case, to determine every table that will be in the FROM clause, given that if a query has filter(X.foo == 'bar'), now X is in the FROM list, which you wouldn't know until you generate the core Select statement.The problem of determining exactly what outer JOINs and such are present in an easy and performant way is a large problem to be solved and you can see the proposed, eventual API for this at https://bitbucket.org/zzzeek/sqlalchemy/issues/3225/query-heuristic-inspection-system#comment-12988632. So given that this is a big new feature that's currently targeted at least a year away, for now we need to keep things simple. If I really had simple imperative code all in one place like that, I'd probably just refer to the flag twice: if option1 or option2 or option3: query = query.join(TableB) if option1: # etc if option2: # etc. But I can hear you cringing, so you'd have to just keep track of what you're joining.So still keeping it boringly simple, use a set. To make this maybe less tedious, we'll build a closure function so that we can be q a little more succinct: def joiner(): already_joined = set() def join(q, ent): if ent not in already_joined: already_joined.add(ent) q = q.join(ent) return q return join def my_filter_thing(option1, option2, option3): q = session.query(A) join = joiner() if option1: q = join(q, B).filter(B.option1 == foo) if option2: q = join(q, B).filter(B.option2 == bar) Then I hear, OK but I'm passing the Query to other functions and I don't want to ship that extra thing along with it everywhere. So we can stick the already_joined on the Query directly. But the thing to track here is that this is an immutable object so that the previous version of the Query doesn't have the newer state on it, in case you are forking off a Query object into multiple versions of itself: def join(q, ent): if not hasattr(q, '_already_joined'): already_joined = q._already_joined = frozenset() else: already_joined = q._already_joined if ent not in already_joined: q = q.join(ent) q._already_joined = q._already_joined.union([ent]) return q def my_filter_thing(option1, option2, option3): q = session.query(A) if option1: q = join(q, B).filter(B.option1 == foo) if option2: q = join(q, B).filter(B.option2 == bar) Another option is to try to anticipate what we'll be doing in #3225; that is, look in query._from_obj. This is where we're poking around in things not 100% stable API over the long term and the #3225 API would be very preferable, but it could be: from sqlalchemy.sql import util def join(q, ent): if ent not in set(util.surface_selectables(q._from_obj[0])): q = q.join(ent) return q surface_selectables() is a helper that basically looks at Join objects and pulls out the left and right of each recursively, so it can find tables. query._from_obj
[sqlalchemy] how to tell which tables already joined in a query statement (or best way to dynamically build a query?)
Hi, I'm trying to build an SQLalchemy ORM query dynamically based on user selected options. Some of these options come from the same table, but the user could select either one or both criteria to filter on. Since I don't know which options the user will select ahead of time, I have to join to the same table multiple times. However this throws an error ProgrammingError: (psycopg2.ProgrammingError) table name TableB specified more than once when I try to submit the query. How can I find out which tables have already been joined in a query? Or what's the best way to handle building a query based on multiple criteria? I'm using SQLalchemy 1.0.0. Here is my pseudo-code. Option 1. Option 2. Option 3. (any or all options can be selected, and they all come from the same joined table) // base table query = session.query(TableA) // add on new criteria if option 1: query = query.join(TableB).filter(TableB.option1 X ) if option 2: query = query.join(TableB).filter(TableB.option2 X ) if option 3: query = query.join(TableB).filter(TableB.option3 X ) However, when attempting query.all(), this throws the above error, if I have selected any two options. What I think it should be is something like this... //base query = session.query(TableA) //join query = query.join(TableB) // add on new criteria if option 1: query = query.filter(TableB.option1 X ) if option 2: query = query.filter(TableB.option2 X ) if option 3: query = query.filter(TableB.option3 X ) but I don't want to join to TableB if I don't have to. I have many different tables where this kind of situation applies, and it seems inefficient to join to all other tables just in case I may need to filter on something. Any thoughts, help or suggestions? Thanks, Brian -- 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] how fast can pure sqlalchemy perform?
On Friday 07 August 2015 03:03 PM, Ladislav Lenart wrote: Hello. ORM is certainly slower. How much depends A LOT on your workload. For example bulk operations with ORM are an order of magnitude slower than raw SQL. On the other hand, SQLAlchemy Core let's you write generative SQL queries without ORM features which are as performant as raw SQL. So is is there some kind of a method to have some prepared sql statements in SQLAlchemy itself? I have seen that prepared statements in other languages like Java do a great job. Overall SQLAlchemy is an excellent library to work with! So you mean performance will really get hit when pure ORM is used. So shold I use a mixture? For inserts let's say orm and for bulk select queries some prepared statement like thing (if it exists )? Happy hacking. Krishnakant. -- 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] how fast can pure sqlalchemy perform?
On Fri, Aug 7, 2015 at 12:05 PM, kk krm...@gmail.com wrote: On Friday 07 August 2015 03:03 PM, Ladislav Lenart wrote: Hello. ORM is certainly slower. How much depends A LOT on your workload. For example bulk operations with ORM are an order of magnitude slower than raw SQL. On the other hand, SQLAlchemy Core let's you write generative SQL queries without ORM features which are as performant as raw SQL. So is is there some kind of a method to have some prepared sql statements in SQLAlchemy itself? I have seen that prepared statements in other languages like Java do a great job. Depending on the dialect and driver, you can use executemany. Not all drivers implement executemany with prepared statements though, check your case. Overall SQLAlchemy is an excellent library to work with! So you mean performance will really get hit when pure ORM is used. So shold I use a mixture? For inserts let's say orm and for bulk select queries some prepared statement like thing (if it exists )? It really depends on each use case. If your concern is whether ORM queries will be efficient, don't worry, SQLAlchemy is powerful enough that you can make almost any kind of query with the ORM. Almost all the optimizations you could do to plain SQL are doable at the ORM level. If your concern is CPU overhead on the application side, yes, the ORM does induce quite an overhead, but whether it's a problem or not greatly depends on your use case, the number of objects your transactions will be handling, the complexity of the mapping, your latency and thoughput constraints, etc. I've convinced myself over time that a little overhead is fine in exchange for the benefits the ORM gives you, in ease of coding mostly, but also robustness (the ORM solves some issues that are hard to handle correctly and robustly with raw SQL), and SQLAlchemy is flexible enough that you can usually escape to raw sql if/when you need to. You shouldn't optimize prematurely, the ORM won't be a death trap as it happens with other ORMs. So, we're saying we need more information if we're to give a meaningful answer. -- 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] how fast can pure sqlalchemy perform?
On Fri, Aug 7, 2015 at 12:51 PM, Mike Bayer mike...@zzzcomputing.com wrote: On 8/7/15 11:05 AM, kk wrote: On Friday 07 August 2015 03:03 PM, Ladislav Lenart wrote: Hello. ORM is certainly slower. How much depends A LOT on your workload. For example bulk operations with ORM are an order of magnitude slower than raw SQL. On the other hand, SQLAlchemy Core let's you write generative SQL queries without ORM features which are as performant as raw SQL. So is is there some kind of a method to have some prepared sql statements in SQLAlchemy itself? I have seen that prepared statements in other languages like Java do a great job. That's totally a myth and you can see me ranting on this whole topic of explicit prepared statements == SPEED here: https://mail.python.org/pipermail/db-sig/2014-December/006147.html See the benchmark there. Whatever performance we get with prepared statements is vanishingly small and utterly dwarfed by the order-of-magnitude-greater latencies we get from Python. The DBAPI already has a great speed optimization in this area and it is known as executemany() - it applies only to CRUD statements, not SELECT, but SQLAlchemy uses executemany() very heavily and to great effect - the speed gains here are not so much due to prepared statements, as psycopg2 does not use them in any way, but due to the fact that we roll up lots of data into a single call that psycopg2 can run from pure compiled C code. It may be a myth most of the time, but there are cases where it is not. I had one case (in a whole decade of programming, so it is indeed very rare) in which a very complex query ran very fast, and planning time was the dominant cost (think 150ms for planning and 15ms for execution). For that query, preparing it explicitly saved a lot of runtime. Again, executemany helps when it's implemented with prepared statements. But I was using psycopg2 and it doesn't use prepared statements, so I had to prepare them explicitly myself. This was with SQLAlchemy 0.3 and I managed to do it just fine. Some black magic was needed of course (had to explicitly compile the query to SQL, bind parameters, and generate a PREPARE statement from it), but nothing too complex. Now, pg8000 does use prepared statements, so it may be as simple as using that driver if the need for prepared statements is there. You can even have two engines pointing to the same database and use pg8000 only for the queries that really need prepared statements. So SQLAlchemy (and DBAPI) has come a long way since I found that case I mentioned above. I'm sure today handling that case would have been even easier. -- 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] Instrumenting an object after the fact
Hi there,
I'm trying to do something pretty weird. I have SQLAlchemy instrumented
classes that extend a class in addition to Base, like so:
class Region(Base, t_Region):
__tablename__ = 'region'
id = Column(
mysql.INTEGER(11),
primary_key=True,
nullable=False,
autoincrement=True,
index=True,
unique=True)
name = Column(
mysql.VARCHAR(64),
nullable=False,
index=True,
default='',
unique=True)
This works great for reads because I can return it anywhere a t_Region is
expected and everything does what it is supposed to.
But now I can't figure out how to do the reverse; for example, I have a
t_Region object, and I want to insert it into the database. So far I've got
this:
mapped = Region(**region.__dict__)
This works, but it lacks elegance as it creates another object.
I also tried region.__class__ = Region, but I got a (obvious) error message:
sqlalchemy.orm.exc.UnmappedInstanceError: Class
'ame.serf_service.models.region.Region' is mapped, but this instance lacks
instrumentation. This occurs when the instanceis created before
sqlalchemy.orm.mapper(ame.serf_service.model
s.region.Region) was called.
I've tried a few attempts at calling mapper() that haven't been fruitful
yet. Is there an elegant way to do this?
Bonus points if it can also handle the recursive case, eg:
class A(Base, t_A):
b = Column(mysql.VARCHAR(20), ForeignKey('b.b'))
b_obj = relationship('B', foreign_keys=[b])
such that if I get a t_A that has a b_obj of type t_B, t_A and t_B will
both get mapped appropriately. I'm happy to do this parsing myself, though.
Thanks for any hints,
Dusty
--
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] Instrumenting an object after the fact
On 8/7/15 2:10 PM, buch...@gmail.com wrote: Hi there, I'm trying to do something pretty weird. I have SQLAlchemy instrumented classes that extend a class in addition to Base, like so: class Region(Base, t_Region): __tablename__ = 'region' id = Column( mysql.INTEGER(11), primary_key=True, nullable=False, autoincrement=True, index=True, unique=True) name = Column( mysql.VARCHAR(64), nullable=False, index=True, default='', unique=True) This works great for reads because I can return it anywhere a t_Region is expected and everything does what it is supposed to. But now I can't figure out how to do the reverse; for example, I have a t_Region object, and I want to insert it into the database. So far I've got this: mapped = Region(**region.__dict__) This works, but it lacks elegance as it creates another object. I'd go with that, or more likely a dedicated constructor (and I'd never just yank __dict__ like that for schema-defined objects): from sqlalchemy import inspect class Region(...): @classmethod def as_region(cls, other): kw = dict( (key, getattr(other, key)) for key in inspect(cls).attrs.keys() if hasattr(other, key) ) return Region(**kw) as it is the simplest. Ideally you'd not use raw t_Region objects and instead use an appropriate factory pattern to create the Region object up front - it's this lack of elegance that ultimately leads to other elegance-lacking things like having to copy the object to the type you want. -- 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: Instrumenting an object after the fact
Thanks for the insights! I'll carry on with this and see what I can make happen. I'm not able to introduce a factory pattern at the point Region object is created without monkeypatching an external library. However, I'm now going to explore instrumenting that class using mapper() instead of having separate ORM objects. Thanks, as always, for maintaining SQLAlchemy so well and for so long. Dusty -- 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] how fast can pure sqlalchemy perform?
On 8/7/15 11:05 AM, kk wrote: On Friday 07 August 2015 03:03 PM, Ladislav Lenart wrote: Hello. ORM is certainly slower. How much depends A LOT on your workload. For example bulk operations with ORM are an order of magnitude slower than raw SQL. On the other hand, SQLAlchemy Core let's you write generative SQL queries without ORM features which are as performant as raw SQL. So is is there some kind of a method to have some prepared sql statements in SQLAlchemy itself? I have seen that prepared statements in other languages like Java do a great job. That's totally a myth and you can see me ranting on this whole topic of explicit prepared statements == SPEED here: https://mail.python.org/pipermail/db-sig/2014-December/006147.html See the benchmark there. Whatever performance we get with prepared statements is vanishingly small and utterly dwarfed by the order-of-magnitude-greater latencies we get from Python. The DBAPI already has a great speed optimization in this area and it is known as executemany() - it applies only to CRUD statements, not SELECT, but SQLAlchemy uses executemany() very heavily and to great effect - the speed gains here are not so much due to prepared statements, as psycopg2 does not use them in any way, but due to the fact that we roll up lots of data into a single call that psycopg2 can run from pure compiled C code. Reading that thread overall, you'll learn at the very least that the Python DBAPI does not expose prepared statements. As you'll note, I'm entirely against the idea of them being made explicit, for this exact reason; now everyone's going to want the concept expressed explicitly in SQLAlchemy, involving that multiple resource-holding cursors be held onto which then open the doors to all kinds of new concurrency / memory / connection pool / locking issues that will all be reported as new bugs that I have to worry about, all for absolutely no good reason as explicit PS does just about nothing to help performance in any real way. Yet another chronically misunderstood concept that everyone is going to demand everywhere even if you show them that it's pointless (see: http://techspot.zzzeek.org/2015/02/15/asynchronous-python-and-databases/ for the reigning king of this phenomenon). Now, there is something in SQLAlchemy that will give you an *enormous* boost of speed that is basically doing what everyone things a prepared statement will do, which is a Python-side prepare of everything. Because compared to the database's time to set up a statement handle, the time it takes for SQLAlchemy to set up a core Select from a Query object as well as the time to build the Query itself is very significant. That feature is known as Baked Queries and it is documented here: http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/baked.html. A lot of work went into this very unique feature and it is also benchmarked in the example suite, which I would strongly recommend you read and run fully. Overall SQLAlchemy is an excellent library to work with! So you mean performance will really get hit when pure ORM is used. So shold I use a mixture? For inserts let's say orm and for bulk select queries some prepared statement like thing (if it exists )? The suite in http://docs.sqlalchemy.org/en/rel_1_0/orm/examples.html#module-examples.performance was built in order to provide the answers to these questions. That's where you need to be. Happy hacking. Krishnakant. -- 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] Configuring a self-referential relationship with cumstom foreign conditions
On 8/7/15 5:05 PM, SElsner wrote:
Hello,
I am trying to create a declarative calss, which is self referential
and uses a custom primaryjoin condition, involving other columns like
a is_deleted column:
class Item(Base):
__tablename__ = items
uuid = Column(UUID(), primary_key=True)
is_deleted = Column(Boolean, default=False)
parent_id = Column(UUID(), ForeignKey('items.uuid'))
parent = relationship('Item', remote_side=[uuid],
back_populates=children)
children = relationship('Item',
primaryjoin=and_(Item.parent_id ==
Item.uuid, Item.is_deleted == False),
back_populates=parent)
Right now when asking for all non-deleted children (any_item.children)
I get all all Items, not matter their is_deleted value. I found,
this is due to the query rendering as:
SELECT . FROM items WHERE items.parent_id = 'abc33424dsfsdf' AND 0
= false;
It seems like the Item.is_deleted == False is actually evaluated to
0 = false. How can I make SQLA use items.is_deleted = 0 instead? I
tried to use remote() and foreign() but they seem to be meant for
something else.
mmm nope, this is exactly the reason remote() was created,
self-referential with fine-grained rules inside the primaryjoin, since
you are on MySQL w/ a custom uuid type I did a full test to make sure
nothing weird going on, works fine see below and note remote():
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import uuid
class UUID(TypeDecorator):
Platform-independent GUID type.
Uses Postgresql's UUID type, otherwise uses
CHAR(32), storing as stringified hex values.
impl = CHAR
def load_dialect_impl(self, dialect):
if dialect.name == 'postgresql':
return dialect.type_descriptor(UUID())
else:
return dialect.type_descriptor(CHAR(32))
def process_bind_param(self, value, dialect):
if value is None:
return value
elif dialect.name == 'postgresql':
return str(value)
else:
if not isinstance(value, uuid.UUID):
return %.32x % uuid.UUID(value)
else:
# hexstring
return %.32x % value
def process_result_value(self, value, dialect):
if value is None:
return value
else:
return uuid.UUID(value)
Base = declarative_base()
class Item(Base):
__tablename__ = items
uuid = Column(UUID(), default=uuid.uuid4, primary_key=True)
is_deleted = Column(Boolean, default=False)
parent_id = Column(UUID(), ForeignKey('items.uuid'))
parent = relationship(
'Item', remote_side=[uuid],
back_populates=children)
children = relationship(
'Item',
primaryjoin=and_(remote(Item.parent_id) == Item.uuid,
remote(Item.is_deleted) == False),
back_populates=parent)
e = create_engine(mysql://scott:tiger@localhost/test, echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
i1id = uuid.uuid4()
i1, i2, i3, i4 = Item(uuid=i1id), Item(), Item(), Item(is_deleted=True)
i1.children = [i2, i3, i4]
s.add(i1)
s.commit()
s.close()
i1 = s.query(Item).filter_by(uuid=i1id).one()
print i1.children
query at the end is:
SELECT items.uuid AS items_uuid, items.is_deleted AS items_is_deleted,
items.parent_id AS items_parent_id
FROM items
WHERE items.parent_id = %s AND items.is_deleted = 0
--
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.
