Hi all,
i am writing a tool to import MySQL dumps into different databases
(MySQL and PostgreSQL). The tool will create suitable databases and
tables within them, so i wanted to use metadata.create_all() for table
creation.
The dumps are in the collowing format:
--- snip ---
INSERT INTO `categorylinks` VALUES (0,'Alias_使用者','I18n',
20080218135752),(0,'Ru-0_使用者','I18n',20080218135752),(9,'維基執行主
編','Mountain',20070130023546),(9,'維基壓力
等級4的維基人','Mountain',20070130023546),(9,'維基推廣專家','User:Mountain',
20070127235033),(9,'維基站務專家','User:Mountain',20070127235033),(9,'维基百科管理
员','Mountain',20070127234936),(12,'優良條目','数学',20080402070008) ....
--- snip ---
so they contain a lot of multiple-values inserts.
I have a question regarding the ForeignKey constrains enforced by the
underlying database. The table definitions look
like:
--- snip ---
Table(u'links', metadata,
Column(u'from', Integer,
ForeignKey('page.id'),
nullable=False,
server_default='0',
),
Column
....
)
--- snip ---
The problem is that MySQL (with InnoDB) and PostgreSQL will check the
FK constraints *for every single datum* which will slow down the
import process significantly.
Is there any way to (in order of preference):
a) Temporarily disable FK constraint checks
b) Defer the creation of ForeignKey constraints and create them
'automatically' after the import
Something along the lines:
--- snip ---
....
ForeignKey('foo.id', create_on_demand=True)
....
for table in metadata.tables:
table.create_missing_fks(checkexist=True)
--- snip ---
c) Create the Tables without FKs and add them later on?
I assume that c) will need some way to ALTER the tables from within
SA. Do I have to write these ALTER TABLE statements for each dialect i
want to support, or is there a better way to do that? (sqlalchemy-
migrate maybe)
Can i reload the new metadata within SA (rereflect?)
What will happen if i have indeed missing FKs, which is unlikely but
might happen.
with kind regards
Wolodja Wentland
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
