FQSoft - Python: MySQL

Install MySql connector

pip install mysql-connector-python

Secara konsep kita akan membuat variable db untuk koneksi ke mysql di file config.py . variable db tersebut akan panggil di class models.

Untuk query yang sifatnya untuk perubahan misal INSERT, DELETE, UPDATE maka diakhir kode harus menjalankan db.commit()

untuk memasukkan parameter yang aman (dari sql injection) ke query maka gunakan cara di bawah ini. untuk variable val bertipe data tuple, sehingga jika hanya ada satu parameter saja maka harus ditambah koma contoh : val = (self.name,) Dan yang perlu diingat setiap parameter yang disematkan ke query harus bertipe String

sql = "INSERT INTO customer (name, number,contact) VALUES (%s, %s, %s)"
        val = (self.name, self.number,self.contact)
        cursor = db.cursor()
        cursor.execute(sql,val)
        db.commit()

file config.py

import os
import mysql.connector

global APP_PATH
# Take fixed file path, and not the relative file path
APP_PATH = os.path.dirname(os.path.realpath(__file__))

db = mysql.connector.connect(
    host="localhost",
    user="user1",
    password="password1",
    database="nama_db"
)

file Model/Customer.py

from config import db
class Customer:
    customer_id = ''
    name = ''
    number = ''
    contact = ''
    
    def __str__(self):
        return f"{self.name}({self.number} - {self.contact})"
    
    def save(self):
        sql = "INSERT INTO customer (name, number,contact) VALUES (%s, %s, %s)"
        val = (self.name, self.number,self.contact)
        cursor = db.cursor()
        cursor.execute(sql,val)
        db.commit()

        return "Customer Saved"
    
    def update(self):
        sql = "UPDATE customer SET name = %s, number=%s, contact=%s WHERE customer_id = %s"
        val = (self.name, self.number,self.contact,self.customer_id)
        cursor = db.cursor()
        cursor.execute(sql,val)
        db.commit()

        return "Customer Updated"
    
    def getById(self):
        sql = "SELECT * FROM customer WHERE customer_id = %s %s"
        val = (self.customer_id,' ')
        cursor = db.cursor(dictionary=True)
        cursor.execute(sql,val)
        return cursor.fetchone()

    def getAll(self):
        sql = "SELECT * FROM customer"
        cursor = db.cursor(dictionary=True)
        cursor.execute(sql)
        return cursor.fetchall()
    
    def delete(self):
        sql = "DELETE FROM customer WHERE customer_id = %s"
        val = (self.customer_id,)
        cursor = db.cursor()
        cursor.execute(sql,val)
        db.commit()
        return "Customer Deleted"

file controllers/CutomerController.py

from models.Customer import Customer

class CustomerController():
    def insert(self):
        objCustomer = Customer()
        objCustomer.name = "Habudi"
        objCustomer.number = "MK5679"
        objCustomer.contact = "0815556"
    
        return objCustomer.save()
    
    def update(self):
        objCustomer = Customer()
        objCustomer.customer_id = "1"
        objCustomer.name = "Muhammad"
        objCustomer.number = "MK5678"
        objCustomer.contact = "0815555"
    
        return objCustomer.update()
    
    def getById(self,id):
        objCustomer = Customer()
        objCustomer.customer_id = id
        result = objCustomer.getById()
        # print(result[0]['name'])
        print(result)

    def getAll(self):
        objCustomer = Customer()
        result = objCustomer.getAll()
        # print(result[0]['name'])
        for item in result:
            print(item['name'])
    
    def delete(self,id):
        objCustomer = Customer()
        objCustomer.customer_id = id
        return objCustomer.delete()

file testunit.py

from controllers.CustomerController import *
from config import APP_PATH

objCusController = CustomerController()
print(objCusController.insert())
print(objCusController.update())
objCusController.getAll()
objCusController.getById(id='3')
objCusController.delete(id="3")

Blog FQSoft

Python: MySQL

Fahim