How to insert rows of information into different rows in a mysql database?

Question

I don't know how to insert a list of info into a mysql database.

I'm trying to insert rows data into a database but it is simply inserting the last row three times. The list is named "t" and it is a tuple .

Data:

11/04/19,17:33,33.4,55
11/04/19,17:34,22.9,57
11/04/19,17:35,11.9,81

Code:

import mysql.connector

sql = mysql.connector.connect(
    host='    ',
    user='    ',
    password='    ',
    db="     "
    )
cursor = sql.cursor()

f = open("C:\Cumulus\data\Apr19log.txt","r")

while True:
    s = f.readline()
    list=[]
    if (s != ""):
        t=s.split(',')
        for item in t:
            list.append(item)
    else:
        break;


sqllist =    """INSERT INTO station_fenelon (variable, date,             
time,outside_temp, outside_humidity)  
                VALUES (%s, %s, %s, %s, %s)"""

record =[(1, t[0], t[1], t[2],t[3]),
         (2, t[0], t[1], t[2],t[3]),
         (3, t[0], t[1], t[2],t[3])]

cursor.executemany(sqllist, record)
sql.commit()

I want to create three rows in the database with this list of information but is is only showing the last row of information in the database.

@Sukumar Rdjf print (t) gives 11/04/19,17:33,33.4,55 11/04/19,17:34,22.9,57 11/04/19,17:35,11.9,81 — John
– John, Commented May 30, 2019 at 9:34
@Sukumar Rdjf 1, 2 and 3 are just auto increments in my database table so that I know where the info is going. — John
– John, Commented May 30, 2019 at 9:45
@Sukumar Rdjf instead of inserting the 3 rows of information it simply inserts 11/04/19,17:35,11.9,81 three times (I made a mistake it's not twice, it's three times) — John
– John, Commented May 30, 2019 at 9:49

Underoos · Accepted Answer · 2019-05-30 10:01:45Z

1

Try this.

import mysql.connector
sql = mysql.connector.connect(host='',user='',password='',db='')
cursor = sql.cursor()
f = open("C:\Cumulus\data\Apr19log.txt","r")
st=[i.strip().split(',') for i in f.readlines()]

sqllist = """INSERT INTO station_fenelon (variable, date, time, outside_temp, outside_humidity) VALUES (%s, %s, %s, %s, %s)"""

record = [(i+1, j[0], j[1], j[2], j[3]) for i, j in enumerate(st)]

cursor.executemany(sqllist, record)
sql.commit()

answered May 30, 2019 at 10:01

Underoos

5,2769 gold badges55 silver badges106 bronze badges

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

Collectives™ on Stack Overflow

How to insert rows of information into different rows in a mysql database?

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