When working on my capstone project a couple of months back, I had to train machine learning models on data stored in a MySQL database.
I tried many methods to establish a database connection in Python, and discovered that the easiest way to do this was with the Python MySQL library.
In this article, I will take you through the following topics:
- Install and import the MySQL-connector library
- Establish a MySQL connection with Python
- Access database tables with Python
- Convert tables into Pandas dataframes
- Insert values into tables with Python
Installations and Imports
To establish a MySQL database connection with Python, you first need to install the mysql-connector library. You can do this easily with the following command:
pip install mysql-connector-python
Now, import the library to make sure it works:
Establish the connection
Great! Now, you can establish a connection with your MySQL database. Run the following lines of code:
mydb = mysql.connector.connect ( host="your_hostname", user="your_username", password="your_password", database = "your_db_name" ) mycursor = mydb.cursor()
If these lines of code run with no errors, congratulations! You have successfully established a MySQL connection with Python.
Access database tables
To access a table in the database with Python, run this line of code:
mycursor.execute('SELECT * FROM table_name')
This will select all the rows and columns of the table you want to load. To convert this table into a Pandas dataframe, run these two lines of code:
table_rows = mycursor.fetchall() df = pd.DataFrame(table_rows)
You can load multiple tables this way, turn them into dataframes, pre-process them, and build machine learning models.
Inserting values into SQL tables
When working on my capstone project, I wanted to capture live incoming traffic and add it to the database.
You can easily add rows to specific database columns using Python.
Above is an example of a MySQL table. If I want to add a new row to the table above with Python, I can do something like this:
sql = "INSERT INTO table_name (Result, Length, Country, Domain, Hour, OS, Mobile) VALUES (%s, %s, %s, %s, %s, %s)" val = ('benign','1096','USA', '127.0.0.1:5000', '22', 'Windows', '0') mycursor.execute(sql, val) mydb.commit()
The above lines will add a new row to the table with the above values.
That's all for this article, I hope you've learnt something useful from this tutorial. Thanks for reading!