Install Library

pip install mysql-connector-python

Create Connection

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourusername",
  database="database_name"
)

if db.is_connected():
  print("Connect to db successfully")

Creating a Database

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword"
)

mycursor = db.cursor()

mycursor.execute("CREATE DATABASE mydatabase")

Creating a Table

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

cursor = db.cursor()

sql = """CREATE TABLE customers (
  customer_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  address Varchar(255)
)
"""
cursor.execute(sql)

print("Create Table Successfully")

Select

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

cursor = db.cursor()

cursor.execute("SELECT * FROM customers")

result = cursor.fetchall()

for x in result:
  print(x)

Select Where

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

cursor = db.cursor()

sql = "SELECT * FROM barang WHERE kd_barang = %s"
val = ("2", )

cursor.execute(sql, val)

result = cursor.fetchall()

for x in result:
  print(x)

Insert One

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

cursor = db.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
cursor.execute(sql, val)

db.commit()

print(cursor.rowcount, "record inserted.")

Insert Many

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

cursor = db.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21')
]

cursor.executemany(sql, val)

db.commit()

print(cursor.rowcount, "was inserted.")

Get Inserted ID

db.commit()
last_id = cursor.lastrowid

Update

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

cursor = db.cursor()

sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'"

cursor.execute(sql)

db.commit()

print(cursor.rowcount, "record(s) affected")

Delete

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

cursor = db.cursor()

sql = "DELETE FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )

cursor.execute(sql, adr)

db.commit()

print(cursor.rowcount, "record(s) deleted")