25 Apr How to Connect Python with SQL Database

In this tutorial, we will learn how to connect Python with SQL Database. Python is a programming language that allows users to work quickly and integrate systems more effectively while MySQL is the most popular Open Source Relational SQL database management system used for developing web-based software applications.

We need to know that Python can be used in database applications and it is also very efficient. Let’s jump right in.

How to Get Started

We need to have MySQL installed on our computer before we dive in. You can download MySQL database at their Official WebsiteMySQL :: MySQL Downloads here.

We also need to create a connection between MySQL and Python, the mysql.connector module can be used to handle this.

To install mysql.connector, use the Pip module to install it by running the command below;

pip install mysql-connector-python

Connect to Database

Now, we need to provide our credentials and connect to the database by using the code below.

1# import the connector here
2import mysql.connector
3
4# initiate a connector object
5the_db = mysql.connector.connect(
6 host = "localhost",
7 user = "yourusername",
8 password = "yourpassword"
9)
10
11# Print the object
12print(the_db)

 

Create Database

Here, we will create our database and further create tables, the cursor instance will be parsed in with the CREATE command in creating our database.

1#import the connector module
2import mysql.connector
3
4#create a connection
5the_db = mysql.connector.connect(
6 host = "localhost",
7 user = "yourusername",
8 password = "your_password"
9)
10
11#we then create an instance of cursor class to use in executing our CREATE command
12cursor = the_db.cursor()
13
14#we then use cursor exceute with CREATE DATABASE command to create database CNDRO
15cursor.execute("CREATE DATABASE cndro")

 

Create Tables

We can create tables by specifying our columns and as well the data types pertaining to them before enclosing them with the Cursor Execute Command.

1#import the connector here
2import mysql.connector
3#we provide our connection object using our credentials which now include the database name 4
5mydb = mysql.connector.connect(
6 host = "localhost",
7 user = "yourusername",
8 password = "your_password",
9 database = "cndro"
10)
11#the cursor instance was created here
12cursor = mydb.cursor()
13
14#we pass the cursor to create the table named Students
15cursor.execute("CREATE TABLE students (Lastname VARCHAR(255), Firstname VARCHAR(255),
Address VARCHAR(255) )")

 

If you found this post helpful, let us know in the comments box.