Python sqlite

Connection	Create database and connection details
insert	Add records to table
update	Update records of table
delete	Delete records from table
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
Case	Grouping Records based on matching conditions

Creating Database

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

Creating table

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

table structure

r_set=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=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=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')

adding a single record

r_set=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=conn.execute('''select last_insert_rowid()''')
id=x.fetchone()
print(id[0])

Deleting records using condition

r_set=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=conn.execute('''select changes()''')
id=x.fetchone()
print(id[0])

Listing all tables in the database

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

Sqlite insert

Numpy Pandas Python & MySQL Python- Tutorials

Python & SQLite

Creating Database

Creating table

table structure

Adding records to student table

Displaying records from student table

adding a single record

getting id of last inserted row

Deleting records using condition

Listing all tables in the database