## Introduction

Today we will talk about sqlite in python. I often use mysql as my data source because of a habit, but when I am in a hurry, I guess sqlite will be enough.

#### To keep in mind:

• I use python3
• Sqlite library is called sqlite3
• I use hashlib to hash the password (not required)

## Action

#!/usr/bin/python
# -*- coding: utf-8 -*-

import hashlib
import sqlite3

con = sqlite3.connect('test.db')


Those import statement is where we put all required libraries. The con variable will handle the connection from / into sqlite database which named “test.db”. Don’t mind the space between import and con, it’s just a habit to give 2 empty line between import statement and code below it.

with con:
con.row_factory = sqlite3.Row  # this one
cur = con.cursor()


Now, “this one” is what I prefer because we will get a dictionary when we select some data from sqlite. We can ignore it of course, but the result from our query will be tuple which I do not prefer.

print(data[0], data[1], ..., data[n])


People will not understand what we print unless we tell them.

print(data['id'], data['email'], data['username'], ...)


This one will be more understandable.

“with” statement will handle the resource. We can also handle the resource manually (close the connection) and use try-except to handle the exception.

This is a little code to show how we drop or create table and insert some data into sqlite.

cur.execute("DROP TABLE IF EXISTS users")
cur.execute("INSERT INTO users VALUES (?, ?, ?, ?, ?)", [1, "petruk@gmail.com", "petruk", hashlib.md5("passwordpetruk".encode()).hexdigest(), True])

cur.execute("SELECT * FROM users WHERE id=?", (1,))
datum = cur.fetchone()


Now, you can see why I use that row_factory.

### Some explanation

• We drop the table first if the table is exist
• We create new table which named users. It has several field which is id, email, username, password, and authenticated.
• We insert a new user into users table.
• We find a user with a specified id.

#### To keep in mind:

I use parameterized queries to insert data. Look for it, it will be helpful for your project. I also use hashlib to hash the password. In real case, you should use a better security like bcrypt or others.

The next code will show you how to do batch insert with sqlite. Instead of execute, we can use executemany to insert data.

wayang_actors = (
)
cur.executemany("INSERT INTO users VALUES (?, ?, ?, ?, ?)", wayang_actors)

cur.execute("SELECT * FROM users")
data = cur.fetchall()
for datum in data:


Now this one is the update query.

cur.execute("UPDATE USERS SET email=? WHERE id=?", ("goreng@gmail.com", 2))
print("Data updated: {}".format(cur.rowcount))

cur.execute("SELECT * FROM users")
data = cur.fetchall()
for datum in data:


The complete code is this one.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import hashlib
import sqlite3

con = sqlite3.connect('test.db')

with con:
con.row_factory = sqlite3.Row
cur = con.cursor()

print("# drop, create table and insert example")
cur.execute("DROP TABLE IF EXISTS users")
cur.execute("INSERT INTO users VALUES (?, ?, ?, ?, ?)", [1, "petruk@gmail.com", "petruk", hashlib.md5("passwordpetruk".encode()).hexdigest(), True])

cur.execute("SELECT * FROM users WHERE id=?", (1,))
datum = cur.fetchone()
print()

print("# batch insert example")
wayang_actors = (
)
cur.executemany("INSERT INTO users VALUES (?, ?, ?, ?, ?)", wayang_actors)

cur.execute("SELECT * FROM users")
data = cur.fetchall()
for datum in data: