Creating a SQLite database in python

SQLite is built into python, so it can easily be imported to any other library. Once you import it, you have to make a connection to it; so as to create the database file. Cursor in SQLite performs most of the functions; you will be doing with the database.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

import sqlite3 # SQLite v3 is the version currently included with python connection = sqlite3.connect("Hand_tools.database") # The.database extension is optional cursor = connection.cursor() #Alternative database created only in memory #mem_conn=sqlite3.connect(":memory:") #cursor=mem_conn.cursor() cursor.execute("""CREATE TABLE TOOLS(id INTEGER PRIMARY KEY,name TEXT,size TEXT,price INTEGER)""") for item in ((None,"Book","Small",15),(None,"Purse","Medium",35),(None,"Pen","Large",55),(None,"Hat","Small",25),(None,"Handbag","Small",25),(None,"Socks","Small",10),(None,"Comb","large",60,)): cursor.execute("INSERT INTO Tools VALUES(?,?,?,?)",item) connection.commit() #write data to database cursor.close() #Close database

Retrieving data from SQLite

to retrieve the data from a SQLite database, you just use the SQL commands that tells the database what information you want and how you want it formatted.

1 2 3 4 5 6 7 8 9 10

import sqlite3 connection = sqlite3.connect("Hand_tools.database") # The.database extension is optional cursor = connection.cursor() cursor.execute("SELECT name,size,price FROM Tools") tools = cursor.fetchall() for tuple in tools: name,size,price=tuple #unpack the tuples item=(name,size,price) print (item)

Output:

('Book', 'Small', 15)
('Purse', 'Medium', 35)
('Pen', 'Large', 55)
('Hat', 'Small', 25)
('Handbag', 'Small', 25)
('Socks', 'Small', 10)
('Comb', 'large', 60)