Get started with Pandas and MySQL

How to make a dataframe from your SQL database and query the data

This assumes you already have a sample database set up on your MySQL server and you have the username and password.

In the example shown we are logging on to a Raspberry Pi running MariaDB and we are executing a query to get 3408 properties into a dataframe.

We then run it in Python and view the results.

The Python code is as follows:

import mysql.connector
import pandas as pd
try:
cnx = mysql.connector.connect(user='user2', password='password2', host='192.168.1.9', database='immodb')
query = '''select * from imt1'''
SQL_Query = pd.read_sql_query(query, cnx)
df = pd.DataFrame(SQL_Query,columns=['title','price'])
print(df)
except mysql.connector.Error as err:
print(err)
else:
cnx.close()