Create a DB from CSV data
In this challenge, you will be asked to import CSV data into a sqlite database.
In the CSV file, you will find a person with a name, email, country of residence, and three phone numbers.
Create a users table and a phone numbers table. The relation is that a user has many phone numbers. Create the schema in SQL designer and push it to this folder on Github.
When you have done that, use Python's sqlite3 library to create the tables.
SQLite 3
Take a look here at Python's sqlite driver documentation
You need to establish a database and a cursor object.
import sqlite3
conn = sqlite3.connect('mydb.db')
c = conn.cursor()
To create a table:
c.execute("CREATE TABLE 'users' (
'id' INTEGER,
'name' VARCHAR,
'account' VARCHAR,
'balance' REAL,
PRIMARY KEY ('id')
)")
To insert a row of data:
c.execute("INSERT INTO users(name, account, balance) VALUES(?,?,?)", (name, account, balance))
**Note: ** You must specify the columns names otherwise sqlite3 will default to all columns.
Sandbox! dir() and help() the cursor for all the methods possible.
What does executemany do?
What are your options for fetch?
CSV
Take a look at the CSV library for Python's documentation
Sandbox some more with CSV.
Import the file using Python's csv library and figure out how to write this data into the appropriate table and column in the db.