how to insert tab limited text file into sql database using python?

Question

with open('employee.txt') as textfile:
    csvreader = csv.reader(textfile, delimiter='\t')
    csvdata = []
    for row in csvreader:
        csvdata.append(row)

conn = pymysql.connect (host = "localhost",user = "root", passwd = "password",db = "details1")
c = conn.cursor()
for row in csvdata:
    c.execute("INSERT INTO employee6(serial_no, first_name, last_name, age, sex,city) VALUES ('%s', '%s', '%s', '%s', '%s','%s')" % (serial_no,first_name,last_name,age,sex,city))

conn.commit()
c.close()`enter code here`

but I am getting an error in () 8 c = conn.cursor() 9 for row in csvdata: ---> 10 c.execute("INSERT INTO employee6(serial_no, first_name, last_name, age, sex,city) VALUES ('%s', '%s', '%s', '%s', '%s','%s')" % (serial_no,first_name,last_name,age,sex,city)) 11 12 conn.commit() NameError: name 'serial_no' is not defined why am i getting this error

it seems that serial_no does not exist in your code. Did you define it? — Humbalan
– Humbalan, Commented Aug 9, 2018 at 11:19
I have serial_no in table i created and even in my text file — cherrycharitha
– cherrycharitha, Commented Aug 9, 2018 at 11:25
@cherry please show us at least one or two rows of the output of print(row) if possible. ANd that would the the rowabouve your c.execute() line. That's kind of relevant for the answer. — shmee
– shmee, Commented Aug 9, 2018 at 13:10

Upasana Mittal · Accepted Answer · 2018-08-09 13:34:47Z

1

It should be:

        c.execute("INSERT INTO employee6(serial_no, first_name, last_name, age, sex,city) VALUES ('%s', '%s', '%s', '%s', '%s','%s')" % (row["serial_no"], row["first_name"], row["last_name"], row["age"],row["sex"],row["city"]))

Edited

    with open('employee.txt') as textfile:
        csvreader = csv.reader(textfile, delimiter='\t')
        csvdata = []
        conn = pymysql.connect (host = "localhost",user = "root", passwd = "password",db = "details1")
        c = conn.cursor()
        for row in csvreader:
            c.execute("INSERT INTO employee6(serial_no, first_name, last_name, age, sex,city) VALUES ('%s', '%s', '%s', '%s', '%s','%s')" % (row["serial_no"], row["first_name"], row["last_name"], row["age"],row["sex"],row["city"]))


        conn.commit()
        c.close()

edited Aug 9, 2018 at 13:34

answered Aug 9, 2018 at 11:45

Upasana Mittal

2,6991 gold badge17 silver badges21 bronze badges

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

8 Comments

cherrycharitha Over a year ago

its throwing me an error saying" list indices must be integers or slices, not str"

shmee Over a year ago

No, sorry, but no, it should not be that :) You are proposing the use of string interpolation, which not only makes the code vulnerable to SQL injection attacks, but also will break if, for example, the serial_no is a numeric data type in the DB where the above would attempt to insert a string.

Upasana Mittal Over a year ago

Show output of print(rows) please @cherry

Upasana Mittal Over a year ago

Above code is to make sure that we are inputting correct value in DB @shmee

cherrycharitha Over a year ago

['Serial_no', 'FIRST_NAME', 'LAST_NAME', 'AGE', 'SEX', 'city', ''] ['1', '', 'john', '', 'mathew', '', '32', 'm', 'bombay'] ['2', '', 'jim', '', 'parker', '', '35', 'm', 'pune'] ['3', '', 'sophia', '', 'ran', '', '28', 'f', 'kolkata'] ['4', '', 'wendi', '', 'blake', '', '40', 'f', 'chennai'] @upasana

|

shmee · Accepted Answer · 2018-08-09 13:33:11Z

0

Assuming that reading the input file works correctly, you will have a list of list in your csvdata variable.

The way you try to create your SQL query string by using string interpolation is not recommended and potentially dangerous. It allows for SQL injection attacks, but also can make formatting the string to enforce the correct data types, especially for INSERT queries a pain.

All DB-API 2-compliant adaptors alow to pass a sequence of parameters along with your query string that the adaptor will safely replace.

Since row should already be a list when you iterate over csvdata, your execute call would look like this:

for row in csvdata:
    c.execute("INSERT INTO employee6(serial_no, first_name, last_name, age, sex,city) VALUES (%s, %s, %s, %s, %s,%s)", row)

However, since your csvdata is already a list of lists, you could also use executemany instead of the above:

c.executemany("INSERT INTO employee6(serial_no, first_name, last_name, age, sex,city) VALUES (%s, %s, %s, %s, %s,%s)", csvdata[1:])

Note: the csvdata[1:] is to exclude the header line from the INSERT.

answered Aug 9, 2018 at 13:33

shmee

5,1612 gold badges25 silver badges31 bronze badges

6 Comments

cherrycharitha Over a year ago

its showing "not all arguments converted during string formatting"@shmee

shmee Over a year ago

Is there any row in your input file where any of the fields is missing or where there is an additional field? Which option did you use? The execute() or executemany()? If the execute(), print row before the insert to identify the potentially offending row.

cherrycharitha Over a year ago

I dont have any fields missing and i used execute many and print(row) before for loop and insert statement

shmee Over a year ago

print(csvdata[:-1] what does that return?

cherrycharitha Over a year ago

[['Serial_no', 'FIRST_NAME', 'LAST_NAME', 'AGE', 'SEX', 'city', ''], ['1', '', 'john', '', 'mathew', '', '32', 'm', 'bombay'], ['2', '', 'jim', '', 'parker', '', '35', 'm', 'pune'], ['3', '', 'sophia', '', 'ran', '', '28', 'f', 'kolkata'], ['4', '', 'wendi', '', 'blake', '', '40', 'f', 'chennai']] and its repeating@shmee

|

Collectives™ on Stack Overflow

how to insert tab limited text file into sql database using python?

2 Answers 2

8 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related