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.