SQL is a structured query language that is used to communicate with database where database is the organized collection of data that is usually stored electronically on your computer.
There various types of database management system including
- Hierarchical databases.
- Network databases.
- Relational databases.
- Object-oriented databases.
- Graph databases.
- ER model databases.
- Document databases.
- NoSQL databases.
On this Tutorial will focus on relational database management systems of which SQL is it’s standard language .
Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.
Structured query language(Sql)
SQL communicate with the database using SQL statements , which are used to perform tasks such as update data on a database, or retrieve data from a database
To be able to complete follow through this tutorial you need to be little familiar with SQL statement but even If you’re not you can still grasp something .
What will you learn ?
- How to connect to Database using Python
- How to interact with database by performing all CRUD operations
CRUD stands for Create , Read , Update and Delete operations .
There various library in Python to interact with with database depending on type of database you’re using , including
On this tutorial we are going to use SQLite which comes by default with Python standard library therefore you don’t need to install anything
Let’s get started
Creating new Database
We use connect ( ) method to connect to existing SQLite database and if there is no database in your project directory it will automatically creates new database .
It receives a name of database as a parameter , also you need to make sure that the your database name has extension of .db for instance Customers.db
>>> import sqlite3 >>> sqlite3.connect('Customers.db') <sqlite3.Connection object at 0x7f2ec8c90b90>
Once you execute the Above code it will create new database on you project directory with name Customers.db as shown below.
. ├── app.py └── Customers.db 0 directories, 2 files
Once you have a Connection, to the database you can create a Cursor object and call its execute method to perform SQL commands:
Creating a new Table
On creating new tables we have to use SQL Statements , don’t worry If you never learned before about them they are kinda intuitive.
import sqlite3 connection = sqlite3.connect('Customers.db') #connecting to our datbase Cursor = connection.cursor() #Creating a cursor obect SQL_stement = 'create table Customers(name varchar(20), age int);' #Sql statement to create table with name and age column Cursor.execute(SQL_stement) #excuting our sql statement connection.commit() #Saving changes to database
Running our code
Once you run the above code it create a new table on a database with name and age as column. you can view your Database using DB Browser
Inserting data to our Table
The procedures during inserting new data to database we have just created It same as we have used to create the table . The only difference lies on SQL statement
>>> import sqlite3 >>> database = sqlite3.connect('Customers.db') >>> Cursor = database.cursor() >>> Cursor.execute('insert into Customers values("Jordan", 20);') >>> Cursor.execute('insert into Customers values("Frederick", 26);') >>> Cursor.execute('insert into Customers values("Stephen", 56);') >>> database.commit()
When you open again your database , you’re table values should be updated as shown in the picture below .
Reading data from our Database
Now Let’s read our data from the database by iterating over all values contained within Customers table .
>>> import sqlite3 >>> database = sqlite3.connect('Customers.db') >>> Cursor = database.cursor() >>> for row in Cursor.execute('select * from Customers'): ... print(row) ... ('Jordan', 20) ('Frederick', 26) ('Stephen', 56)
Updating data on table in Database
Let’s try updating one of Customer details in database , for instance Let’s change Jordan age to 27
>>> import sqlite3 >>> database = sqlite3.connect('Customers.db') >>> Cursor = database.cursor() >>> Cursor.execute('update Customers set age = 27 where name = "Jordan";') <sqlite3.Cursor object at 0x7fd055bd5d50>
Now when when read again all the elements in a database ,the age of Jordan have updated to 27
>>> for row in Cursor.execute('select * from Customers'): ... print(row) ... ('Jordan', 27) ('Frederick', 26) ('Stephen', 56)
Altering our Database
Altering the database including changing its structure for instance , we can try adding new column to the existing table .
Let’s add profession column to our Customers table
>>> import sqlite3 >>> database = sqlite3.connect('Customers.db') >>> Cursor = database.cursor() >>> Cursor.execute('ALTER TABLE Customers add column Profession;') <sqlite3.Cursor object at 0x7fd055bd5d50>
Now when we view again our database it will look as shown below
Hope you find this post interesting , don’t forget to subscribe to get more tutorials like this.
In case of any suggestion or comment , drop it on the comment box and I will reply to you immediately.