SQL is a simple yet powerful language that helps us manage and query data directly from a database, without having to copy it first.
Table of Contents
- What is a Database?
- What is SQL?
- Why Should you use SQLite?
- Connecting to an SQLite Database
- Creating tables using SQL
- Inserting values in a table using SQL
- Fetching records from a table using SQL
What is a Database?
A database is an organized collection of interrelated data stored in an electronic format.
There are various types of databases and their choice of usage varies from organization to organization, the most basic and widely used is the Relational Database model.
Database Management System (DBMS): It is a software that facilitates users and different applications to store, retrieve, and manipulate data in a database. Relational Database Management System or RDBMS is a DBMS for relational databases.
What is SQL?
But wait — we’ve been hearing the word ‘SQL’ since the beginner. What in the world is SQL?
SQL stands for Structured Query Language. It is a querying language designed for accessing and manipulating information from RDBMS.
Why Should you use SQLite?
SQLite is a relational database management system based on SQL.
It is designed for embedded devices that require fast and reliable data.
It is server-less, lightweight, and requires zero-configuration.
The Sqlite3 module facilitates the use of SQLite databases with Python.
Connecting to an SQLite Database
- Create connection.
2. connect() method is used that returns a Connection object.
#It accepts a path to the existing database. If no database exists, it will create a new database on the given path.
3. Cursor object is generated using the cursor() method.
#It allows you to execute queries against a database.
EXAMPLE:
Creating New Database
con=sqlite3.connect(‘demo_sales4.db’)
cur = con.cursor()
print(‘Database created.’)
Two important things to be done:
- Commit:
It save the operations that we performed on the database using the commit() method.
If we don’t commit our queries, then any changes we made to the database will not be saved automatically.
con.commit()
print(‘Changes saved.’)
2. Close:
Close() the connection to the database to prevent the SQLite database from getting locked.
con.close()
print(‘Connection closed.’)
Creating tables using SQL
“SQL keywords are case-insensitive so you can write the commands in UPPERCASE IF YOU WANT!”
A table in SQL is created using the CREATE TABLE command.
Sample Program 1:
import sqlite3
con=sqlite3.connect(‘sample_sales.db’)
cur=con.cursor()
cur.execute(‘’’CREATE TABLE Empl(EmpNo integer,EmpName text,Empcity text, salary real)’’’)
cur.execute(‘’’INSERT INTO Empl VALUES(1,’Tom’,’Bangalore’,20000)’’’)
con.commit()
cur=con.cursor()
cur.execute(‘’’SELECT * FROM Emp’’’)
record=cur.fetchall()
print(record)
Output: [(1, ‘Tom’, ‘Bangalore’, 20000.0)]
Sample Program 2:
# connect to existing database
con = sqlite3.connect(‘.\sql_db\Demo_table.db’)
cur = con.cursor()
# create table in database
cur.execute(‘’’CREATE TABLE CUSTOMER( User_ID INTEGER PRIMARY KEY NOT NULL, Product_ID INTEGER NOT NULL, Name TEXT NOT NULL, Gender TEXT NOT NULL, AGE INTEGER NOT NULL, CITY TEXT); ‘’’)
# commit and save changes to database
con.commit()
Inserting values in a SQL table
- execute()
- executescript()
- executemany()
A database table is of no use without values. So, we can use the INSERT INTO SQL command to add values to the table. The syntax for the command is as follows:
INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
But if we are adding values for all the columns in the table, we can just simplify things and get rid of the column names in the SQL statement:
INSERT INTO table_name
VALUES (value1, value2, value3, …);
execute():
cur.execute(‘’’Insert Into Customer (‘User_ID’,’Product_ID’,’Name’,’Gender’,’AGE’,’CITY’) Values (1006, 3, ‘Princess Diana’, ‘Female’, 28, ‘Amazons’);’’’)
What if we want to write multiple Insert commands in a single go? We could use the executescript() method instead:
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’);
‘’’)
executemany():
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)
Fetching Records from a SQL table
- fetchone()
- fetchall()
For fetching values from the database, we use the SELECT command and the attribute values we want to retrieve.
SELECT column1, column2, … FROM table_name;
To fetch values of all the attributes in the table, * character is used instead of column names.
SELECT * FROM table_name;
Sample Program
# Fetch all rows of query result
cur.execute(‘SELECT * FROM CUSTOMER;’).fetchone()
for row in cur.execute(‘SELECT Name FROM CUSTOMER;’):
print(row)
fetchall() method which returns all the records in a list format.
cur.execute(‘SELECT * FROM CUSTOMER;’).fetchall()
Thank you !!!
