Resolved: Connecting Python with MySQL
In the third section of the course, while writing the script for the Waterfall chart, how can we connect directly to MySQL and run our script there instead of extracting a CSV file and uploading it into Python?
1 answers ( 1 marked as helpful)
You can use the following code to connect mysql with Python directly.
conn = mysql.connector.connect(
host="localhost",
user="your user name(usually root)",
password="your_password",
database="name of the database(it is supposed to be growth_data)",use_pure = True
)
query = """
SELECT
user_id,
subscription_id,
CASE
WHEN subscription_type = 0 THEN "Monthly"
WHEN subscription_type = 1 THEN "Quarterly"
WHEN subscription_type = 2 THEN "Annual"
END as plan,
subscription_status as status,
CAST(date_start as DATE) as sub_start,
CAST(date_deactivated as DATE) as sub_end
FROM
student_subscriptions
WHERE
subscription_type != 3
ORDER BY user_id, date_start;
"""
# Load into DataFrame
df = pd.read_sql(query, con=conn)
# Preview
print(df.head())
conn.close()
conn = mysql.connector.connect(
host="localhost",
user="your user name(usually root)",
password="your_password",
database="name of the database(it is supposed to be growth_data)",use_pure = True
)
query = """
SELECT
user_id,
subscription_id,
CASE
WHEN subscription_type = 0 THEN "Monthly"
WHEN subscription_type = 1 THEN "Quarterly"
WHEN subscription_type = 2 THEN "Annual"
END as plan,
subscription_status as status,
CAST(date_start as DATE) as sub_start,
CAST(date_deactivated as DATE) as sub_end
FROM
student_subscriptions
WHERE
subscription_type != 3
ORDER BY user_id, date_start;
"""
# Load into DataFrame
df = pd.read_sql(query, con=conn)
# Preview
print(df.head())
conn.close()