An introductory guide to using SQL in a Python Script to make Databases in MySQL

An introductory guide to using SQL in a Python Script to make Databases in MySQL

Andrew Dass's photo
Andrew Dass
·

5 min read

Table of contents

Overview

This is a guide explaining what SQL is, why it is beneficial to learn SQL, and how to learn SQL commands in Python to make and delete MySQL databases. The following technologies are used in this guide:

Materials

  1. Computer

  2. Visual Studio Code (Any IDE can be used and in this tutorial, Visual Studio Code is used to show a Python script)

  3. Python

  4. MySQL Workbench

    • Download MySQL Community Server: https://dev.mysql.com/downloads/mysql/

    • MySQL Community Server is the free version of MySQL. Other versions of MySQL come with more features but are not free to immediately use

  • When downloading Visual Studio Code, Python and MySQL, make sure to choose the right files to install that match your operating system and follow the steps to install them.

What is SQL?

SQL stands for Structured Query Language and is primarily used for databases. A database is responsible for withholding all new data that is saved recently within an application.

SQL Basic Commands

In SQL, there are commands to make a database and within each database, tables can be created too. Having many tables in a database is good to organize different information in each table. A table is specified to have how many rows and columns as well.

Below are commands used in SQL to make, show and delete databases (1):

  • Make a new database: CREATE DATABASE "databasename";

  • Show existing databases: SHOW DATABASES;

  • Delete a database: DROP DATABASE "databasename";

    • To make or delete more than one database, write the command and include commas to separate more than one entry

    • Create more than one database: CREATE DATABASE database1, database2;

    • Every command ends with a ";"

Different Types of SQL Databases

Many types of databases use the SQL language. Each database is similar in that they operate by using SQL with a few minor syntax differences. Below are a few databases that are commonly used:

  • PostgreSQL

  • Microsoft SQL Server

  • Oracle

  • MariaDB

  • MongoDB

  • Redis

  • Amazon Aurora

  • Amazon DynamoDB

As stated before, this guide will use SQL to operate a MySQL database

How to use SQL commands to operate MySQL

As stated, SQL commands can be run to make and change databases. After downloading MySQL Workbench, a similar window shown below should appear:

If there are no MySQL Connections, press the circular + button next to the "MySQL Connections" to make a connection

After clicking the + button, a menu shows up to make a database:

  • Connection name - Shows up as the name of the database

  • Username and Password are important to remember to login into the database. The username can be changed as shown above, it was changed to "test_database". To make the password click on "Store in Vault" and enter one

After making a username and password, click on "Test Connection" to see if the database can be entered into. A successful connection notification is shown down below:

After making a successful connection to the database, the database can now be entered and modified. A similar screen should show after entering the database on MySQL workbench:

On the left-hand menu, under "SCHEMAS" shows some default databases. By running SQL commands in a Python script, more databases can be created. Before writing a python script, install the "mysql-connector-python" package by performing pip install mysql-connector-python in the Command Prompt, PowerShell or the terminal. After downloading the package, import the module in the python script (2):

Make a database connection (2):

Then print the database connection (2):

Python script:

A successful connection is shown below:

Object at "...." is referring to the successful connection at MySQL Workbench. The long string of numbers and letters changes every time after starting a new connection.

Make a database:

.cursor() is a command that lets you make, update, or delete databases, tables or any information or properties that are related to databases.

Updated Python script:

After running the script, a result like this will occur:

Even though the database has been created, it will say "Nothing executed yet". In SQL Workbench, the database will not show it created unless the refresh button across from "SCHEMAS" is pressed

Updated Python script:

After making the database, within the line of code "mysql.connector.connect", the "database" variable can now be declared and running this now connect to the recently made database. Line 18 became a comment since running the "CREATE DATABASE" command again is redundant since the database was created. An error will not occur if the name is changed since a new database with the new name will be created.

After the database was created, the "SHOW DATABASES" command and by using a for loop, the information in .cursor() can be accessed to show the existing databases:

dbcursor is the variable that contains the .cursor() command, and the cursor is responsible for making or editing information. Printing the .cursor() command will display the latest information.

Updated Python script:

Showing existing databases:

Once done with the database, it can be deleted with the drop command:

Updated Python Script:

After refreshing the page the database will be gone:

To learn more about how to use other SQL commands and to use them to operate a MySQL database, refer to the guides created by w3schools shown down below.

Sources

https://www.w3schools.com/sql/ (1)

https://www.w3schools.com/python/python_mysql_getstarted.asp (2)

SQLMySQLDatabasesPython