Beginners guide to Integrating Python and SQL
Tags: python, sql, codenewbie, tutorial
Published: 2020-10-25 11:04:54

**SQL** is a structured query language that is used to communicate with a database where the 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.

This Tutorial will focus on relational database management systems of which **SQL** is its 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 communicates 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

Note :
To be able to completely follow-through this tutorial you need to be a 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 the database by performing all CRUD operations
**CRUD** stands for *Create*, *Read*, *Update*, and *Delete* operations.

### Requirements
There various library in Python to interact with database depending on the type of database you’re using, including

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

It receives the name of the database as a parameter, also you need to make sure that your database name has an extension of .db for instance Customers.db

#### Example of Usage

```python
>>> 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.

```bash
.
├── 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.

```python
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 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 [DB Browser](https://sqlitebrowser.org/)

![1.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1603622641891/wLgPygwus.png)

#### Inserting data to our Table

The procedures during inserting new data to the database we have just created It same as we have used to create the table. The only difference lies in the SQL statement just as shown below 

```python 
>>> 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 :

When you open again your database, your table values should be updated as shown in the picture below.

![2.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1603622709352/1ozIDJ8Qq.png)

#### Reading data from our Database

Now Let’s read our data from the database by iterating over all values contained within the Customer table.

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

Let’s try updating one of the Customer details in the table, for instance, Let’s change Jordan age to 27

```python
>>> 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 reading again the table, the age of Jordan have updated to 27

```python
>>> 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**

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

![3.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1603622738616/Vz2xdSKng.png)

Hope you find this post interesting, Now share it with your fellow developers 

In case of any suggestion or comment, drop it in the comment box and I will reply to you immediately.

The [Original Article](https://kalebujordan.com/beginners-guide-to-integrating-python-and-sql/) can be found on [kalebujordan.com](https://kalebujordan.com/ )