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
To use the SQLite3 module we need to add an import statement to our python script:
>>> import sqlite3
We can check sqlite version:
>>> sqlite3.version '2.6.0' >>> sqlite3.sqlite_version '3.7.17
The sqlite.version is the version of the pysqlite (2.6.0), which is the binding of the Python language to the SQLite database. The sqlite3.sqlite_version gives us the version of the SQLite database library. In our case it is 3.7.17.
Connecting to the Database
To connect to the database, we can use sqlite3.connect function by passing the name of a file to open or create it:
>>> import sqlite3
>>> db = sqlite3.connect('data/test.db')
We can use the argument ":memory:" to create a temporary DB in the RAM:
>>> import sqlite3
>>> dbm = db = sqlite3.connect(':memory:')
When we are done working with the DB we need to close the connection:
>>> 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.