使用Python将CSV数据加载到MySQL中

Question

我不确定我漏掉了什么，但是这段代码没有任何错误消息，但表格中没有任何内容。我正在将一个带有三列值的CSV文件加载到MySQL表中。

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.
cursor.close()
print "Done"

希望有人能够看一下，感谢。

- Helen Neely

Answer 1

我认为你需要在所有的insert操作之后执行mydb.commit()。

像这样：

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"

- Jakob Bowyer

---

谢谢，它有效了 :) 但是你知道为什么数据库中的所有值都在单引号中吗？ - Helen Neely

尝试执行以下代码："insert into testcsv(names, classes, mark) values(%s, %s, %s)", row。其中，%s表示要插入的值会在代码执行时被替换为变量row中对应位置的值。该代码用于向名为testcsv的表中插入数据，包括names、classes和mark三个字段。 - Jakob Bowyer

谢谢，如果您有其他问题，请发布一个新的问题:D - Jakob Bowyer

@JakobBowyer 如何忽略 CSV 文件的第一行。 - Ashutosh SIngh

为了使其正常工作，您需要在加载任何CSV之前找出并创建具有正确模式的表。此外，如果您的数据包括数字列和/或数据中存在缺失（NULL）值，则此方法将会给您带来许多问题。这些问题可以通过下面的pandas/sqlalchemy方法自动处理。 - Luis Vazquez

Answer 2

如果你没有pandas和sqlalchemy库，请使用pip安装它们。

pip install pandas
pip install sqlalchemy

我们可以使用 pandas 和 sqlalchemy 直接插入到数据库中

import csv
import pandas as pd
from sqlalchemy import create_engine, types

engine = create_engine('mysql://root:*Enter password here*@localhost/*Enter Databse name here*') # enter your password and database names here

df = pd.read_csv("Excel_file_name.csv",sep=',',quotechar='\'',encoding='utf8') # Replace Excel_file_name with your excel sheet name
df.to_sql('Table_name',con=engine,index=False,if_exists='append') # Replace Table_name with your sql table name

- Harsha pps

---

对我来说，这会产生错误消息“没有名为MySQLdb的模块”。遵循https://dev59.com/QXRB5IYBdhLWcg3w9L3n#58246337，我使用了pymysql，并将create engine部分更改为“mysql+pymysql://...” - Sebastian

或者，您可以按照此答案 https://dev59.com/QXRB5IYBdhLWcg3w9L3n#5873259 安装 mysqlclient 来解决它。 - Kokokoko

Answer 3

上面的答案看起来不错。但另一种方法是在db连接中添加自动提交选项。这样可以自动提交数据库中执行的每个操作，避免每次都要使用 sql.commit()。

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

- Mahesh Kumaran

Answer 4

  from __future__ import print_function
import csv
import MySQLdb

print("Enter  File  To Be Export")
conn = MySQLdb.connect(host="localhost", port=3306, user="root", passwd="", db="database")
cursor = conn.cursor()
#sql = 'CREATE DATABASE test1'
sql ='''DROP TABLE IF EXISTS `test1`; CREATE TABLE test1 (policyID int, statecode varchar(255), county varchar(255))'''
cursor.execute(sql)

with open('C:/Users/Desktop/Code/python/sample.csv') as csvfile:
    reader = csv.DictReader(csvfile, delimiter = ',')
    for row in reader:
        print(row['policyID'], row['statecode'], row['county'])
        # insert
        conn = MySQLdb.connect(host="localhost", port=3306, user="root", passwd="", db="database")
        sql_statement = "INSERT INTO test1(policyID ,statecode,county) VALUES (%s,%s,%s)"
        cur = conn.cursor()
        cur.executemany(sql_statement,[(row['policyID'], row['statecode'], row['county'])])
        conn.escape_string(sql_statement)
        conn.commit()

- KeepLearning

Answer 5

如果有帮助的话，可以使用pymysql

import pymysql
import csv
db = pymysql.connect("localhost","root","12345678","data" )

cursor = db.cursor()
csv_data = csv.reader(open('test.csv'))
next(csv_data)
for row in csv_data:
    cursor.execute('INSERT INTO PM(col1,col2) VALUES(%s, %s)',row)

db.commit()
cursor.close()

- user6882757

---

谢谢你的建议，不过这个问题已经在8年前被问过了 :) - Helen Neely

@HelenNeely同意了，那么现在是时候选择我的答案作为正确答案了，因为open(file)不起作用 :P - user6882757

考虑到使用 Pandas 更简单的选项，这种方法相当原始。 - Murtaza Haji

@MurtazaHaji 不，这很简单，只要我们不想使用pandas或/和sqalchemy或sqlite。 - Geeocode

@HelenNeely 无论何时提出问题，SO都是为那些有与您8年前相同问题的人提供持续参考的平台。因此，随着现有答案变得不再相关，不断提供新答案是有意义的。 - brycejl

Answer 6

如果是pandas数据框，您可以这样做：

发送数据

csv_data.to_sql=(con=mydb, name='<the name of your table>',
  if_exists='replace', flavor='mysql')

为了避免使用 for。

- Rafael Valero

---

你好像是引用了一份手册？在这种情况下，请添加一个参考，以便人们可以在感兴趣的情况下继续阅读（并为原作者提供适当的参考）。 - Andreas Wolf

如果您的数据框架的索引名称与数据库表中的列名不同，则此方法将无法正常工作。 - Piyush S. Wanare

Answer 7

最快的方法是使用MySQL批量加载程序，通过“load data infile”语句。这比您在Python中想出的任何方法都要快得多。如果必须使用Python，则可以从Python本身调用语句“load data infile”。

- Jonas