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