Integrating Python and SQL: A Guide for Beginners

• Hierarchical databases
• Network databases
• Relational databases
• Object-oriented databases
• Graph databases
• ER model databases
• Document databases
• NoSQL databases

This tutorial will focus on relational database management systems of which SQL is its standard language.

• Oracle
• Sybase
• Microsoft SQL Server
• Access
• Ingres, etc.

Structured Query Language (SQL)

What will you learn?

How to connect to Database using Python

How to interact with the database by performing all CRUD operations. CRUD stands for Create, Read, Update, and Delete operations.

Requirements

• SQLite
• PyMySQL
• python-sql
• myql-connector

In 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 a new database

We use the connect ( ) method to connect to an existing SQLite database and if there is no database in your project directory it will automatically create a new database.

Example of Usage

>>> import sqlite3
>>> sqlite3.connect('Customers.db')
<sqlite3.Connection object at 0x7f2ec8c90b90>

Once you execute the above code it will create a new database on your project directory with the name Customers.db as shown below.

.
├── app.py
└── Customers.db​
0 directories, 2 files

Now that you have a connection, we then supposed to 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 database​
Cursor = connection.cursor() #Creating a cursor obect 
SQL_stement = 'create table Customers(name varchar(20), age int);
#Sql statement to create a table with name and age column
Cursor.execute(SQL_stement) #excuting our sql statement 
connection.commit() #Saving changes to database

Once you run the above code it creates a new table on a database with name and age as columns, you can view your Database using

Inserting data to our Table

>>> 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()

Output

Reading data from our Database

>>> 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 a table in Database

>>> 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>

>>> 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 a new column to the existing table.

Let’s add the profession column to our customer's table

>>> import sqlite3
>>> database = sqlite3.connect('Customers.db')
>>> Cursor = database.cursor()
>>> Cursor.execute('ALTER TABLE Customers add column Profession;')
<sqlite3.Cursor object at 0x7fd055bd5d50>

Previously published here: //kalebujordan.com/beginners-guide-to-integrating-python-and-sql/