Install ipython-sql to be able to run sql in Notebook
!pip install ipython-sql
!pip install pymysql
!pip install mysql-connector-python
WARNING: Skipping /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/six-1.16.0-py3.11.egg-info due to invalid metadata entry 'name' WARNING: Skipping /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/six-1.16.0-py3.11.egg-info due to invalid metadata entry 'name' Requirement already satisfied: ipython-sql in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (0.5.0) Requirement already satisfied: prettytable in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython-sql) (3.6.0) Requirement already satisfied: ipython in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython-sql) (8.11.0) Requirement already satisfied: sqlalchemy>=2.0 in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython-sql) (2.0.8) Requirement already satisfied: sqlparse in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython-sql) (0.4.3) Requirement already satisfied: six in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython-sql) (1.11.0) Requirement already satisfied: ipython-genutils in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython-sql) (0.2.0) Requirement already satisfied: typing-extensions>=4.2.0 in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from sqlalchemy>=2.0->ipython-sql) (4.5.0) Requirement already satisfied: greenlet!=0.4.17 in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from sqlalchemy>=2.0->ipython-sql) (2.0.2) Requirement already satisfied: backcall in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython->ipython-sql) (0.2.0) Requirement already satisfied: decorator in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython->ipython-sql) (5.1.1) Requirement already satisfied: jedi>=0.16 in /Users/User/Library/Python/3.11/lib/python/site-packages (from ipython->ipython-sql) (0.18.2) Requirement already satisfied: matplotlib-inline in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython->ipython-sql) (0.1.6) Requirement already satisfied: pickleshare in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython->ipython-sql) (0.7.5) Requirement already satisfied: prompt-toolkit!=3.0.37,<3.1.0,>=3.0.30 in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython->ipython-sql) (3.0.38) Requirement already satisfied: pygments>=2.4.0 in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython->ipython-sql) (2.14.0) Requirement already satisfied: stack-data in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython->ipython-sql) (0.6.2) Requirement already satisfied: traitlets>=5 in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython->ipython-sql) (5.9.0) Requirement already satisfied: pexpect>4.3 in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython->ipython-sql) (4.8.0) Requirement already satisfied: appnope in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from ipython->ipython-sql) (0.1.3) Requirement already satisfied: wcwidth in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from prettytable->ipython-sql) (0.2.6) Requirement already satisfied: parso<0.9.0,>=0.8.0 in /Users/User/Library/Python/3.11/lib/python/site-packages (from jedi>=0.16->ipython->ipython-sql) (0.8.3) Requirement already satisfied: ptyprocess>=0.5 in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from pexpect>4.3->ipython->ipython-sql) (0.7.0) Requirement already satisfied: executing>=1.2.0 in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from stack-data->ipython->ipython-sql) (1.2.0) Requirement already satisfied: asttokens>=2.1.0 in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from stack-data->ipython->ipython-sql) (2.2.1) Requirement already satisfied: pure-eval in /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages (from stack-data->ipython->ipython-sql) (0.2.2) WARNING: Skipping /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/six-1.16.0-py3.11.egg-info due to invalid metadata entry 'name' WARNING: Skipping /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/six-1.16.0-py3.11.egg-info due to invalid metadata entry 'name' WARNING: Skipping /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/six-1.16.0-py3.11.egg-info due to invalid metadata entry 'name' WARNING: Skipping /Users/user/homebrew/Cellar/python@3.11/3.11.2_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/six-1.16.0-py3.11.egg-info due to invalid metadata entry 'name'
%load_ext sql
Connection String to your database.
%sql mysql+pymysql://root:1234@localhost/information_schema
%%sql
select * from movies.director;
* mysql+pymysql://root:***@localhost/information_schema mysql+pymysql://root:***@localhost/movies 2 rows affected.
| id | dname |
|---|---|
| 1 | shonmda rhymes |
| 2 | steven sterberg |
%%sql
show tables;
mysql+pymysql://root:***@localhost/information_schema * mysql+pymysql://root:***@localhost/movies 3 rows affected.
| Tables_in_movies |
|---|
| director |
| movies |
| participate |
%%sql
select * from movies, director;
mysql+pymysql://root:***@localhost/information_schema * mysql+pymysql://root:***@localhost/movies 12 rows affected.
| mid | mname | rating | id | dname |
|---|---|---|---|---|
| 1 | Jurassic Park | 9.0 | 2 | steven sterberg |
| 1 | Jurassic Park | 9.0 | 1 | shonmda rhymes |
| 2 | west side stories | 8.0 | 2 | steven sterberg |
| 2 | west side stories | 8.0 | 1 | shonmda rhymes |
| 3 | crossroads | 7.0 | 2 | steven sterberg |
| 3 | crossroads | 7.0 | 1 | shonmda rhymes |
| 4 | princesse diary | 9.0 | 2 | steven sterberg |
| 4 | princesse diary | 9.0 | 1 | shonmda rhymes |
| 2 | das | 11.0 | 2 | steven sterberg |
| 2 | das | 11.0 | 1 | shonmda rhymes |
| 10 | das | 11.0 | 2 | steven sterberg |
| 10 | das | 11.0 | 1 | shonmda rhymes |
import pandas as pd
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+mysqlconnector://root:1234@localhost/movies')
from sqlalchemy import text
with engine.connect() as connection:
result = connection.execute(text("select * from movies"))
for row in result:
print(row)
(1, 'Jurassic Park', 9.0) (2, 'west side stories', 8.0) (3, 'crossroads', 7.0) (4, 'princesse diary', 9.0) (2, 'das', 11.0) (10, 'das', 11.0)
import mysql.connector # using mysql-connector-python
cnx=mysql.connector.connect(user='root', password='1234', host='127.0.0.1', database='movies')
query='select * from movies;'
cursor = cnx.cursor()
cursor.execute(query)
for (o) in cursor:
print(o)
(1, 'Jurassic Park', 9.0) (2, 'west side stories', 8.0) (3, 'crossroads', 7.0) (4, 'princesse diary', 9.0) (2, 'das', 11.0) (10, 'das', 11.0)
myFrames = pd.read_sql_query(query, cnx)
/var/folders/rz/zdccgsws7xb56zbddkrk6_8c0000gq/T/ipykernel_23220/2576899907.py:1: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy. myFrames = pd.read_sql_query(query, cnx)
myFrames
| mid | mname | rating | |
|---|---|---|---|
| 0 | 1 | Jurassic Park | 9.0 |
| 1 | 2 | west side stories | 8.0 |
| 2 | 3 | crossroads | 7.0 |
| 3 | 4 | princesse diary | 9.0 |
| 4 | 2 | das | 11.0 |
| 5 | 10 | das | 11.0 |