如何从CSV中逐行读取数据并存储在数据库中

Question

我有一个csv文件，其中有20条记录需要存储在数据库中的表中。我试图逐行读取每一行，但不确定如何将其传递给一个函数，将检查与数据库的连接，并存储在其中。我已经创建了一个单独的配置文件对于数据库的连接对象。

我应该如何逐行读取csv和传递每一行数据函数到然后存储它并对csv的每一行执行相同的操作。我的代码如下:

# This variable stores the insert query to store data in database
query = """INSERT INTO product(product_id, product_name, description, product_value) 
values(%s, %s, %s, %s)"""
def create_product():
data = pd.read_csv('path/to/csv')
df = pd.DataFrame(data)
data_list = []
# How to Write This part?
# How will I pass an entire row in the function call and what to have in the argument like a 
# List or something
for row in df.iterrows():
# print(row)
input_data = ",".join(row)
insert_data = output_data_to_DB(query, input_data, connect_db) # Calling Function
data_list.append(insert_data)
print(data_list)
# Called Function Here
def output_data_to_DB(insert_query, output_list, conn):
try:
cur = conn.cursor()
cur.execute(insert_query, output_list)
print("row inserted with valueList : ", output_list)
output_list.commit()
cur.close()
return ""
except Exception as e:
connect_db.rollback()
cur.close

我将感激任何形式的帮助。我对python程序不太熟悉。

Answer 1

示例:pandas

裁判:https://www.listendata.com/2019/06/pandas-read-csv.html

import pandas as pd
# read csv
data = pd.read_csv("your-file.csv")
# read csv and skip the header
data = pd.read_csv("your-file.csv", header = 1)
# read csv, define col names
data = pd.read_csv("your-file.csv", skiprows=1, names=['product_id', 'product_name'])
for row in data.iterrows():
print(row)
# process row value as you want
res = output_data_to_DB(query, res, connect_db)

示例:python CSV模块(<-我推荐这样做)

csv库将足够并且更简单地将每一行数据传递给函数。

def create_product():
data_list = []
with open('your-file.csv', newline='') as csvfile:
reader = csv.reader(csvfile)
next(reader)  # discard header
for row in reader:
print(row)  # e.g. `['foo', 'bar']`
insert_data = output_data_to_DB(query, row, connect_db)
data_list.append(insert_data)
print(data_list)

,

编辑

主键(自增列)

为id等列添加自动递增值的一些选项可以:

• BULK INSERT,id列为空
  • BULK INSERT with identity (auto-increment) column
• AUTOINCREMENT关键字
  • https://www.sqlite.org/autoinc.html
  • 如何插入新的行与AUTO_INCREMENT列数据库没有指定列名?
• 手动添加值到csv文件或从中选择行(<-我不建议这样做)