#!/usr/bin/env python
# coding: utf-8

# Install ipython-sql to be able to run sql in Notebook

# In[1]:


get_ipython().system('pip install ipython-sql')
get_ipython().system('pip install pymysql')
get_ipython().system('pip install mysql-connector-python')


# ## Connect to database (Method 1)

# In[1]:


get_ipython().run_line_magic('load_ext', 'sql')


# Connection String to your database.

# In[26]:


get_ipython().run_line_magic('sql', 'mysql+pymysql://root:1234@localhost/information_schema')


# In[29]:


get_ipython().run_cell_magic('sql', '', '\nselect * from movies.director;\n')


# In[8]:


get_ipython().run_cell_magic('sql', '', '\nshow tables;\n')


# In[11]:


get_ipython().run_cell_magic('sql', '', 'select  * from movies, director;\n')


# ## Connect to Database (Method 2)

# In[5]:


import pandas as pd
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+mysqlconnector://root:1234@localhost/movies')


# In[25]:


from sqlalchemy import text

with engine.connect() as connection:
    result = connection.execute(text("select * from movies"))
    for row in result:
        print(row)


# ## Connect to Database (Method 3)

# In[14]:


import mysql.connector # using mysql-connector-python
cnx=mysql.connector.connect(user='root', password='1234', host='127.0.0.1', database='movies')


# In[16]:


query='select * from movies;'
cursor = cnx.cursor()
cursor.execute(query)


# In[17]:


for (o) in cursor:
  print(o)


# ### To read into a data frame

# In[22]:


myFrames = pd.read_sql_query(query, cnx)


# In[23]:


myFrames