Scribd
Upload
0 views2 pages

Data Gathering From SQL

The document provides a quick reference for using SQL with Python, detailing the structure of a sample database containing students, courses, and enrollments. It includes instructions for installing the MySQL connector, connecting to a database, executing SQL queries, and retrieving results using pandas. Additionally, it compares reading data from CSV files versus SQL databases and outlines a complete notebook pattern for executing queries.

Uploaded by

subhajitdutta.vivo.2002
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
0 views2 pages

Data Gathering From SQL

The document provides a quick reference for using SQL with Python, detailing the structure of a sample database containing students, courses, and enrollments. It includes instructions for installing the MySQL connector, connecting to a database, executing SQL queries, and retrieving results using pandas. Additionally, it compares reading data from CSV files versus SQL databases and outlines a complete notebook pattern for executing queries.

Uploaded by

subhajitdutta.vivo.2002
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL + Python Notebook — Quick Reference

1. Database structure
One database can contain multiple tables.

students

id name age

1 Rahul 24

2 Priya 25

3 Amit 23

courses

course_id course_name credits

101 Machine Learning 4

102 Database Systems 3

103 Algorithms 4

enrollments

student_id course_id semester

1 101 1

1 103 1

2 102 1

2. Install and import


Install the connector once:
pip install mysql-connector-python

Import it in the notebook, like pandas/NumPy:


import [Link]
import pandas as pd

3. Connect to the database


conn = [Link](
host="localhost",
user="root",
password="",
database="college"
)

For a local XAMPP setup, localhost means the database server is on your own computer.

4. Create a cursor
cursor = [Link]()

5. Write and execute a SQL query


query = "SELECT * FROM students"
[Link](query)

Examples:

Page 1
SELECT name, age FROM students;
SELECT * FROM students WHERE age > 23;
SELECT * FROM courses;
SELECT * FROM students ORDER BY age;

6. Get the result


data = [Link]()
df = [Link](data, columns=cursor.column_names)

7. Using pd.read_sql_query()
Important: pd.read_sql_query() does not read a saved SQL file. It sends your SQL query to the database
through the connection.
query = "SELECT * FROM students"

df = pd.read_sql_query(
query,
conn
)

Pattern: pd.read_sql_query(SQL_QUERY, DATABASE_CONNECTION)

8. CSV vs SQL database


Source Code

CSV file pd.read_csv("[Link]")

SQL database pd.read_sql_query("SELECT * FROM students", conn)

9. Complete notebook pattern


import [Link]
import pandas as pd

conn = [Link](
host="localhost",
user="root",
password="",
database="college"
)

query = "SELECT * FROM students"


df = pd.read_sql_query(query, conn)

print(df)

Page 2

You might also like