String Formatting: Iterate the row values from a csv file

Question

I have a csv file. I want to iterate the rows and produce sql string. I tried the solutions in stackoverflow but could not manage to fix it.

csv file

rating,product_type,upc,title

Three,Books,a897fe39b1053632,A Light in the Attic

One,Books,6957f44c3847a760,Soumission

python file starts with the following code

path = r'C:\Users\HP\PycharmProjects\book_crawler\books\items.csv'
file = open(path, 'rt')

I tried different version for string formatting. Some of the errors I get:

IndexError: tuple index out of range

for row in file:
    print ('INSERT IGNORE INTO books_table(rating, product_type, upc, title) VALUES({},{},{},{})'.format(row))

TypeError: not all arguments converted during string formatting

for row in file:
    print ('INSERT IGNORE INTO books_table(rating, product_type, upc, title) VALUES({0},{1},{2},{3})' % row)

TypeError: not all arguments converted during string formatting

for row in file:
    print ('INSERT IGNORE INTO books_table(rating, product_type, upc, title) VALUES({0},{1},{2},{3})' % (row,))

TypeError: not all arguments converted during string formatting

for row in file:
    print ('INSERT IGNORE INTO books_table(rating, product_type, upc, title) VALUES({0},{1},{2},{3})' % tuple(row))

Start by doing print(row). That will explain to us (and you) where the number of arguments is going wrong. — BoarGules
– BoarGules, Commented Apr 8, 2018 at 20:48
You're probably parsing the first line of the csv, use next() to skip it, also make sure row isn't empty before processing it. Divide your code into blocks and debug it properly. — Pedro Lobito
– Pedro Lobito, Commented Apr 8, 2018 at 20:49
This is the part of my code. I am scraping some data to load mysql. — Land Owner
– Land Owner, Commented Apr 8, 2018 at 20:49

Pedro Lobito · Accepted Answer · 2018-04-08 21:18:51Z

I'm not entirely sure of what you're trying to do, but to parse a csv file and generate mysql queries with the csv values, you can use:

import csv
csv_path = "C:/Users/HP/PycharmProjects/book_crawler/books/items.csv"
with open(csv_path) as csvfile:
    readCSV = csv.reader(csvfile, delimiter=',')
    # skip the first line
    next(readCSV) 
    for row in readCSV:
        # skip blank lines
        if row: 
            # assign variables
            rating = row[0]; product_type = row[1]; upc = row[2]; title = row[3]
            # surround table and fields with  back-tick ` and values with single quote '
            print ("INSERT IGNORE INTO `books_table` (`rating`, `product_type`, `upc`, `title`) VALUES('{}', '{}', '{}', '{}')".format(rating, product_type, upc, title))

Output:

INSERT IGNORE INTO `books_table` (`rating`, `product_type`, `upc`, `title`) VALUES('Three', 'Books', 'a897fe39b1053632', 'A Light in the Attic')
INSERT IGNORE INTO `books_table` (`rating`, `product_type`, `upc`, `title`) VALUES('One', 'Books', '6957f44c3847a760', 'Soumission')

Collectives™ on Stack Overflow

String Formatting: Iterate the row values from a csv file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related