Python: sqlite

Sumber: http://www.bogotobogo.com/python/python_sqlite_connect_create_drop_table.php

SQLite

"SQLite is a software library that implements a self-contained, serverless, zero-configuration,
transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world."
- http://www.sqlite.org/

SQLite Module in Python

Untuk menggunakan modul SQLite3 kita perlu menambahkan pernyataan impor ke skrip python kita:

import sqlite3

Kami dapat memeriksa versi sqlite:

sqlite3.version
'2.6.0'
sqlite3.sqlite_version
'3.7.17

sqlite.version adalah versi pysqlite (2.6.0), yang merupakan pengikatan bahasa Python ke database SQLite. sqlite3.sqlite_version memberi kita versi pustaka database SQLite. Dalam kasus kami ini adalah 3.7.17.

Connecting to the Database

Untuk terhubung ke database, kita dapat menggunakan fungsi sqlite3.connect dengan memberikan nama file untuk membuka atau membuatnya:

import sqlite3
db = sqlite3.connect('data/test.db')

Kita dapat menggunakan argumen ":memory:" untuk membuat DB sementara di RAM:

import sqlite3
dbm = db = sqlite3.connect(':memory:')

Ketika kita selesai bekerja dengan DB, kita perlu menutup koneksi:

db.close()
dbm.close()

Creating DB with SQLite command line - Outside of python shell

Another way of creating db is to use the sqlite3 command line tool:

$ ls
$ sqlite3 test.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
sqlite> .exit
$ ls
test.db

The .tables command gives a list of tables in the test.db database. We don't have any tables now. The .exit command terminates the interactive session of the sqlite3 command line tool.

CREATE TABLE

To use the database, we need to get a cursor object and pass the SQL statements to the cursor object to execute them. Then, we should commit the changes.

We are going to create a books table with title, author, price and year columns.

cursor = db.cursor()
cursor.execute(CREATE TABLE books(id INTEGER PRIMARY KEY, 
                  title TEXT, author TEXT, price TEXT, year TEXT)
               )
db.commit()

Note that the commit function is invoked on the db object, not the cursor object.

DROP TABLE

To drop a table:

cursor = db.cursor()
cursor.execute(DROP TABLE books)
db.commit()

INSERT - Inserting Data into the Database

To insert data we use the cursor to execute the query. In this example we are going to insert two books in the database, their information will stored in python variables.

db.close()
import sqlite3
db = sqlite3.connect('data/test.db')
cursor = db.cursor()
cursor.execute(CREATE TABLE books(id INTEGER PRIMARY KEY,
                  title TEXT, author TEXT, price TEXT, year TEXT)
               )
db.commit()

import sqlite3
db = sqlite3.connect('data/test.db')
cursor = db.cursor()
title1 = 'Learning Python'
author1 = 'Mark Lutz'
price1 = '$36.19'
year1 ='Jul 6, 2013'
 
title2 = 'Two Scoops of Django: Best Practices For Django 1.6'
author2 = 'Daniel Greenfeld'
price2 = '$34.68'
year2 = 'Feb 1, 2014' 

cursor.execute(INSERT INTO books(title, author, price, year)
                  VALUES(?,?,?,?), (title1, author1, price1, year1))

cursor.execute(INSERT INTO books(title, author, price, year)
                  VALUES(?,?,?,?), (title2, author2, price2, year2)) 

db.commit()

Note: If we need values from Python variables it is recommended to use the "?" placeholder. Never use string operations or concatenation to make your queries because is very insecure.

The values of the Python variables are passed inside a tuple.

If we have more books to insert, we can continue. But this time, we'll do it another way: passing a dictionary using the ":keyname" placeholder:

title3 = 'Python Cookbook'
author3 = 'David Beazley'
price3 = '$30.29'
year3 = 'May 29, 2013'

cursor.execute(INSERT INTO books(title, author, price, year)
                  VALUES(:title, :author, :price, :year),
                  {'title':title3, 'author':author3, 'price':price3, 'year':year3})
<sqlite3.Cursor object at 0x7f1d2717d650>
 
db.commit()

If we need to insert several users, we can use executemany and a list with the tuples:

title4 = 'The Quick Python Book'
author4 = 'Naomi R. Ceder'
price4 = '$16.39'
year4 = 'Jan 15, 2010'
 
title5 ='Python Testing'
author5 ='David Sale'
price5 = '$38.20'
year5 = 'Sep 2, 2014'

books = [(title4,author4, price4, year4),
         (title5,author5, price5, year5)]
cursor.executemany(INSERT INTO books(title, author, price, year) VALUES(?,?,?,?), books)
db.commit()

sqlite3 command line

Let's see what we've done so far using sqlite command shell:

$ sqlite3 test.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> .tables
books

sqlite> SELECT * FROM books;
1|Learning Python|Mark Lutz|$36.19|Jul 6, 2013
2|Two Scoops of Django: Best Practices For Django 1.6|Daniel Greenfeld|$34.68|Feb 1, 2014
3|Python Cookbook|David Beazley|$30.29|May 29, 2013
4|The Quick Python Book|Naomi R. Ceder|$16.39|Jan 15, 2010
5|Python Testing|David Sale|$38.20|Sep 2, 2014

sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM books;
id          title            author      price       year       
----------  ---------------  ----------  ----------  -----------
1           Learning Python  Mark Lutz   $36.19      Jul 6, 2013
2           Two Scoops of D  Daniel Gre  $34.68      Feb 1, 2014
3           Python Cookbook  David Beaz  $30.29      May 29, 201
4           The Quick Pytho  Naomi R. C  $16.39      Jan 15, 201
5           Python Testing   David Sale  $38.20      Sep 2, 2014
sqlite> 

Note that we modified the way the data is displayed in the console. We used the column mode and turned on the headers.

Referensi

• http://www.bogotobogo.com/python/python_sqlite_connect_create_drop_table.php