sqlite

sqlite is a relational database management system based on SQL. It is serverless, lightweight, and requires zero-configuration. It reads and writes directly to a disk file that can be easily copied and is platform-independent.

sqlite3

import sqlite3 as sl
con = sl.connect('my-test.db')
#con = sl.connect(':memory:') # to create database in memory without local file ### create table Any SQL command can be executed using the execute() method of the Cursor object. 

with con:
#cur = con.cursor() 
#or do following operation with cursor
con.execute("""
    CREATE TABLE USER (
        id INTEGER NOT NULL PRIMARYKEY AUTOINCREMENT,
        name TEXT,
        age INTEGER
    );
""")
#insert table
sql = 'INSERT INTO USER (id, name, age) values(?,?, ?)'
data = [
    (1, 'Alice', 21),
    (2, 'Bob', 22),
    (3, 'Chris', 23)
]
with con:
    con.executemany(sql, data)

insert value

insert value can be done with methods: execute(), executescript(), executemany()

cur.execute('''Insert Into Customer ('User_ID','Product_ID','Name','Gender','AGE','CITY') Values (1006, 3, 'Princess Diana', 'Female', 28, 'Amazons');''')

# Execute multiple commands at once
cur.executescript('''Insert Into CUSTOMER Values
                (1005, 3, 'Clark Kent', 'Male', 36, 'Metropolis');
                
                Insert Into CUSTOMER Values
                (1003, 4, 'Bruce Wayne', 'Male', 39, 'Gotham City');
                
                ''')

# Insert maultiple values into table at once
customers = [(1004, 2, 'John Wick', 'Male', 32, 'New York'),
        (1001, 1, 'Tony Stark', 'Male', 35, 'New York'),
        (1002, 3, 'Gordon Ramsey', 'Male', 38, 'London')
            ]
cur.executemany('Insert Into CUSTOMER Values (?,?,?,?,?,?)', customers)

query data

with con:
    data = con.execute("SELECT *FROM USER WHERE age <= 22")
    for row in data:
        print(row)

Fetching Records

fetch records from datebase can be done with methods: fetchone() or fetchall()

#fetch single-row record
cur.execute('SELECT * FROM CUSTOMER;').fetchone()
#fetch multi-row record
# iterate over the rows 
for row in cur.execute('SELECT Name FROM CUSTOMER;'):
    print(row)
#fetch multi records
cur.execute('SELECT * FROM CUSTOMER;').fetchall()

commit

commit makes the changes to be saved in database

con.commit()

close

after change is done the connection should be closed

cur.close()
con.close()

using pandas to operate sql

df to sql

#insert to table
df_skill = pd.DataFrame({
    'user_id': [1,1,2,2,3,3,3],
    'skill': ['Network Security','Algorithm Development', 'Network Security', 'Java', 'Python', 'Data Science','Machine Learning']
})
df_skill.to_sql('SKILL', con)
#read table
df = pd.read_sql('''
    SELECT s.user_id, u.name, u.age, s.skill
    FROM USER u LEFT JOIN SKILL s ON u.id= s.user_id
''', con)
#write to new talbe
df.to_sql('USER_SKILL', con)

sql to df

df = pd.read_sql_query("select * from meal;", conn)
df.head()

query sql with pandas

# WHERE clause to filter the records based on a condition
df = pd.read_sql_query('''Select * from meal 
                    Where cuisine='Indian'; ''', conn)
# LIMIT clause to filter the records based on a condition
df = pd.read_sql_query('''Select * from meal 
                        Where category='Beverages' 
                        Limit 5;''', conn)
# GROUPBY statement separates rows into different groups based on an attribute
df = pd.read_sql_query('''Select Count(id) from train
                        Group by emailer_for_promotion;''',conn)

# ORDER BY statement is used to sort the result into ascending or descending order using the keywords ASC or DESC respectively. 
df = pd.read_sql_query('''Select count(id) as Email from train 
                    Group by emailer_for_promotion 
                    Order by Count(id);''',conn)
# HAVING clause is used to query on the results of another query run on the database
df = pd.read_sql_query('''Select Count(center_type), center_type
                        From centers
                        Group By center_type
                        Having Count(center_type) > 15; ''', conn)   
# JOIN clause retrieves and combines data from multiple tables on the same query based on a common attribute
df = pd.read_sql_query('''Select Sum(train.num_orders) as "Total_Orders", centers.center_type from train 
                    Inner Join centers 
                    On train.center_id = centers.center_id 
                    Group By centers.center_type;''', conn)     

# UPDATE statement is used to modify existing records in a table
conn.execute('''Update train
                Set base_price = base_price - 10
                Where emailer_for_promotion = 1; ''')

# delete statement 
conn.execute('''Delete from centers
                Where center_id = 11; ''')

# drop table from database
conn.execute('''Drop table train;''')