Python File Read + Write

Question

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]');

Paolo Bergantino · Accepted Answer · 2009-05-30 03:38:16Z

10

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 :)

answered May 30, 2009 at 3:38

Paolo Bergantino

490k83 gold badges523 silver badges437 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dave Over a year ago

...and regain that habit when you start golfing in Python :|

tom · Accepted Answer · 2009-05-30 03:55:25Z

1

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) )

edited May 30, 2009 at 3:55

answered May 30, 2009 at 3:48

tom

1,6491 gold badge13 silver badges14 bronze badges

Comments

othercriteria · Accepted Answer · 2009-05-30 03:32:11Z

0

The Python Standard Library has a module for CSV (comma separated value) file reading and writing that can be made to work on tab separated files like your one. It's probably overkill for this task.

answered May 30, 2009 at 3:32

othercriteria

4512 silver badges4 bronze badges

Comments

Etienne Perot · Accepted Answer · 2009-05-30 03:39:25Z

0

What you probably want is something like this: data=line.split("\t")
It'll give you a nice sequence object to work with.
(By the way, no need for semicolons in Python. There's one here: print line;)

As Dave pointed out, this might leave a newline in there. Call strip() on line before splitting, like so: line.strip().split("\t")

edited May 30, 2009 at 3:39

answered May 30, 2009 at 3:25

Etienne Perot

4,8928 gold badges43 silver badges50 bronze badges

4 Comments

Dave Over a year ago

This might leave a newline in the last field.

Etienne Perot Over a year ago

Then what about: line.strip().split("\t")

Dave Over a year ago

Eats all leading and trailing whitespace, not just the newline. This may or may not be a problem.

Etienne Perot Over a year ago

In his case, I think not, it's just a little single-use script to do his bidding. The split() method wouldn't be appropriate for real-world uses, he'd be better off using the csv module as other the other solutions say.

sqram · Accepted Answer · 2009-05-30 04:34:16Z

fwrite = open('/home/lyrae/Desktop/E/wp.users.sql','a')
fread = open('/home/lyrae/Desktop/E/users.txt','r')

for line in fread:
    line = line.split("\t")
    fwrite.write("insert into wp_users ( ID, user_login, user_name ) values (%s, '%s', '%s')\n" % (line[0], line[1], line[2]))

fread.close()
fwrite.close()

Assuming users.txt is:

1   John Smith  Developer   http://twiiter.com/johns    Chicago, IL
2   Billy bob   Developer   http://twiiter.com/johns    Chicago, IL
3   John Smith  Developer   http://twiiter.com/johns    Chicago, IL

wp.users.sql will look like:

insert into wp_users ( ID, user_login, user_name ) values (1, 'John Smith', 'Developer')
insert into wp_users ( ID, user_login, user_name ) values (2, 'Billy bob', 'Developer')
insert into wp_users ( ID, user_login, user_name ) values (3, 'John Smith', 'Developer')

Assuming only 1 tab separates the id, name, position

Collectives™ on Stack Overflow

Python File Read + Write

5 Answers 5

1 Comment

Comments

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related