For this project, I created a fake json file using a
data generator online, my goal here is to convert this json file into a SQLite database. I can then do some SQLite practice with this database.
In order to use Sqlite in Python, import the library 'sqlite3', then start a connection 'con = sqlite3.connect('employees.db'). This connect() call will create a new database with the filename specified, or connect to it if it's existed already.
The file extension can be .db or .sqlite, either one will work because an SQLite database file is created by starting with the following 16 bytes (in hex): 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00, not validated by the extension name.
If for some reason you prefer to create and store your database in memory only, you can use the special filename ":memory:". Once the database connection is closed, this in-memory database will vanish. You can open several in-memory database connections with ":memory:" filename, they are all independent and distinct from one another.
A temporary database can also be created by passing an empty string "" as the file name. It works almost the same as an in-memory database.
After a connection is started, we need to create a Cursor for opening the database: cur = con.cursor(). Then cur.execute() is called repeatedly for performing all types of SQL commands.
For creating or connecting a database, you don't need to run commit() command. However, if you need to save any changes you've made to the database, you must run commit() : con.commit(), otherwise you won't see the updates in your database. I use DB Browser for SQLite to visualize my database files.
After commit all changes to the database, you can call con.close() to close the connection.
'''Conver a json file into SQLite database
3 Steps: sqlits3.connect -> con.cursor -> cur.execute '''
import sqlite3
import json
# get a connection, create / connect to the db file
conn = sqlite3.connect('people.db')
# open the database
cur = conn.cursor()
# cur.execute('''DROP TABLE people''') # so that to create a new one from scratch
# use doc string for multiple line of command
cur.execute('''CREATE TABLE people (
name text,
phone text,
email text,
postal text,
country text
)''')
# open json file and read it
filehandler = open('data.json')
# decode json
people_list = json.load(filehandler)
# write each item in the list to the database
for p in people_list:
name = p['name']
phone = p['phone']
email = p['email']
postal = p['postalZip']
country = p['country']
cur.execute('INSERT INTO people VALUES (?,?,?,?,?)', (name, phone, email, postal, country))
# commit the changes
conn.commit()
conn.close()
