On 10/28/10 2:24 AM, Fraser Burns wrote:
> We have need to extract the info out of a proprietry database and
> transfer it to MySQL. Because the are quite a number of tables we want
> to dynamically extract the field names from the existing base(they are
> stored inside the file) and allocate those same names to the create
> stage and then loading the data in.
Makes sense.
> Things that I have read all suggest that you need to hand code the table
> and field names in. You can't use %s except for VALUES.
But you could hand-code the sql script to later on pass to MySQL for execution.
You
could hand-code the CREATE TABLE, CREATE INDEX, etc. statements...
> Is it a matter of having to compile a string with all those parameters
> in first within the python code and then pass that to "loading function?
Here's a real-world example of what I do to create the tables and load the data:
{{{
1 import os
2 import sys
3 import glob
4
5 #host = "mac"
6 host = "sbs"
7 db = "ss"
8 user = "ss-admin"
9 passwd = "-------"
10
11 sql_scripts = glob.glob("*.sql")
12
13 for sql_script in sql_scripts:
14 print sql_script
15 os.system("mysql --host=%s --database=%s --user=%s --password=%s < %s" %
(host, db, user, passwd, sql_script))
}}}
A portion of one of those SQL files:
{{{
6 -- styles:
7 create table styles (id CHAR(40) PRIMARY KEY,
8 code CHAR(40) DEFAULT "",
9 name CHAR(64) DEFAULT "",
10 notes LONGTEXT DEFAULT "");
11 create index styles_code on styles(code);
12
13 insert into styles (id, code, name)
14 values ("HIDDEN_TILT", "001", "Hidden Tilt Rod");
15
16 insert into styles (id, code, name)
17 values ("FIXED_LOUVERS", "002", "Fixed Louvers");
}}}
So it is easy from Python, but you first need to get the data out of the
original
proprietary database and into this format, which is also probably
straightforward
with Python.
> Is this best done within dabo or at a python level?
Python level.
> Once we have it in MySQL it is all dabo thereafter.
Great! :)
Paul
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/[email protected]
