0

I have a .csv file that I want to import into PyCharm and convert/export into a .db file. I've looked through numerous similar questions and solution, but just can't seem to quite understand. The CSV file can be downloaded here. The file contains Farmer Market information across the United States. I am using the file for a app building tutorial I am watching. Apprently using a .db is better that a .csv when working with sqlite3. Below is what I have based on @Marichyasana answer, though I run into a "table already exists error". I've also looked into @Tennessee Leeuwenburg answer, but do not understand what table_name and conn represent. I'd rather use @Tennessee Leeuwenburg answer due to the simplicity. I'd appreciate any help!

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
Binx
  • 382
  • 7
  • 22

2 Answers2

2

1) Table name will be the name of sql table you want to push data to, conn will be the database connection

2) Marichyasana answer uses Create table which if you run second time will not work because table is already created, you can replace "create table" query with "create table if not exists" check documentation here

Shubham Srivastava
  • 1,807
  • 1
  • 10
  • 17
  • So Table name I can choose anything I want and conn is my imported CSV file? – Binx Jun 12 '20 at 02:59
  • 1
    Table name is whatever you want, conn is not csv it is the sql connection con = sqlite3.connect("market_table.db") – Shubham Srivastava Jun 12 '20 at 03:11
  • To clarify, `conn` is creating a `.db`? The first argument in the `to_sql` function is just naming the table within the `.db`? So with what you have above my `.db` file name in Pycharm would be `market_table.db`. When I open that file, the table title (?) will be called what ever I put for `table_name`? – Binx Jun 12 '20 at 06:43
  • That is correct, try it and update if u face any problem – Shubham Srivastava Jun 12 '20 at 06:57
  • Will do! Thank you for the help and clarification! – Binx Jun 12 '20 at 20:23
0

You can use pandas library, this library provides a lot of helper to handle data.

You can simply read the csv using read_csv to get dataFrame and then dump it using to_sql

import pandas as pd
import sqlite3

df = pd.read_csv("C:/location/Export.csv")
conn = sqlite3.connect("markets.db")
df.to_sql("markets", conn, if_exists='append', index=False)
Ôrel
  • 7,044
  • 3
  • 27
  • 46
Binx
  • 382
  • 7
  • 22