Overview

This article explains how to write SQL commands in the Python Programming Language to create databases and edit information in databases.

Materials

MySQL (1)
Python (2)

Procedure

During the installation of MySQL, it asks the user to make a database. Remember the username and password of the created database, because both are needed to enter the database. In this demonstration, the username of the database is called root.

To run MySQL commands and to make a MySQL database in a Python script, install the module “mysql-connector-python” (3) first.

pip install mysql-connector-python

Steps Explaining How to Make the Python Script

To run the MySQL module, write the following below:

import mysql.connector

Next, declare a variable that uses the “mysql.connector.connect()” and have a database currently running in MySQL. The parameters “mysql.connector.connect()” uses are user, password and host. When a MySQL database is created, a name for the user and password must be configured to access the database each time afterward. Every database runs on a port, and by default it runs on 127.0.0.1:3306. A variable usually named cursor is declared to eventually send and execute commands to the connected database and store, delete and display information from the database.

cnx = mysql.connector.connect(user='...', host='127.0.0.1', password='...')
cursor = cnx.cursor()

After declaring the cursor, variables in Python can be declared to execute a command that uses SQL syntax. Below shows the “make_database” variable, will make a database by using the SQL command “create database”.

make_database = "create database name_of_database"

Once again, the cursor variable is needed to execute the SQL command or variable that contains the SQL command by performing “cursor.execute(…)”.

cursor.execute(make_database)

Afterwards, run the command “show databases” and a for loop to retrieve and display information from the cursor, and it will display the changes.

list_of_databases = cursor.execute("show databases")

for i in cursor:
    print(i)

Final Python Script Integrating MySQL

# 1st import the module
import mysql.connector

# 2nd: connect to database, both lines of code down below is essential
cnx = mysql.connector.connect(user='...', host='127.0.0.1', password='...')

cursor = cnx.cursor()

# 3rd: write sql code: 
make_database = "create database name_of_database"

# 4th: execute
cursor.execute(make_database)

# 5th: show changes or databases
list_of_databases = cursor.execute("show databases")

for i in cursor:
    print(i)