[Tutor] Rapidly Importing CSVs with Python into MySQL

[Alan Gauld]

On 20/07/12 21:34, Fred G wrote:
> Hi--
>
> This question has to do with MySQL but is fundamentally a python
> question, so apologies if it seems tangential initially...
>
> I've written a long python file that creates all the tables in database

To be honest I'd normally do that by writing a SQL file and executing 
that directly on the database. I'm not sure how you do that in MySql but 
every database I've worked with had some kind of SQL interpreter that 
you could run SQL directly.

I use Python when I want to process data not when I want to create or 
modify tables

> import MySQLdb as mysql
> statement = """CREATE DATABASE UN"""
> db = mysql.connect(this stuff is correct)
> cursor = db.connect()
> cursor.execute(statement)
> sql = """CREATE TABLE UN.1(
>           id VARCHAR(255),
>           name VARCHAR(255),
>           age INT
>         )"""
> cursor.execute(sql)
> statement = """LOAD DATA LOCAL INFILE...(this stuff is correct)"""
> db.commit()
> db.close()

You shouldn't need to close the database connection each time.

> db = mysql.connect(this stuff is correct)
> cursor = db.connect()
> sql = """CREATE TABLE UN.2(
>           id VARCHAR(255),
>           fname VARCHAR(255),
>           lname VARCHAR(255),
>           age INT
>         )"""
> cursor.execute(sql)
> statement = """LOAD DATA LOCAL INFILE...(this stuff is correct)"""
> db.commit()
> db.close()
>
> I have a lot of tables here and I have a few questions:
> 1) When I run this Python file, only the first table is created.  Why
> does the interpreter think that the statements are done at this point?

You have a bug or MySql has some kind of restriction on your user account.

Do you get any error messages?
Try running the code outside IDLE and see if anything gets reported. 
Sometimes IDEs like IDLE will mask important messages.

> tried experimenting with taking out all the "db.close()" commands until
> the last one, but that didn't work, either.  More importantly, how can I
> get it such that I can press run all and all these tables are built?

You need to fix the problem but...
You shouldn't really run programs inside IDLE except when you are 
developing/testing them. IDLE is only meant for use when writing code. 
Executing it should be done from the OS using the raw python interpreter.

>   I'm going to be re-building these (and other database's tables) pretty
> frequently, so it's pretty frustrating to have to copy and paste out of
> the .py file into the idle editor every single statement...

Not sure what you mean there? The IDLE editor is where your .py file 
should be. I'm assuming you mean from the IDLE editor into the IDLE shell?

> 2) Is there a shortkey command to run line-by-line in a file using IDLE
> in Python 2.7? (Some languages have this capacity, but a Google search
> of python was not too promising about this feature...)

Yes, there is a debugger in IDLE. You can set break points and then step 
over or into functions as needed. But usually inserting a few print 
statements into your code is all you need to monitor progress.
For example try putting a print just before every execute() call to 
print out the actual SQL command string used.

That will show which statement was last executed and you can examine the 
SQL to check there is nothing wrong with it.

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/