Use Python to load data into Mysql

Question

is it possible to set up tables for Mysql in Python?

Here's my problem, I have bunch of .txt files which I want to load into Mysql database. Instead of creating tables in phpmyadmin manually, is it possible to do the following things all in Python?

1. Create table, including data type definition.
2. Load many files one by one. I only know this LOAD DATA LOCAL INFILE command to load one file.

Many thanks

Answer 1

It is. Typically what you want to do is use an Object-Retlational Mapping library.

Probably the most widely used in the python ecosystem is SQLAlchemy, but there is a lot of magic going on in it, so if you want to keep a tighter control on your DB schema, or if you are learning about relational DB's and want to follow along what the code does, you might be better off with something lighter like Canonical's storm.

EDIT: Just thought to add. The reason to use ORM's is that they provide a very handy way to manipulate data / interface to the DB. But if all you will ever want to do is to do a script to convert textual data to MySQL tables, than you might get along with something even easier. Check the tutorial linked from the official MySQL website, for example.

HTH!

Answer 2

Yes, it is possible, you'll need to read the data from the CSV files using CSV module.

http://docs.python.org/library/csv.html

And the inject the data using Python MySQL binding. Here is a good starter tutorial:

http://zetcode.com/databases/mysqlpythontutorial/

If you already know python it will be easy