How to insert data from one table to another?

What's the best way using Python to insert data from dbfpy into MySQL?

• I'm using the dbfpy module to read data from DBF files with the intention of writing that same data to equivalent MySQL tables. Here's a rough version of my code: http://pastebin.com/DeEr9tqX The MySQL table I'm trying to insert into contains one additional field which I need to populate with the concept_id value set at the top of the script. That value is not part of the DBF records (rec). What's the best way for me to insert this data?

• Answer:

  Seems like you could just add the additional field to each record in your loop (rec['concept_id'] = concept_id before line 31 of your pastebin code) and add the extra column into your INSERT statement. I'm assuming that rec is a dictionary or other writable mapping though I haven't used the dbfpy module recently enough to know.  If not then create a new mapping and updates it with the key/value pairs from your existing rec object. Incidentally I would suggest that you explicitly list your column names in your INSERT statement: cur.execute("INSERT INTO discount (tx_id, date, seller_id ..., concept_id) VALUES (%s, %s, %s, %s, %s, %s)", rec) ... where I've made up some column names and added the "concept_id" column in this example. The reason to adhere to this best practice is that your code will then be more robust even if there are later changes to the table definition in your database schema.  So long as any additional columns are optional (accept NULL values or have defaults) or the code defining each record/dictionary is updated then you INSERT statements won't need to be changed to keep in sync.  (Similarly you should always explicitly enumerate the intended column names in your SELECT statements). Using explicit columns names throughout your SQL code will also allow your code to function even if, at some point, you recreate your schema and change the order in which some of your columns are defined.  The SQL engine will return only the subset of columns you specify and in the order in which they're specified in your statement. (Not an issue when using dictionary-like objects as your rows, but it can be when using tuples, for example). (This tip is a best practice regardless of whether you're using Python or any other language to access your SQL RDBMS).