Re: [sqlalchemy] Error 1005 (errno 150) trying to create related tables using InnoDB
I looked into the AddConstraint class, and added some debugging info and found it wasn't being hit, because the fk was part of a CREATE. So I'd need to use @compiles to override both of those to ensure both of them render the references correctly. The single point where the magic happens is in compiler.py, in a function called 'define_constraint_remote_table'. Can I use @compiles to override that function? Right now I have manually added the following to dialects.mysql.base.MySQLDDLCompiler: def define_constraint_remote_table(self, constraint, table, preparer): Format the remote table clause of a CREATE CONSTRAINT clause. If using InnoDB, tables without manually-provided schemas will be formatted using the default connection schema when rendered in foreign key statements. This is because InnoDB (or perhaps mysql?) will interpret references without schemas as being in the same schema as the table being created/altered engine_key = '%s_engine' % self.dialect.name is_innodb = engine_key in table.kwargs and \ table.kwargs[engine_key].lower() == 'innodb' if is_innodb and table.schema is None: default_schema = table.bind.url.database constraint_schema = constraint.columns[0].table.schema if constraint_schema not in (default_schema, None): if the constraint schema is not the default, we need to add a schema before formatting the table table.schema = default_schema value = preparer.format_table(table) table.schema = None return value return preparer.format_table(table) This does exactly what I want it to, and ensures that in any cases where the referenced keys are in a different schema than the active table, the keys are prefixed by the schema. Is there a way to 'inject' this into the MySQLDDLCompiler? Would this version of the formatting function not be more appropriate than the current, as the current is unable to handle a simple 2 model relation when they are in different schemas? On Wednesday, August 21, 2013 2:52:41 PM UTC-7, Michael Bayer wrote: On Aug 21, 2013, at 5:37 PM, Gerald Thibault diesel...@gmail.comjavascript: wrote: To make it sqlalchemy specific, how do i cause generated CREATE statements to use absolute schema.table names for foreign key references? you either need to specify schema in your referenced Table def, or you'd otherwise have to intercept the AddConstraint construct using @compiles to inject the schema name that you'd want there. I realized the reason MyISAM had no issue with it was because it ignores all those lines, so even if they were wrong (which they seem to be), it wouldn't care. Here is the relevant output from SHOW ENGINE STATUS INNODB: LATEST FOREIGN KEY ERROR 130821 13:22:18 Error in foreign key constraint of table test2/registrations: FOREIGN KEY(user_id) REFERENCES user_vars (id) )ENGINE=InnoDB: Cannot resolve table name close to: (id) )ENGINE=InnoDB It seems the schema is mandatory when operating cross-schema. Is there a way to get this behavior from sqlalchemy? If MyISAM ignores the FK declarations, and InnoDB requires a schema in order to have cross-schema fk references, it seems like using schema.table format would fix this. On Wednesday, August 21, 2013 1:57:07 PM UTC-7, Michael Bayer wrote: you might try asking this as a generic MySQL question on stackoverflow, I don't really know how MySQL does cross-schema work. my rough understanding was not much. On Aug 21, 2013, at 4:17 PM, Gerald Thibault diesel...@gmail.com wrote: I have a User class, and a Registration class with a FK to User.id. When I try to create these on a db using InnoDB as default, I get this error: sqlalchemy.exc.OperationalError: (OperationalError) (1005, Can't create table 'test2.registrations' (errno: 150)) '\nCREATE TABLE test2.registrations (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tuser_id INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(user_id) REFERENCES user_vars (id)\n)\n\n' () If i copy this query and try it manually via MySQL Workbench, it still fails. If I prepend the default schema to the table (user_vars - 'test.user_vars'), the query succeeds. I'm not entirely sure what is happening here. In the absence of an explicit schema, I thought mysql used the active schema to handle table lookups, but it looks like this isn't the case. Is it 'switching' the active schema to the one hosting the new table? Also, this works perfectly with MyISAM tables, so I have even less to go on. Any ideas? Do I need explicit schema declarations for every fk declared? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To
Re: [sqlalchemy] Error 1005 (errno 150) trying to create related tables using InnoDB
On Aug 22, 2013, at 6:08 AM, Gerald Thibault dieselmach...@gmail.com wrote: I looked into the AddConstraint class, and added some debugging info and found it wasn't being hit, because the fk was part of a CREATE. So I'd need to use @compiles to override both of those to ensure both of them render the references correctly. The single point where the magic happens is in compiler.py, in a function called 'define_constraint_remote_table'. Can I use @compiles to override that function? Right now I have manually added the following to dialects.mysql.base.MySQLDDLCompiler: def define_constraint_remote_table(self, constraint, table, preparer): Format the remote table clause of a CREATE CONSTRAINT clause. If using InnoDB, tables without manually-provided schemas will be formatted using the default connection schema when rendered in foreign key statements. This is because InnoDB (or perhaps mysql?) will interpret references without schemas as being in the same schema as the table being created/altered engine_key = '%s_engine' % self.dialect.name is_innodb = engine_key in table.kwargs and \ table.kwargs[engine_key].lower() == 'innodb' if is_innodb and table.schema is None: default_schema = table.bind.url.database constraint_schema = constraint.columns[0].table.schema if constraint_schema not in (default_schema, None): if the constraint schema is not the default, we need to add a schema before formatting the table table.schema = default_schema value = preparer.format_table(table) table.schema = None return value return preparer.format_table(table) This does exactly what I want it to, and ensures that in any cases where the referenced keys are in a different schema than the active table, the keys are prefixed by the schema. Is there a way to 'inject' this into the MySQLDDLCompiler? if you specify a name and use_alter=True on your ForeignKey it should use the AddConstraint feature, which is easier to override. Would this version of the formatting function not be more appropriate than the current, as the current is unable to handle a simple 2 model relation when they are in different schemas? first I'd want to fully understand and verify exactly what's going on with this, if you have some definitive documentation that refers to this it would be helpful. from there I'd probably still prefer to make this a table-level flag (I considered a dialect-level flag, but this is really part of mysql_engine's behavior), particularly because we prefer CREATE TABLE statements to be correct for the target database even in the absence of a database connection. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Creating Tables from a Schema defined in YAML
On Wed, Aug 21, 2013 at 11:26 PM, monosij.for...@gmail.com wrote: Hello - Wondering if someone could please help me with this: I have created a schema definition file in YAML which I read into a dict. I am used to statically creating a table in this form: tableName = Table (theTableName, Metadata, Column(column1, String), Column(column2, String), Column(coulumn3, String) ... ) I am trying to see if there is a way to dynamically create a table using the column definition in the YAML file. So it would need to iterate over the column names and the types. I tried with creating the string = 'Column(column1, String), Column(column2, String), Column(coulumn3, String) ...' by iterating over the dict keys and then calling as above - but that did not work. Just wondering if there would be another way to do this. I looked at mapper as well but then that is for objects. Thanks. Mono The answer to this probably depends on exactly what your schema looks like, but based on what you've told us so far, I would probably try to create all the columns first, get them into a list or tuple, and then use star args to pass them into the Table constructor. Something like this: columns = [Column(name, type) for name, type in some_kind_of_iterable_generated_from_your_yaml] table = Table(theTableName, Metadata, *columns) though obviously that's a very rough example -- Kevin Horn -- 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.
Re: [sqlalchemy] Creating Tables from a Schema defined in YAML
Hi Kevin - Thanks for your help. I am, however, unable to call Column on the tuple (attrname, 'String'). I tried like this: columns = [] for attribute in attrList: tup = attribute, 'String' tupC = Column(tup) // does not work columns.append(tup) So Columns (tup) did not work and I also tried calling Column on the tuples list I created in the iterator. So my call to create a table as in: theTable = Table (tableName, Metadata, *columns) - does not work. I also tried w/o the column call but did not expect it to work. So sounds like would be a 'star args' for the Column call as well but am unable to find it. Please let me know if I am approaching this incorrectly (I assume I am). Also, I could generate a SQL create statement from my YAML file. Would there be a way to use the Table command to create a table from that - but it would defeat the purpose as if go with a different DBMS I have to make sure the SQL is addressed? Or does sqlalchemy take care of that as well? If there is some documentation you could refer me to in how this dynamic generation is implemented will be great. Also, since it is a tuple I assume I can put in PK / FK information as well. And I can help create some of this documentation if it does not exist. Its a great library, very helpful, just trying to find some flexibility. Thank you for your help. Mono On Thu, Aug 22, 2013 at 11:18 AM, Kevin Horn kevin.h...@gmail.com wrote: On Wed, Aug 21, 2013 at 11:26 PM, monosij.for...@gmail.com wrote: Hello - Wondering if someone could please help me with this: I have created a schema definition file in YAML which I read into a dict. I am used to statically creating a table in this form: tableName = Table (theTableName, Metadata, Column(column1, String), Column(column2, String), Column(coulumn3, String) ... ) I am trying to see if there is a way to dynamically create a table using the column definition in the YAML file. So it would need to iterate over the column names and the types. I tried with creating the string = 'Column(column1, String), Column(column2, String), Column(coulumn3, String) ...' by iterating over the dict keys and then calling as above - but that did not work. Just wondering if there would be another way to do this. I looked at mapper as well but then that is for objects. Thanks. Mono The answer to this probably depends on exactly what your schema looks like, but based on what you've told us so far, I would probably try to create all the columns first, get them into a list or tuple, and then use star args to pass them into the Table constructor. Something like this: columns = [Column(name, type) for name, type in some_kind_of_iterable_generated_from_your_yaml] table = Table(theTableName, Metadata, *columns) though obviously that's a very rough example -- Kevin Horn -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/2eY6URGd2mY/unsubscribe. To unsubscribe from this group and all its topics, 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. -- 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] Feedback appreciated
Hi all, First I want to say how much I appreciate SQLAlchemy. I think it is by far the best ORM available for any language. Thank you Mike for this wonderful piece of software. I can’t imagine how many countless hours you’ve put into this. From the day I started coding I’ve always been enthusiastic about databases. 2007 I created Doctrine, which is now the most popular ORM for PHP. Over the years I’ve switched to Python (I could’ve switched to Ruby, but didn’t like Rails’ ORM at all). Now I’m the CEO of a company called Fast Monkeys. We employ 15 people currently and develop solely with Python, SQLAlchemy and Flask. One of the key principles of our company is to contribute to our chosen open source technologies as much as possible. There are couple of interesting projects I would like to hear some feedback: SQLAlchemy-Utils https://github.com/kvesteri/sqlalchemy-utils Provides number of things for SQLAlchemy. Some highlights: - Batch fetching utilities (experimental at the moment) - We had a real life scenario were join loading, lazy loading and subquery loading were all too slow. - I used this https://groups.google.com/forum/#!topic/sqlalchemy/vHQlm0U5f2k as an inspiration. I agree this is a bottomless hole but I’m nevertheless willing to try to make something useful for SQLAlchemy users. :) - Number of new datatypes SQLAlchemy-Continuum https://github.com/kvesteri/sqlalchemy-continuum Hibernate Envers style versioning for SQLAlchemy declarative models. WTForms-Alchemy https://github.com/kvesteri/wtforms-alchemy Easily create WTForms forms from SQLAlchemy declarative models. The documentation is pretty poor on first two projects. I will add more docs in the following weeks. -- 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.
Re: [sqlalchemy] Modification tracking
Dear Michael,
thanks for the detailed response.
On Mittwoch, 21. August 2013 16:55:18 Michael Bayer wrote:
On Aug 21, 2013, at 12:40 PM, Hans-Peter Jansen h...@urpla.net wrote:
Hi,
being new to SQLAlchemy, I try to get my way through it.
In an application, I have rather elaborate needs to track changes.
I've defined 3 classes with declarative, where the main class has
relationships with two auxiliary classes, that refer to the main class
with foreign references. All pretty basic stuff.
Now I need to track all modifications to all fields, including the
relationship list objects.
What is the best way to accomplish this task with SQLAlchemy?
Is there some boilerplate available to support this, or do I have to carry
around two objects and compare them item by item?
you can intercept changes on attributes using the attribute event system:
http://docs.sqlalchemy.org/en/rel_0_8/orm/events.html#attribute-events
otherwise you can get at the changes on an attribute after the fact (but
before a flush) using the history interface:
http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html?highlight=history#sq
lalchemy.orm.attributes.History
Hmm, it looks like that needs to be applied on every column attribute..
you can get a list of all attributes mapped using mapper.attrs:
http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html?highlight=mapp
er.attrs#sqlalchemy.orm.mapper.Mapper.attrs
from here. Since I need to track all 'net' changes, that's rather unpractical.
I've changed my code to cope with best practices hopefully (from what I can
extract from the overwhelming docs).
That is: just change instance attributes in place, and check for dirtiness
later on, with session.is_modified(obj, passive = True). I've chosen this
interface, because the fact of *really* being modified is essential here.
This scheme would do just fine, but doesn't work as expected.
See attached code. Depending on modification state of parent (line 51), the
modification state of the child is detected (parent changed) or not (parent
unchanged).
In my real code, it's the other way around, modifications to relations are
detected (as I iterate through all of them), but changes to the parent
aren't, although the correct SQL UPDATE code is executed after commit(). Since
it isn't detected correctly, my app falls flat on its nose..
Do you have any idea on this one?
Cheers,
Pete
--
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.
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import relationship, backref, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
ERR = 1
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
name = Column(String(100), nullable = False, index = True)
children = relationship('Child', backref = 'parent',
single_parent = True, # lazy = 'joined',
cascade = 'all, delete-orphan')
def __repr__(self):
cl = [repr(c) for c in self.children]
return 'Parent(%r, children: %s)' % (self.name, ', '.join(cl))
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
name = Column(String(100), nullable = False, index = True)
parent_id = Column(Integer, ForeignKey('parent.id'), nullable = False)
def __repr__(self):
return 'Child(%r)' % (self.name)
if __name__ == '__main__':
engine = create_engine('sqlite://', echo = True)
Base.metadata.create_all(engine)
session = sessionmaker(engine, expire_on_commit=False)()
def pr(obj, exp, msg):
res = session.is_modified(obj, passive = True)
print msg + ',', 'expected: %s,' % exp, 'outcome: %s,' % res, exp == res and 'okay' or 'FAILED'
p = Parent(name = 'pa')
c1 = Child(name = 'li')
p.children.append(c1)
print 'Starting with:', p
session.add(p)
session.commit()
pr(p, False, 'initial session committed')
if ERR:
pr(p, False, 'parent not renamed')
else:
p.name = 'po'
pr(p, True, 'parent renamed to po')
c1.name = 'lo'
pr(c1, True, 'child renamed to lo, testing child')
pr(p, True, 'child renamed to lo, testing parent')
session.commit()
Re: [sqlalchemy] Modification tracking
On Donnerstag, 22. August 2013 23:58:17 Hans-Peter Jansen wrote: Dear Michael, Pardon, I'm using 0.8.2 ATM. Cheers, Pete -- 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.
Re: [sqlalchemy] Modification tracking
On Aug 22, 2013, at 5:58 PM, Hans-Peter Jansen h...@urpla.net wrote: Dear Michael, Hmm, it looks like that needs to be applied on every column attribute.. you can get a list of all attributes mapped using mapper.attrs: http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html?highlight=mapp er.attrs#sqlalchemy.orm.mapper.Mapper.attrs from here. Since I need to track all 'net' changes, that's rather unpractical. if you're just looking for dirty you can look at session.dirty itself, such as in a before_flush() handlernot sure what the specific scenario is. That is: just change instance attributes in place, and check for dirtiness later on, with session.is_modified(obj, passive = True). I've chosen this interface, because the fact of *really* being modified is essential here. This scheme would do just fine, but doesn't work as expected. See attached code. Depending on modification state of parent (line 51), the modification state of the child is detected (parent changed) or not (parent unchanged). it looks like this code makes a change to p.children[0].name, and then expects that session.is_modified(p) would be True.is_modified() is per-object, so modifying Child means that just Child is modified, Parent hasn't changed at all. If you want to make an is_modified() that recurses through object graphs, you'd need to traverse down yourself through related attributes. I can show you what that looks like if this is what you want. In my real code, it's the other way around, modifications to relations are detected (as I iterate through all of them), but changes to the parent aren't, although the correct SQL UPDATE code is executed after commit(). Since it isn't detected correctly, my app falls flat on its nose.. well if you change Child.name, the only UPDATE needed is on the child table. the parent table doesn't require an UPDATE in this case which is why session.is_modified() returns False. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Feedback appreciated
On Aug 22, 2013, at 3:37 PM, Konsta Vesterinen konsta.vesteri...@gmail.com wrote: Hi all, First I want to say how much I appreciate SQLAlchemy. I think it is by far the best ORM available for any language. Thank you Mike for this wonderful piece of software. I can’t imagine how many countless hours you’ve put into this. From the day I started coding I’ve always been enthusiastic about databases. 2007 I created Doctrine, which is now the most popular ORM for PHP. wow that is quite a compliment, I of course have heard of Doctrine and met many PHP users who use it extensively. This is really amazing that you're A. using Python now B. using SQLAlchemy and C. writing full blown software for it, wow ! Over the years I’ve switched to Python (I could’ve switched to Ruby, but didn’t like Rails’ ORM at all). Now I’m the CEO of a company called Fast Monkeys. We employ 15 people currently and develop solely with Python, SQLAlchemy and Flask. One of the key principles of our company is to contribute to our chosen open source technologies as much as possible. There are couple of interesting projects I would like to hear some feedback: SQLAlchemy-Utils https://github.com/kvesteri/sqlalchemy-utils Provides number of things for SQLAlchemy. Some highlights: Batch fetching utilities (experimental at the moment) We had a real life scenario were join loading, lazy loading and subquery loading were all too slow. I used this https://groups.google.com/forum/#!topic/sqlalchemy/vHQlm0U5f2k as an inspiration. I agree this is a bottomless hole but I’m nevertheless willing to try to make something useful for SQLAlchemy users. :) Number of new datatypes SQLAlchemy-Continuum https://github.com/kvesteri/sqlalchemy-continuum Hibernate Envers style versioning for SQLAlchemy declarative models. WTForms-Alchemy https://github.com/kvesteri/wtforms-alchemy Easily create WTForms forms from SQLAlchemy declarative models. These are all really interesting projects and I had a lot of thoughts looking at all of them just briefly. I also wonder at what points within here should/can some of this be part of SQLA itself, or not. Here's my notes: wtforms: 1. in all cases, use inspect(cls) to get at a Mapper. if on 0.7, use class_mapper(cls). but it would be great if you could target 0.8 and up as a lot of functions were added for exactly these kinds of use cases (See http://docs.sqlalchemy.org/en/rel_0_8/faq.html#how-do-i-get-a-list-of-all-columns-relationships-mapped-attributes-etc-given-a-mapped-class) 2. ClassManager.values() is not terrible, but again isn't super public. you can use mapper.attrs as well as mapper.column_attrs and others as of 0.8. versioning: 1. wow this is really ambitious. 2. unit_of_work.py: you can tell if a statement is an INSERT/DELETE usually by looking at context.isinsert/context.isdelete, and also the table name you can get from context.statement.table (something like that). Similar things can be done where I see you're regexping the DELETE later on. Digging into the string is fine but once you're targeting the broad spectrum of scenarios, like users that are adding SQL comments and such to their SQL, backends that don't actually use SQL, you want to stick with inspecting the expression trees as much as possible. 3. make schema object names configurable, i.e. transaction_id 4. This code looks great but I'd still be scared to use it, because versioning is such a particular thing, not to mention interactions with other schema complexities.But I don't say that to be discouraging, just to state how non-trivial a problem this is. When i do versioning for real, there's always weird quirks and things specific to the app, which are easier to hardcode in my versioning code rather than having to configure a 3rd party library to do it. it's why i kept it as just an example in SQLA itself, it's a huge job... but if you can make this extension successful, that'll be very impressive. In the docs it would be nice if I could see immediately what happens to the SQL schema when I use this. sqlalchemy_utils: 1. have coercion_listener configure itself? coercion_listener.configure(). since it's global usually, and you could always pass a target base class to configure() as an option. 2. ScalarListType vs. Postgresql ARRAY ? same/better? should SLT use ARRAY on a PG backend ? 3. operators for types! I see these are mostly string storage but you can start adding special operations as needed using TypeEngine.Comparator: http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#types-operators e.g. look at all the operations that pg.ARRAY has (contains, indexed access, concatenation, etc.). then you can make all these types *really* slick. 4a. batch_fetch - h. I see the idea is avoid JOIN by just feeding the keys into an IN (caveat there, IN works well for small lists, but less so for large
