[sqlalchemy] Re: Mysql issue with big composite primary keys
Iain Duncan iainduncanli...@gmail.com writes: Hi folks, I've been googling and cargo culting to no avail here, hope someone can help. I'm trying to get an app running on MySQL on OS X, that was running fine on MySQL on Linux. The issue is I have some large composite primarky keys: class VarTrigger(Base): assoc table that associates actions when a var is set __tablename__ = var_trigger # actual primary key is var_id, p_value var_id = Column(Integer, ForeignKey(var.id), nullable=True, primary_key=True) p_value = Column( String(255), primary_key=True, nullable=False ) ... When I try to create the tables I'm getting the following: qlalchemy.exc.OperationalError: (OperationalError) (1709, 'Index column size too large. The maximum column size is 767 bytes.') Most probably, the MySQL settings differ from one installation to the other, and on OS X the database has been created with UTF-8 as default codec. At that point, MySQL considers that each character in a string *may* consume up to 3 (or 4, depending on the exact codec) bytes, so it assume that your p_value string needs 255*3 bytes, that summed with var_id size exceeds maximum allowed index entry size. There may be some setting to change that limit. I've been trying out things like changing character collation and so one with no luck. Collation is different from the encoding: the former defines the ordering rules, the latter the actual content encoding (that is, how the data is written to permanent storage). hth, ciao, lele. -- nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia. l...@metapensiero.it | -- Fortunato Depero, 1929. -- 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: [Sqlachemy Core] Help on joining 3 tables
Hello! After some research on the *FromClause *object, I have figured out what should I do. So, my I got my code to work as follows: # Considering the table objects: A, B and C... # I first made a join clase over the A table. *J1 = A.join(A.c.some_col == B.c.some_col)* # The second join is applyed from within the frist join *J2 = J1.join(A.c.other_col == C.c.other_col)* # The select references each table explicitly, and the select_from clause # uses the second join *sel = select([A.c.id,* * B.c.id,* * C.c.id]).\* * select_from(J2)* # And we're done! *connection.execute(sel)* On Friday, November 7, 2014 5:16:36 PM UTC-2, Tiago Guimarães wrote: Hi. Could anyone help me with translating the following SQL to Sqlalchemy Core? SELECT A.id, B.id, C.id FROM A LEFT OUTER JOIN B ON B.some_col = A.some_col LEFT OUTER JOIN C ON C.other_col = A.other_col The problem I'm facing is that the *Select().select_from(fromclause*) method only accepts only one *fromclause* object. In my mind, it would be just nice if *select_from* could accept multiple join objects, as it seems to happen on the raw SQL exerpt above. How would you guys compose this selectable using core? Thanks in advance!!! -- 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] Migrating databases: Alembic vs. Liquibase, Flyway, ??
I'm gradually working toward getting serious about controlled DB migration (version control for DBs), and have been following the Alembic announcements with interest. (We're already using SA successfully in a Twisted environment, just making sure that all DB access is encapsulated in deferreds.) Recently, I came across mention of two projects that look similar: Liquibase (http://www.liquibase.org/index.html) and Flyway (http://flywaydb.org/). Both of them look to be based on Java, but that aside, I'd be interested to know how they compare in terms of features, maturity, etc. Thanks for any info, -- Don Dwiggins Advanced Publishing Technology -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] a problem recently encountered with the example 'Explain'
Using this https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Explain if I take a select statement, and then produce an insert statement using i = some_table.insert(inline=True).from_select( some_select ) and then: e = Explain(i) I can't execute(e) because the statement is rendered with RETURNING (despite the inline=True). What's going on here? -- Jon Software Blacksmith -- 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] a problem recently encountered with the example 'Explain'
i made this change: text += compiler.process(element.statement, **kw) can you try that? thanks On Nov 10, 2014, at 4:32 PM, Jon Nelson jnel...@jamponi.net wrote: Using this https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Explain if I take a select statement, and then produce an insert statement using i = some_table.insert(inline=True).from_select( some_select ) and then: e = Explain(i) I can't execute(e) because the statement is rendered with RETURNING (despite the inline=True). What's going on here? -- Jon Software Blacksmith -- 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] a problem recently encountered with the example 'Explain'
OK nevermind, do this: class explain(Executable, ClauseElement): def __init__(self, stmt, analyze=False): self.statement = _literal_as_text(stmt) self.analyze = analyze # helps with INSERT statements self.inline = getattr(stmt, 'inline', None) On Nov 10, 2014, at 5:16 PM, Michael Bayer mike...@zzzcomputing.com wrote: i made this change: text += compiler.process(element.statement, **kw) can you try that? thanks On Nov 10, 2014, at 4:32 PM, Jon Nelson jnel...@jamponi.net wrote: Using this https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Explain if I take a select statement, and then produce an insert statement using i = some_table.insert(inline=True).from_select( some_select ) and then: e = Explain(i) I can't execute(e) because the statement is rendered with RETURNING (despite the inline=True). What's going on here? -- Jon Software Blacksmith -- 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. -- 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] a problem recently encountered with the example 'Explain'
Now the statement doesn't render with RETURNING, however I still get this: AttributeError: 'Explain' object has no attribute '_returning' -- 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 issue with big composite primary keys
This is a well known mysql-ism I remember reading that a short-term fix to this... is to change VARCHAR(256) to VARCHAR(255) It's fixed in some versions of the db (https://answers.launchpad.net/maria/+question/241612) It looks like 5.6.3 is the earliest : http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix -- 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.
