I am working on porting over a database from a custom MSSQL CMS to MYSQL - Wordpress. I am using Python to read a txt file with \t
delineated columns and one row per line.
I am trying to write a Python script that will read this file (fread) and [eventually] create a MYSSQL ready .sql file with insert statements.
A line in the file I'm reading looks something like:
1 John Smith Developer http://twiiter.com/johns Chicago, IL
My Python script so far:
import sys
fwrite = open('d:/icm_db/wp_sql/wp.users.sql','w')
fread = open('d:/icm_db/users.txt','r')
for line in fread:
print line;
fread.close()
fwrite.close()
How can I "implode" each line so I can access each column and do business on it?
I need to generate multiple MYSQL insert statements per line I read. So... for each line read, I'd generate something like:
INSERT INTO `wp_users` (`ID`, `user_login`, `user_name`)
VALUES (line[0], 'line[2]', 'line[3]');
Python has a built-in open() function to open a file. This function returns a file object, also called a handle, as it is used to read or modify the file accordingly. We can specify the mode while opening a file. In mode, we specify whether we want to read r , write w or append a to the file.
'r' – Read Mode: This is the default mode for open(). The file is opened and a pointer is positioned at the beginning of the file's content. 'w' – Write Mode: Using this mode will overwrite any existing content in a file. If the given file does not exist, a new one will be created.
Although this is easily doable, it does become easier with the csv module.
>>> import csv
>>> reader = csv.reader(open('C:/www/stackoverflow.txt'), delimiter='\t')
>>> for row in reader:
... print row
...
['1', 'John Smith', 'Developer', 'http://twiiter.com/johns', 'Chicago, IL']
['2', 'John Doe', 'Developer', 'http://whatever.com', 'Tallahassee, FL']
Also, as pointed out, semicolons are not needed in Python. Try to kick that habit :)
Knowing the exact number of columns helps self document your code:
fwrite = open("d:/icm_db/wp_sql/wp.users.sql","w")
for line in open("d:/icm_db/users.txt"):
name, title, login, location = line.strip().split("\t")
# Double up on those single quotes to avoid nasty SQL!
safe_name = name.replace("'","''")
safe_login = name.replace("'","''")
# ID field is primary key and will auto-increment
fwrite.write( "INSERT INTO `wp_users` (`user_login`, `user_name`) " )
fwrite.write( "VALUES ('%s','%s');\n" % (safe_name,safe_login) )
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With