Geek Answers Handbook

Reading file + Python

I am working on a database transfer from a custom MSSQL CMS to MYSQL - Wordpress. I use Python to read a txt file with \tselected columns and one line per line.

I am trying to write a Python script that will read this file (fread) and [eventually] create a finished MYSSQL .sql file with insert instructions.

The line in the file I'm reading looks something like this:

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 “blow up” every row so that I can access each column and do business on it?

I need to generate several MYSQL insert statements for each row I read. So ... for each line I read, I would generate something like:

INSERT INTO `wp_users` (`ID`, `user_login`, `user_name`) 
VALUES (line[0], 'line[2]', 'line[3]');
+5
5

, csv .

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

, , Python. :)

+9

, :

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

, , , : data=line.split("\t")
.
(, Python. : print line;)

, . () , : line.strip().split("\t")

0

Python CSV (, ) , , . , .

0
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 user.txt:

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

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 shares id, name, position

0
source

More articles:


All Articles