python - 如何使用 python 将数据从 csv 文件写入 MySQL 数据库?
问题描述
我正在尝试使用 python 将数据从 csv 文件写入 MySQL 数据库。我使用查询在 MySQL 中创建了一个表:
CREATE TABLE books_table(rating VARCHAR(20), product_type VARCHAR(20), upc VARCHAR(20), title VARCHAR(20));
下面是写数据的python代码:
import os
import csv
import glob
import pymysql
csv_filepath = r"D:\Scraping projects\items.csv"
mydb = pymysql.connect(host='localhost', user='root', passwd='password', db='books_db')
cursor = mydb.cursor()
with open(csv_filepath, "r") as csv_file:
csv_data =csv.reader(csv_file)
row_count = 0
for row in csv_data:
print(row)
if row_count != 0:
try:
cursor.execute('INSERT IGNORE INTO books_table(rating, product_type, upc, title) VALUES(%s, %s, %s, %s)', row)
row_count += 1
except Exception:
pass
mydb.commit()
cursor.close()
代码在终端中没有显示任何错误,当我检查 MySQL 数据库时,它是空的。您还可以检查下面来自打印命令的 csv 文件的内容:
['title', 'rating', 'upc', 'product_type']
["It's Only the Himalayas", 'Two', 'a22124811bfa8350', 'Books']
['Olio', 'One', 'feb7cc7701ecf901', 'Books']
['Our Band Could Be Your Life: Scenes from the American Indie Underground, 1981-1991', 'Three', 'deda3e61b9514b83', 'Books']
["Scott Pilgrim's Precious Little Life (Scott Pilgrim #1)", 'Five', '3b1c02bac2a429e6', 'Books']
['Libertarianism for Beginners', 'Two', 'a18a4f574854aced', 'Books']
['Rip it Up and Start Again', 'Five', 'a34ba96d4081e6a4', 'Books']
['Set Me Free', 'Five', 'ce6396b0f23f6ecc', 'Books']
['Mesaerion: The Best Science Fiction Stories 1800-1849', 'One', 'e30f54cea9b38190', 'Books']
['Starving Hearts (Triangular Trade Trilogy, #1)', 'Two', '0312262ecafa5a40', 'Books']
['The Black Maria', 'One', '1dfe412b8ac00530', 'Books']
['The Boys in the Boat: Nine Americans and Their Epic Quest for Gold at the 1936 Berlin Olympics', 'Four', 'e10e1e165dc8be4a', 'Books']
['The Dirty Little Secrets of Getting Your Dream Job', 'Four', '2597b5a345f45e1b', 'Books']
['The Requiem Red', 'One', 'f77dbf2323deb740', 'Books']
['Sapiens: A Brief History of Humankind', 'Five', '4165285e1663650f', 'Books']
['Sharp Objects', 'Four', 'e00eb4fd7b871a48', 'Books']
['Soumission', 'One', '6957f44c3847a760', 'Books']
['The Coming Woman: A Novel Based on the Life of the Infamous Feminist, Victoria Woodhull', 'Three', 'e72a5dfc7e9267b2', 'Books']
['Tipping the Velvet', 'One', '90fa61229261140a', 'Books']
['A Light in the Attic', 'Three', 'a897fe39b1053632', 'Books']
["Shakespeare's Sonnets", 'Four', '30a7f60cd76ca58c', 'Books']
任何建议表示赞赏。
解决方案
@nano 值得称赞,他是第一个看到错误的人。您可以像这样修复代码:
import os
import csv
import glob
import pymysql
csv_filepath = r"D:\Scraping projects\items.csv"
mydb = pymysql.connect(host='localhost', user='root', passwd='password', db='books_db')
cursor = mydb.cursor()
with open(csv_filepath, "r") as csv_file:
csv_data =csv.reader(csv_file)
row_count = 0
for row in csv_data:
print(row)
if row_count != 0:
try:
cursor.execute('INSERT IGNORE INTO books_table(title, rating, upc, product_type) VALUES(%s, %s, %s, %s)', row)
except Exception:
pass
row_count += 1
mydb.commit()
cursor.close()
推荐阅读
- c# - 无法将浮点类型转换为 UnityEngine.Vector3
- python - 读取 csv 时忽略列中的新行
- android - 移动项目文件夹后未解决的参考错误
- ios - 如何在 iOS 中解码自定界作品
- java - 即使业务逻辑是纯CPU操作,是否需要在channelread方法中启动一个新线程来执行业务逻辑?
- python - uwsgi_param 和 proxy_set_header 的区别
- go - 仅通过 TCP 发送的第一条消息
- python - 如何根据 DataFrame 中先前行的值标记某个值?
- python - 如何在线托管数据库
- laravel - Vue多选未在编辑表单laravel上显示数据