Connecting to MySQL and performing SQL Queries in Python

The goal of this project is to connect python and MySQL and perform the SQL queries in python. The process is as follows:

  1. Install and import required packages.
  2. Creating engine to connect to MySQL using credentials.
  3. Converting table from SQL into a Data Frame.
  4. Performing SQL query in python.
  5. Updating database with new data using SQL query.

I hope you enjoy this.

Installing and importing necessary packages

In [1]:
! pip install pymysql
! pip install mysql
! pip install sqlalchemy

import pandas as pd
import mysql.connector as mysql
import sqlalchemy
Requirement already satisfied: pymysql in c:\users\saluj\appdata\local\continuum\anaconda3\lib\site-packages (0.9.3)
Requirement already satisfied: mysql in c:\users\saluj\appdata\local\continuum\anaconda3\lib\site-packages (0.0.2)
Requirement already satisfied: mysqlclient in c:\users\saluj\appdata\local\continuum\anaconda3\lib\site-packages (from mysql) (1.4.4)
Requirement already satisfied: sqlalchemy in c:\users\saluj\appdata\local\continuum\anaconda3\lib\site-packages (1.3.1)

Input your credentials here

In [2]:
host = "**********"    ## input host here
user = "**********"  ## input username here
password = "**********"  ## input password here
database = "Twitter"     ## input schema(database) name here

Creating engine to connect to MySQL

In [3]:
engine = sqlalchemy.create_engine(f'mysql+pymysql://{user}:{password}@{host}:3306/{database}')

Reading a table in the specified database

In [4]:
df1 = pd.read_sql_table("tableau_data", engine)

df1.head()
Out[4]:
Date Twitter_ID User_Name Followers Tweet Link Favourites Retweets
0 2019-08-12 erg_data ERG Data 1 Who posted this image first? Great way to expl... https://t.co/5e4WJFvSi1 0 1
1 2019-08-12 AblazeGroupBI Denise Peck 259 LOOK! $90/hr is THE NEW #FTE $45/hr 👉 https://... https://t.co/MNZU3zKULn 0 3
2 2019-08-12 Gustav0Eiffel Gustavo 4 Is anyone have an idea on how to filter data o... None 1 1
3 2019-08-12 HitmarkerJobs HitmarkerJobs.com // Esports Jobs 11765 ⚡ NEW: If you've lived in a world of #SQL and ... https://t.co/aIP5CIRhaI 2 1
4 2019-08-12 fredffery Frederic Fery 508 Pharmaceutical research and development - #tab... https://t.co/uhj8F6IM64 5 1

Performing SQL Query to Select data from the database

In [5]:
query='''

SELECT * FROM tableau_data ORDER by Date DESC;

'''

df1 = pd.read_sql_query(query, engine)

df1.head(5)
Out[5]:
Date Twitter_ID User_Name Followers Tweet Link Favourites Retweets
0 2019-08-12 erg_data ERG Data 1 Who posted this image first? Great way to expl... https://t.co/5e4WJFvSi1 0 1
1 2019-08-12 AblazeGroupBI Denise Peck 259 LOOK! $90/hr is THE NEW #FTE $45/hr 👉 https://... https://t.co/MNZU3zKULn 0 3
2 2019-08-12 Gustav0Eiffel Gustavo 4 Is anyone have an idea on how to filter data o... None 1 1
3 2019-08-12 HitmarkerJobs HitmarkerJobs.com // Esports Jobs 11765 ⚡ NEW: If you've lived in a world of #SQL and ... https://t.co/aIP5CIRhaI 2 1
4 2019-08-12 fredffery Frederic Fery 508 Pharmaceutical research and development - #tab... https://t.co/uhj8F6IM64 5 1

Reading a local csv file to append our Twitter database

In [6]:
new_tweets = pd.read_csv('new_tweets.csv')

Appending news tweets to tablea_data table

In [ ]:
new_tweets.to_sql(
    name = 'tableau_data', #table name
    con = engine,
    index = False,
    if_exists = 'append'
)

Performing SQL Query to check if the new_tweets was uploaded to the database

In [8]:
query='''

SELECT * FROM tableau_data ORDER BY Date DESC;

'''

df1 = pd.read_sql_query(query, engine)

df1.head(5)
Out[8]:
Date Twitter_ID User_Name Followers Tweet Link Favourites Retweets
0 2019-08-16 frances_okolo Frances Okolo 17 #Nigeria #Tableau #VisOfTheDay #Maps\n\nGoodnight https://t.co/8SuyWPii2V 0 0
1 2019-08-16 infolabUK The Information Lab 7529 #TableauTip 💡\nHow to save time 🕒 on default p... https://t.co/O8hA7cGlPh 1 2
2 2019-08-16 ByStefCaldwell Stef Caldwell 5 There are people that walk into your life and ... https://t.co/UWBdjJsWdX 0 0
3 2019-08-16 YEMGunter Yolanda Gunter 1832 This happened last night w @lelienmusic after ... https://t.co/9HJAoKe6uW 1 0
4 2019-08-16 craigbloodworth Craig Bloodworth 5596 It's Desktop, no it's Prep, no! It's The Hyper... https://t.co/0IZRyBJY3g 9 0

Author: Amandeep Saluja