Connecting-Python-with-MySQL

Here you will find all steps you need to do for connecting Python using Visual Studio Code with MySQL

First Step: Install MySQL

Download and Install MySQL using this link https://dev.mysql.com/downloads/mysql/
after Installing it will open MySQL configurator
press next then in account you have to input a password (Don't forget this Password)
then next again untill you are in apply configuration tab
In apply configuration tab press execute and when it finishes press next and now congratulation first step is completed

Second Step: Install Visual Studio Code

You can simply download VS code from Microsoft Store (if you are on Windows) or using this link https://code.visualstudio.com/download
then download python also from Microsoft Store (if you are on Windows) or using this link https://www.python.org/downloads/
after the download and install is complete open VS code and go to Extensions tab and download Python, Jupyter and MySQL extensions
here is how they look like:

Third Step: Make Database in MySQL

Open MySQL Command Line Client Enter password you made in the configurator
then type these queries:
CREATE DATABASE Manage_Products
USE Manage_Products

Fourth Step: Step up MySQL inside VS Code

1-Open Database Explorer panel, then click the + button.
2-Select your database type, input connection config then click the connect button.
while in the connect tab enter a Database name Manage_Products and password you made for MySQL then click connect
you should see the connection like this:

Congrats now you can use MySQL inside VS code

Example:

Now this an example on how you can work
first make a Query file in your Database using this + sign:

inside Query write this line show VARIABLES Like 'secure_file_priv';
this code will show you the directory in which MySQL is reading from so all data that you want to upload must be in this directory
I have left sample data in these three files found in this repo aisles.csv , products.csv and departments.csv
upload it in the directory that was show using the above code

Now let's make some tables inside our database write the following code:

CREATE TABLE Aisles (
    aisle_id INT PRIMARY KEY,
    aisle VARCHAR(255)
);

LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 9.0/Uploads/aisles.csv"
INTO TABLE aisles
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(aisle_id, aisle);

CREATE TABLE departments (
    dep_id INT PRIMARY KEY,
    dep VARCHAR(255)
);


LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 9.0/Uploads/departments.csv"
INTO TABLE departments
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(dep_id, dep);

CREATE TABLE products(
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    aisle_id INT,
    dep_id INT,
    FOREIGN KEY (aisle_id) REFERENCES aisles(aisle_id),
    FOREIGN KEY (dep_id) REFERENCES departments(dep_id)
);

LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 9.0/Uploads/products.csv"
INTO TABLE products
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(product_id, product_name, aisle_id, dep_id);

run this code it will make three tables using data samples

Trying to add data on MySQL using Python

In this step we will try to add data on the MySQL using Python
we will these data add_aisles.csv , add_products.csv , add_departments.csv you will find them attached in this repo download and save them.
Now make a new jupyter notebook file in VS code:
open command prompt in your computer and write these lines to install some python libraries we will use.
pip install mysql
pip install pandas
after they finish installing write these codes

import pymysql
import pandas as pd

this code will import the libraries we just install

dep = pd.read_csv("file_path/add_departments.csv") # remove file path word and enter the file path where you downloaded the sample data
aisles = pd.read_csv("file_path/add_aisles.csv")
products = pd.read_csv("file_path/add_products.csv")  

this code will read our downloaded data add_aisles.csv , add_products.csv , add_departments.csv

conn = pymysql.connect(
    host='localhost',
    user='root',
    password='your password',          # remove your password and enter the password you made for MySQL
    database='Manage_Products'
)
cursor = conn.cursor()

Write these three codes to add data to your MySQl tables

for index, row in departments.iterrows():
    cursor.execute("INSERT INTO departments (dep_id, dep) VALUES (%s,%s)",
                   (row['department_id'], row['department']))
    
conn.commit()

for index, row in aisles.iterrows():
    cursor.execute("INSERT INTO aisles (aisle_id, aisle) VALUES (%s,%s)",
                   (row['aisle_id'], row['aisle']))
    
conn.commit()

for index, row in products.iterrows():
    cursor.execute("INSERT INTO products (product_id, product_name, aisle_id, dep_id) VALUES (%s,%s,%s,%s)",
                   (row['product_id'], row['product_name'], row['aisle_id'], row['department_id']))
    
conn.commit()

Now we can save the complete tables in a new csv files using this code

query = f"SELECT * from {table}"  # remove {table} and enter which table you want to save example: aisles, departments, Products
df = pd.read_sql(query, conn)
#save each table to a csv file
df.to_csv(f"choose_path/{table}.csv", index= False)  # remove choose_path and enter path where you want to save the new files, remove {table} and enter a name for your table