Python & SQLite

SQLite is a file based database with full functional capacity like any other Relational Database. Portability is the main advantage of SQLite database. Using sqlite3 library we can manage SQLite database by using Standard Query languages.
ConnectionCreate database and connection details
insert Add records to table
update Update records of table
delete Delete records from table
distinct Unique data from columns
select SELECT records from table
limit Limiting records returned from table
Order By Limiting records returned from table
Like Matching wildcards to get records from table
Between Records matching a range using between
CaseGrouping Records based on matching conditions
checkAdd, update Constraint CHECK for data validation
check(date)Add, update Constraint CHECK for date validation

Managing Date

For date queries use the fresh copy of SQL dump of date table to apply queries using present date as reference.
Date queryFilter records based on different date values

Creating Database

import sqlite3
my_conn = sqlite3.connect('my_db')
print("Created database successfully");

Creating table

my_conn.execute('''
CREATE TABLE IF NOT EXISTS student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      sex text 
                      );''')
my_conn.commit()
print("Student Table created successfully");

table structure

r_set=my_conn.execute('''PRAGMA table_info(student);''')
for row in r_set:
    print(row)
Output
(0, 'id', 'integer', 0, None, 1)
(1, 'name', 'text', 0, None, 0)
(2, 'class', 'text', 0, None, 0)
(3, 'mark', 'integer', 0, None, 0)
(4, 'sex', 'text', 0, None, 0)

Adding records to student table

r_set=my_conn.execute('''INSERT INTO `student` (`id`, `name`, `class`, `mark`, `sex`) VALUES
(1, 'John Deo', 'Four', 75, 'female'),
(2, 'Max Ruin', 'Three', 85, 'male'),
(3, 'Arnold', 'Three', 55, 'male'),
-----------
-----------
(4, 'Krish Star', 'Four', 60, 'female'),
(34, 'Gain Toe', 'Seven', 69, 'male'),
(35, 'Rows Noump', 'Six', 88, 'female');''')

Displaying records from student table

r_set=my_conn.execute('''SELECT * from student''');
for row in r_set:
    print(row)
Output
(1, 'John Deo', 'Four', 75, 'female')
(2, 'Max Ruin', 'Three', 85, 'male')
(3, 'Arnold', 'Three', 55, 'male')
---------------
---------------
(34, 'Gain Toe', 'Seven', 69, 'male')
(35, 'Rows Noump', 'Six', 88, 'female')

Total number of records in a table

Used count query without any WHERE condition to get total number of records.
r_set=my_conn.execute("SELECT count(*) as no from STUDENT")
data_row=r_set.fetchone()
nume=data_row[0] # 35

adding a single record

r_set=my_conn.execute('''INSERT INTO `student` 
( `name`, `class`, `mark`, `sex`) VALUES
('New _name', 'Four', 45, 'female')''')

getting id of last inserted row

We can use last_insert_rowid()
x=my_conn.execute('''select last_insert_rowid()''')
id=x.fetchone()
print(id[0])

Deleting records using condition

r_set=my_conn.execute('''DELETE FROM STUDENT WHERE class='Four' ''')
Getting number of rows affected or deleted or updated by recent query. We can use changes()
x=my_conn.execute('''select changes()''')
id=x.fetchone()
print(id[0])

Listing of all tables in the database

r_set=my_conn.execute('''select name from sqlite_master where type = 'table' ''')
for row in r_set:
    print(row)

Listing of all tables with number of records in the database

r_set=my_conn.execute('''select name from sqlite_master 
		where type = 'table' ''')
for row in r_set:
    r_set=my_conn.execute("SELECT count(*) as no from "+ row[0])
    data_row=r_set.fetchone()
    print(row[0],"(",data_row[0],")")
Output
student ( 35 )
category ( 4 )
subcategory ( 16 )
insert


plus2net.com



Post your comments , suggestion , error , requirements etc here




We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer