时间戳

首页 > 解决方案 > 将 CSV 导入导出数据库

python - 将 CSV 导入导出数据库

问题描述

我有一个.csv文件要导入 PyCharm 并转换/导出为.db文件。我查看了许多类似的问题和解决方案，但似乎不太明白。CSV 文件可在此处下载。该文件包含美国各地的农贸市场信息。我正在将该文件用于我正在观看的应用程序构建教程。显然使用 a.db比 a使用.csv时更好sqlite3。以下是我基于@Marichyasana回答的内容，尽管我遇到了“表已存在错误”。我也查看了@Tennessee Leeuwenburg的答案，但不明白什么table_name和conn代表。由于简单，我宁愿使用@Tennessee Leeuwenburg 的答案。我会很感激任何帮助！

import sqlite3, csv

# con = sqlite3.connect(":memory:")
con = sqlite3.connect("market_table.db")
cur = con.cursor()
cur.execute("CREATE TABLE market_table(MarketName STR, Website STR, Facebook STR, Twitter STR, Youtube STR, OtherMedia STR, street STR, city STR, Country STR, State STR, zip STR, "
            "Season1Date STR, Season1Time STR, Season2Date STR, Season2Time STR, Season3Date STR, Season3Time STR, Season4Date STR, Season4Time STR, x STR, y STR, Location STR, "
            "Credit STR, WIC STR, WICcash STR, SFMNP STR, SNAP STR, Organic STR, Bakedgoods STR, Cheese STR, Crafts STR, Flowers STR, Eggs STR, Seafood STR, Herbs STR, Vegetables STR, "
            "Honey STR, Jams STR, Maple STR, Meat STR, Nursery STR, Nuts STR, Plants STR, Poultry STR, Prepared STR, Soap STR, Trees STR, Wine STR, Coffee STR, Beans STR, Fruits STR, "
            "Grains STR, Juices STR, Mushrooms STR, PetFood STR, Tofu STR, WildHarvested STR, updateTime STR, PRIMARY KEY(MarketName))")

with open('C:/Users/zlesl/PycharmProjects/AeroTract_App/Export.csv','r') as market_table:
    dr = csv.DictReader(market_table)
    to_db = [(i['MarketName'], i['Website'], i['Facebook'], i['Twitter'], i['Youtube'], i['OtherMedia'], i['street'],
    i['city'], i['Country'], i['State'], i['zip'], i['Season1Date'], i['Season1Time'], i['Season2Date'], i['Season2Time'],
    i['Season3Date'], i['Season3time'], i['Season4Time'], i['Season4Date'], i['x'], i['y'], i['Location'], i['Credit'],
    i['WIC'], i['WICcash'], i['SFMNP'], i['SNAP'], i['Organic'], i['Bakedgoods'], i['Cheese'], i['Crafts'], i['Flowers'],
    i['Eggs'], i['Seafood'], i['Herbs'], i['Vegetables'], i['Honey'], i['Jams'], i['Maple'], i['Meat'], i['Nursery'],
    i['Nuts'], i['Plants'], i['Poultry'], i['Prepared'], i['Soap'], i['Trees'], i['Wine'], i['Coffee'], i['Beans'],
    i['Fruits'], i['Grains'], i['Juices'], i['Mushrooms'], i['PetFood'], i['Tofu'], i['WildHarvested'], i['updateTime']) for i in dr]

cur.executemany("INSERT INTO market_table VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
                "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);", to_db)
con.commit()


    cur.execute("CREATE TABLE market_table(MarketName STR, Website STR, Facebook STR, Twitter STR, Youtube STR, OtherMedia STR, street STR, city STR, Country STR, State STR, zip STR, "
sqlite3.OperationalError: table market_table already exists

标签： pythonpandassqlitepycharm

解决方案

1) 表名是你要推送数据的sql表名，conn是数据库连接

2） Marichyasana 答案使用创建表，如果您第二次运行该表将不起作用，因为表已经创建，您可以将“创建表”查询替换为“如果不存在则创建表”检查文档here

推荐阅读