Python with MySQL: Connect, Create Database, Table, Insert

An application without a database can’t remember information. Once you quit the application all the information will be lost. To store information that you either take from the user or the generated through complex computations database is used. In this tutorial, we will learn how to connect your Python code with the database to store the information. We will connect the MySQL database with our python code.

So you need to install MySql first.

For the Installation of MySql in Mac Os, the following command is used.

brew install mysql

For the installation of MySql in Linux operating System the following command is used.

sudo apt-get install mysql

The following is the output when MySql is installed in the Mac.

You also need to install a library that connects your Python with the MySql. The following command is used to install the library.

pip3 install mysql-connector

If everything goes right you must be able to run the following line in your IDE.

import mysql.connector

Connecting with MySQL

Let’s take a look at how to connect Python with MySql.

import mysql.connector
db_connection = mysql.connector.connect(
host="localhost",
user="root",
passwd=""
)
print(db_connection)

You need to pass three parameters 

• Host
• User
• passwd

The host is the IP address of the machine where your MySQL is running. In our case, we are using our localhost. User is the “root”. You can change the user or create a new user with a password.

When you run the above code the following will be the output.

Creating a new Database

We will be creating a wrapper around MySQL queries that execute the MySQL commands.

For example, the command to create a new database in MySQL is following.

CREATE DATABASE “database_name”

We will use the above command in Python. Let’s take a look at the code.

import mysql.connector
db_connection = mysql.connector.connect(
host= "localhost",
user= "root",
passwd= ""
)
# creating database_cursor to perform SQL operation
db_cursor = db_connection.cursor()
# executing cursor with execute method and pass SQL query
db_cursor.execute("CREATE DATABASE k2kinfosys")
# get list of all databases
db_cursor.execute("SHOW DATABASES")
#print all databases
for db in db_cursor:
  print(db)

The following will be the output.

As you can above that “k2kinfosys” database is created.

Now let’s add some data inside this database.

Creating a new Table.

Note: we will not run the “create database command” again as we have already created the database.

The command to create a new table in a database is following.

CREATE  TABLE articles (id num, aritcle_name VARCHAR(50))

The following code will be used to create a table.

import mysql.connector

db_connection = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="k2kinfosys"
)
db_cursor = db_connection.cursor()
# Here creating database table as student'
db_cursor.execute("CREATE TABLE articles(aritcleId INT, articleName VARCHAR(50))")
# Get database table'
db_cursor.execute("SHOW TABLES")
for table in db_cursor:
  print(table)

The following will be the output.

Insert data into the table

First, we created a database then created a table inside the database let’s add data into the table.

import mysql.connector

db_connection = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="k2kinfosys"
)
db_cursor = db_connection.cursor()
article_sql_query = "INSERT INTO articles(aritcleId,articleName) VALUES(01, 'Intro to Python')"
# Execute cursor and pass query as well as student data
db_cursor.execute(article_sql_query)

db_connection.commit()
print(db_cursor.rowcount, "Record Inserted")

The following will be the output.