Learn Python

Learn Data Structure & Algorithm

Learn Numpy

Learn Pandas

Learn Matplotlib

Learn Seaborn

Learn Statistics

Learn Math

Learn MATLAB

Learn Machine learning

Learn Github

Learn OpenCV

Learn Deep Learning

MySQL Introduction

MySQL Setup

MySQL Operators

MySQL Basic Works

MySQL Create Table

MySQL insert and get values

MySQL Condition

MySQL GROUP BY

MySQL Case

MySQL sorting and calculate avg,sum,count

MySQL Update

MySQL Time

MySQL String

MySQL Rollback & Commit

MySQL Join and Concatenate

MySQL Index

MySQL Date

MySQL View

MYSQL With Python

Learn MongoDB

Learn Web scraping

Learn Excel

Learn Power BI

Learn Tableau

Learn Docker

Learn Hadoop

Learn everything to work with mysql using python

How to connect MYSQL database with python?

To connect mysql database with python use a library named mysql.connector.
To download and install we have to give command:

To command prompt:
pip install mysql-connector-python

To jupyter notebook and spyder:
!pip install mysql-connector-python

Code to connect with database

#Import the library
import mysql.connector as sql

connection=sql.connect(host="localhost",user="rafsun001",password="same_mail_pass")
"""
Here user name and the password is the password and name that we gave while installing.
"""

How to use mysql query in python?

All the syntax of queries or code that you had already learned in SQL will be same in python.
For example, if you create a database, you will write create database database_name; in SQL.
You will also write the same query or code in python but here you have to do some work before.

Here you have use a cursor function and you have to store it in a variable.

Example:
my_cursor=connection.cursor()

Here the connection is that variable name where you connected python with MySQL.

Now you will write MySQL query or code and will store it in a variable or you can directly write inside that function where you will pass this query variable.

Example:
query="create database My_database;"

Then to execute we write:
cursor_stored_variable_name.execute(variable name where we stored query")

or,
cursor_stored_variable_name.execute(" sql query or code")

Example:

import mysql.connector as sql

connection=sql.connect(host="localhost",user="rafsun001",password="same_mail_pass")

my_cursor=connection.cursor()

query="create database My_database;"
query="use My_database;"
my_cursor.execute(query)

my_cursor.execute("create table first_table( Id int PRIMARY KEY auto_increment ,Books varchar(100) default"missing" UNIQUE,Price int NOT NULL, Books_category varchar(20) NOT NULL );")

connection.commit()
connection.close()

How to insert values using python in mysql?

import mysql.connector as sql

connection=sql.connect(host="localhost",user="rafsun001",password="same_mail_pass")

my_cursor=connection.cursor()

query="create database My_database;"
query="use My_database;"
#here we wrote our queries and stored it in a variable.

my_cursor.execute(query)
"""
to execute query we pass the variable name(where we stored query) in execute function
"""

# Let's write query inside execute function.
my_cursor.execute("create table first_table( Id int PRIMARY KEY auto_increment ,Books varchar(100) default"missing" UNIQUE,Price int NOT NULL, Books_category varchar(20) NOT NULL );")
"""
Here we directly wrote our query inside execute function.
"""

query2="insert into first_table(Books,Price,Books_category) values(%s,%s,%s)"
"""
Here first_table is the table name and inside the bracket pass the columns name. Because we want to insert values so we wrote values and in the bracket wrote %s. Here I wrote %s three times because I have three columns. You will write according to your number of columns.
"""

#Let's put values as we put in sql
values=[ ("SQL",430,"Programming"), ("Python",323,"Programming"), ("Javascript",300,"Programming"), ("Data science",1000,"Programming") ]

my_cursor.executemany(query2,values)
"""
To execute more than one thing we use executemany function.
"""

print(my_cursor.fetchall())

#we use fetchall function to read the data

#we can also run a loop to read the data.
for i in my_cursor:
print(i)

commit()
connection.close()

How to read data like dataset from mysql database?

import pandas as pd
import mysql.connector as sql

connection=sql.connect(host="localhost",user="rafsun001",password="same_mail",database="books_store")
query="select * from first_table"

df=pd.read_sql(query,connection)
df

CodersAim is created for learning and training a self learner to become a professional from beginner. While using CodersAim, you agree to have read and accepted our terms of use, privacy policy, Contact Us