How to operate mysql workbench through python¶

Are you a Python developer looking to interact with a MySQL database using MySQL Workbench? If so, this blog post is for you! In this tutorial, we’ll walk you through how to operate MySQL Workbench through Python, step by step. We’ll cover topics such as setting up a connection to MySQL server, opening and reading SQL files into the database, and writing and executing SQL queries from Python. By the end of this blog post, you’ll have a better understanding of how to use Python to interact with MySQL Workbench

In [1]:

# Import the necessary libraries
import mysql.connector
import pandas as pd

Set up a connection to MySQL server running on the same machine as the Python code.¶

host: This parameter specifies the hostname or IP address of the MySQL server you want to connect to. In this case, it is set to “localhost”, which means that the MySQL server is running on the same machine as the Python code.

port: This parameter specifies the port number that the MySQL server is listening on. The default port for MySQL is 3306, which is what is being used here.

user: This parameter specifies the username that will be used to authenticate the connection to the MySQL server. In this case, it is set to “root”, which is a common default username for MySQL.

password: This parameter specifies the password that will be used to authenticate the connection to the MySQL server.

In [2]:

mydb = mysql.connector.connect(
  host="localhost",
  port="3306",
  user="root",
  password="******")

This line of code creates a cursor object that can be used to execute SQL statements on the MySQL database.

mydb is the database connection object that was previously established using the mysql.connector.connect() method. This object represents the connection to the MySQL server and can be used to interact with the database.

cursor() is a method of the database connection object that creates a new cursor object. The cursor is a temporary workspace that allows you to execute SQL statements and fetch results from the database. mycursor is a variable that stores the cursor object. You can use this cursor to execute SQL statements and interact with the database.

In [3]:

mycursor = mydb.cursor()

mycursor.execute(“CREATE DATABASE db_python”) executes an SQL statement to create a new database named “db_python”. This statement is sent to the MySQL server to be executed.

mycursor.execute(“USE db_python”) executes an SQL statement to select the “db_python” database as the active database for further operations. This statement tells the MySQL server to use the “db_python” database for any subsequent SQL statements that are executed through the mycursor object.

In [5]:

mycursor.execute("CREATE DATABASE db_python")
mycursor.execute("USE db_python")

Open and read sql files into the database¶

open an SQL file named “create-db-hr.sql” located at the specified file path, read and store its content in the variable “sql_script”. The ‘r’ before the path name is used to indicate that the path is a raw string and should not be treated as an escape sequence. Finally, the file is closed using the ‘close()’ method to release the resources used by the file object.

In [32]:

# Open and read the SQL file
file = open(r'C:\Users\d\Downloads\sql-course-materials (1)\SQL Course Materials\create-db-hr.sql')
sql_script = file.read()
file.close()

In [ ]:

# Execute the SQL script
mycursor.execute(sql_script)

In [ ]:

### set the created database as active database
mycursor.execute("USE sql_store")

Write and execute sql queries from python¶

In [7]:

### write sql queries
select_statement = """SELECT p.name, p.unit_price, s.name AS shipper_name
FROM products p
JOIN shippers s
ON p.product_id = s.shipper_id"""
mycursor.fetchall()

Out[7]:

[]

In [8]:

### read the query into python
df_sql = pd.read_sql(select_statement, mydb)
df_sql

C:\Users\d\anaconda3\lib\site-packages\pandas\io\sql.py:762: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy
  warnings.warn(

Out[8]:

	name	unit_price	shipper_name
0	Foam Dinner Plate	1.21	Hettinger LLC
1	Pork – Bacon,back Peameal	4.65	Schinner-Predovic
2	Lettuce – Romaine, Heart	3.35	Satterfield LLC
3	Brocolinni – Gaylan, Chinese	4.53	Mraz, Renner and Nolan
4	Sauce – Ranch Dressing	1.63	Waters, Mayert and Prohaska