[sqlalchemy] Re: Auto load problem with SQLAlchemy 0.3.10 and mySQL
Noufal wrote:
On Sep 20, 10:34 pm, Noufal [EMAIL PROTECTED] wrote:
[..]
I create the tables using SQLAlchemy. I'll send you the output in a
day, I'm away from the machine where this code is right now.
The output of the create table column looks like this
CREATE TABLE `stats` (
`sid` int(11) NOT NULL auto_increment,
`rid` int(11) default NULL,
`stagename` varchar(50) default NULL,
`description` varchar(250) default NULL,
`starttime` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`endtime` timestamp NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
The MyISAM storage engine doesn't retain foreign key information- to
autoload FKs you must create the table with a storage engine that
supports FKs such as InnoDB. That looks like:
Table('stats', metadata, ..columns.., mysql_engine='InnodB')
You can also use ALTER TABLE via mysql to change the storage engine and
add foreign keys if you've already got these MyISAM tables in production
(see the mysql docs.)
Supplying an explicit ForeignKeyConstraint clause for an autoloaded
MyISAM table is just fine too, if you want to keep using that storage
engine.
Cheers,
Jason
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Auto load problem with SQLAlchemy 0.3.10 and mySQL
The MyISAM storage engine doesn't retain foreign key information- to
autoload FKs you must create the table with a storage engine that
supports FKs such as InnoDB. That looks like:
Table('stats', metadata, ..columns.., mysql_engine='InnodB')
You can also use ALTER TABLE via mysql to change the storage engine and
add foreign keys if you've already got these MyISAM tables in production
(see the mysql docs.)
Thanks Jason. The concept of which engine mySQL was using didn't come
into my head. i just used the default.
Supplying an explicit ForeignKeyConstraint clause for an autoloaded
MyISAM table is just fine too, if you want to keep using that storage
engine.
This is what I've done right now.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Auto load problem with SQLAlchemy 0.3.10 and mySQL
Noufal wrote:
snip
I create two tables like so
run_table = sa.Table('runs',md,
sa.Column('rid', sa.Integer,
primary_key=True),
sa.Column('cmdline', sa.String(250)),
sa.Column('hostname', sa.String(20)),
sa.Column('workdir', sa.String(250)),
sa.Column('incremental', sa.Boolean),
sa.Column('user', sa.String(20)),
sa.Column('starttime', sa.TIMESTAMP),
sa.Column('endtime', sa.TIMESTAMP),
sa.Column('status',sa.String(20)),
sa.Column('machinetype',sa.String(20))
)
run_table.create()
stats_table = sa.Table('stats',md,
sa.Column('sid',sa.Integer,primary_key=True),
sa.Column('rid',sa.Integer,sa.ForeignKey('runs.rid')),
sa.Column('stagename',sa.String(50)),
sa.Column('description',sa.String(250)),
sa.Column('starttime',sa.TIMESTAMP),
sa.Column('endtime',sa.TIMESTAMP))
stats_table.create()
Then I can actually use these tables.
However, if I autoload them like so.
run_table = sa.Table('runs', md, autoload=True)
stats_table = sa.Table('stats', md, autoload=True)
(md is the metadata)
I get an error. The final assertion raised is like so
sqlalchemy.exceptions.ArgumentError: Error determining primary and/or
secondary join for relationship 'Run.stages (Stats)'. If the
underlying error cannot be corrected, you should specify the
'primaryjoin' (and 'secondaryjoin', if there is an association table
present) keyword arguments to the relation() function (or for
backrefs, by specifying the backref using the backref() function with
keyword arguments) to explicitly specify the join conditions. Nested
error is Can't find any foreign key relationships between 'runs' and
'stats'
snip
The table reflection doesn't seem to be picking up the foreign key
definition from stats.rid to runs.rid. In 0.3.10, SA uses a regular
expression on the output of 'SHOW CREATE TABLE' to find foreign keys
(round about line 1169 of databases/mysql.py). You could run 'SHOW
CREATE TABLE stats' yourself and try and figure out why the regular
expression isn't matching.
http://www.sqlalchemy.org/trac/browser/sqlalchemy/tags/rel_0_3_10/lib/s
qlalchemy/databases/mysql.py#L1169
However, you can provide your own foreign key definition while still
autoloading the table. Something like this should work:
stats_table = sa.Table('stats', md,
sa.ForeignKeyConstraint(['stats.rid'],
['runs.rid']),
autoload=True)
Also, I believe mysql table reflection is being completely reworked in
0.4, so the issue might go away.
Hope that helps,
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Auto load problem with SQLAlchemy 0.3.10 and mySQL
Noufal wrote:
Hello everyone,
I've recently picked up SQLAlchemy for a project that I'm working
on. I couldn't get a version newer than 0.3.10 (admin bureaucracy) and
have to use this.
I create two tables like so
run_table = sa.Table('runs',md,
sa.Column('rid', sa.Integer,
primary_key=True),
sa.Column('cmdline', sa.String(250)),
sa.Column('hostname', sa.String(20)),
sa.Column('workdir', sa.String(250)),
sa.Column('incremental', sa.Boolean),
sa.Column('user', sa.String(20)),
sa.Column('starttime', sa.TIMESTAMP),
sa.Column('endtime', sa.TIMESTAMP),
sa.Column('status',sa.String(20)),
sa.Column('machinetype',sa.String(20))
)
run_table.create()
stats_table = sa.Table('stats',md,
sa.Column('sid',sa.Integer,primary_key=True),
sa.Column('rid',sa.Integer,sa.ForeignKey('runs.rid')),
sa.Column('stagename',sa.String(50)),
sa.Column('description',sa.String(250)),
sa.Column('starttime',sa.TIMESTAMP),
sa.Column('endtime',sa.TIMESTAMP))
stats_table.create()
Then I can actually use these tables.
However, if I autoload them like so.
run_table = sa.Table('runs', md, autoload=True)
stats_table = sa.Table('stats', md, autoload=True)
(md is the metadata)
I get an error. The final assertion raised is like so
sqlalchemy.exceptions.ArgumentError: Error determining primary and/or
secondary join for relationship 'Run.stages (Stats)'. If the
underlying error cannot be corrected, you should specify the
'primaryjoin' (and 'secondaryjoin', if there is an association table
present) keyword arguments to the relation() function (or for
backrefs, by specifying the backref using the backref() function with
keyword arguments) to explicitly specify the join conditions. Nested
error is Can't find any foreign key relationships between 'runs' and
'stats'
Am I doing something wrong or is there something else wrong here? If
it's a bug that's been fixed in later versions, is there some kind of
a workaround I could use?
Did you originally create the tables through SQLAlchemy or are you
reflecting an existing schema? In either case I'd need to see the
output of SHOW CREATE TABLES for the problem table to make a diagnosis.
You can workaround this by manually adding a FK after autoloading:
stats_table.append_constraint(
sa.ForeignKeyConstraint(['rid'], ['runs.rid']))
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Auto load problem with SQLAlchemy 0.3.10 and mySQL
Did you originally create the tables through SQLAlchemy or are you reflecting an existing schema? In either case I'd need to see the output of SHOW CREATE TABLES for the problem table to make a diagnosis. I create the tables using SQLAlchemy. I'll send you the output in a day, I'm away from the machine where this code is right now. You can workaround this by manually adding a FK after autoloading: stats_table.append_constraint( sa.ForeignKeyConstraint(['rid'], ['runs.rid'])) I'll try that. Thanks for the help. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Auto load problem with SQLAlchemy 0.3.10 and mySQL
On Sep 20, 10:34 pm, Noufal [EMAIL PROTECTED] wrote: [..] I create the tables using SQLAlchemy. I'll send you the output in a day, I'm away from the machine where this code is right now. The output of the create table column looks like this CREATE TABLE `stats` ( `sid` int(11) NOT NULL auto_increment, `rid` int(11) default NULL, `stagename` varchar(50) default NULL, `description` varchar(250) default NULL, `starttime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `endtime` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`sid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
