Load CSV Data into MySQL in Python

Load CSV data into MySQL in Python

I think you have to do mydb.commit() all the insert into.

Something like this

import csv
import MySQLdb

mydb = MySQLdb.connect(host='localhost',
    user='root',
    passwd='',
    db='mydb')
cursor = mydb.cursor()

csv_data = csv.reader(file('students.csv'))
for row in csv_data:

    cursor.execute('INSERT INTO testcsv(names, \
          classes, mark )' \
          'VALUES("%s", "%s", "%s")', 
          row)
#close the connection to the database.
mydb.commit()
cursor.close()
print "Done"

Inserting csv into MySQL database with python library mysql.connector

I'm guessing that seems the problem is that you passed one argument (row) instead of four. So try this:

  cursor.execute("""
            INSERT INTO flex(date, Store, Vendor, Shelf)
            VALUES(%s,%s,%s,%s)""",(row[0], row[1], row[2], row[3], ))

Using python to insert a large csv file into mysql table

missing commit after executing your commands

cursor.commit()

Import Data from .csv file into mysql using python

executemany takes a statement and a sequence of sets of parameters.

Try this:

with open('Sonneneinstrahlung.csv') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=';')
    sql = """INSERT INTO einstrahlung (Uhrzeit, Einstrahlungsdaten) VALUES (%s, %s)"""
    cursor.executemany(sql, csv_reader)
    mydb.commit()

How do I import CSV file into a MySQL table?

The core of your problem seems to be matching the columns in the CSV file to those in the table.

Many graphical mySQL clients have very nice import dialogs for this kind of thing.

My favourite for the job is Windows based HeidiSQL. It gives you a graphical interface to build the LOAD DATA command; you can re-use it programmatically later.

Screenshot: "Import textfile" dialog

To open the Import textfile" dialog, go to Tools > Import CSV file:

How to insert a CSV file data into MYSQL using Python efficiently?

There are many ways to optimise this insert. Here are some ideas:

1. You have a for loop over the entire dataset. You can do a commit() every 100 or so
2. You can insert many rows into one insert
3. you can combine the two and make a multi-row insert every 100 rows on your CSV
4. If python is not a requirement for you can do it directly using MySQL as it's explained here. (If you must do it using python, you can still prepare that statement in python and avoid looping through the file manually).

Examples:

for number 2 in the list, the code will have the following structure:

def csv_to_DB(xing_csv_input, db_opts):
    print("Inserting csv file {} to database {}".format(xing_csv_input, db_opts['host']))
    conn = pymysql.connect(**db_opts)
    cur = conn.cursor()
    try:
        with open(xing_csv_input, newline='') as csvfile:
            csv_data = csv.reader(csvfile, delimiter=',', quotechar='"')
            to_insert = []
            insert_str = "INSERT INTO table_x (ID, desc, desc_version, val, class) VALUES "
            template = '(%s, %s, %s, %s, %s)'
            count = 0
            for row in csv_data:
                count += 1
                to_insert.append(tuple(row))
                if count % 100 == 0:
                    query = insert_str + '\n'.join([template % r for r in to_insert])
                    cur.execute(query)
                    to_insert = []
                    conn.commit()
            query = insert_str + '\n'.join(template % to_insert)
            cur.execute(query)
            conn.commit()
    finally:
        conn.close()

---

Related Topics