banner ad

Using SQLite3 in Python 2.x

| January 30, 2013 | 0 Comments
0 Flares 0 Flares ×

sqlite370_bannerSqlite3 database is a lightweight open source database software. Sqlite3 is a terminal based frontend to the SQLite library that can evaluate queries interactively and display the results in multiple formats. Many scripting languages use the API like Python,Perl,PHP. In python sqlite3 module was written by Gerhard Häring. It provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249.

1. create sqlite3 database and create table.
You can use sqlite3.connect() create database when the database is not exists.

import sqlite3
def sqlite_basic():
    # Connect to db
    conn = sqlite3.connect('test.db')
    # create cursor
    c = conn.cursor()
    # Create table
    c.execute('''
              create table if not exists stocks
              (date text, trans text, symbol text,
              qty real, price real)
              '''
             )
    # Insert a row of data
    c.execute('''
              insert into stocks
              values ('2006-01-05','BUY','REHT',100,35.14)
              '''
             )
    # query the table
    rows  = c.execute("select * from stocks")
    # print the table
    for row in rows:
      print(row)
    # delete the row
    c.execute("delete from stocks where symbol=='REHT'")
    # Save (commit) the changes
    conn.commit()
    # Close the connection
    conn.close()

2. insert table values and select value from table.

def sqlite_adv():
    conn = sqlite3.connect('test2.db')
    c = conn.cursor()
    c.execute('''
              create table if not exists employee
              (id text, name text, age inteage)
              ''')
    # insert many rows
    for t in [('1', 'itech', 10),
              ('2', 'jason', 10),
              ('3', 'jack', 30),
             ]:
        c.execute('insert into employee values (?,?,?)', t)
    # create index
    create_index = 'CREATE INDEX IF NOT EXISTS idx_id ON employee (id);'
    c.execute(create_index)
    # more secure
    t = ('jason',)
    c.execute('select * from employee where name=?', t)
    # fetch query result
    for row in c.fetchall():
      print(row)
    conn.commit()
    conn.close()
     

3.The most common way to force an SQLite database to exist purely in memory is to open the database using the special filename “:memory:”. In other words, instead of passing the name of a real disk file into one of the sqlite3_open(), sqlite3_open16(), or sqlite3_open_v2() functions, pass in the string “:memory:”. For example:
rc = sqlite3_open(“:memory:”, &db);
When this is done, no disk file is opened. Instead, a new database is created purely in memory. The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other. So, opening two database connections each with the filename “:memory:” will create two independent in-memory databases.

Download PDF
0 Flares Twitter 0 Facebook 0 Google+ 0 Pin It Share 0 LinkedIn 0 Reddit 0 StumbleUpon 0 0 Flares ×

Tags: ,

Category: Database

About the Author ()

My name is John Link.I am 26 years old. My major is Computer science and technology. I am a junior programmer with Python.

Leave a Reply

Your email address will not be published. Required fields are marked *

0 Flares Twitter 0 Facebook 0 Google+ 0 Pin It Share 0 LinkedIn 0 Reddit 0 StumbleUpon 0 0 Flares ×