Importing csv to mysql and an array value to first column of table

Question

I want to insert my array value result =['2018-10-18_trx_result-reuslt.csv'] to my column date_sourced, but my problem is how can I include this to my query for rows in my csv data.

Here is my code to import my csv to mysql table

for now, my date_sourced is 0000-00-00, so I want to replace it by my result variable

import csv
import mysql.connector
import re

result =['2018-10-18_trx_result-reuslt.csv']
mydb = mysql.connector.connect(user='root', password='',
                          host='localhost',
                          database='jeremy_db')
file = open('C:\\Users\\trendMICRO\\Desktop\\OJT\\test.csv', 'rb')  
csv_data = csv.reader(file)
cursor = mydb.cursor()

for row in csv_data:
    cursor.execute('INSERT INTO jeremy_table_test(date_sourced, sha1, vsdt,trendx,notes )' 'VALUES(%s, %s, %s, %s,%s)',row)
mydb.commit()
cursor.close()
print("Done")

dev.mysql.com/doc/refman/8.0/en/load-data.html

user13446755
– user13446755

2020-05-01 13:19:40 +00:00
Commented May 1, 2020 at 13:19 — user13446755
– user13446755, Commented May 1, 2020 at 13:19

danblack · Accepted Answer · 2018-11-14 05:12:44Z

1

Because allow_local_infile is True you can use LOAD DATA LOCAL INFILE'filename' INTO TABLE jeremy_table_test to easily load CSV files.

answered Nov 14, 2018 at 5:12

danblack

14.9k2 gold badges27 silver badges49 bronze badges

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

Comments

user13446755 · Accepted Answer · 2020-05-01 10:09:34Z

0

Read here: https://dev.mysql.com/doc/refman/8.0/en/load-data.html

You can do:

LOAD DATA LOCAL INFILE'filename' INTO TABLE jeremy_table_test

answered May 1, 2020 at 10:09

user13446755

Collectives™ on Stack Overflow

Importing csv to mysql and an array value to first column of table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related