MySQL with Python¶

Python dapat digunakan dalam aplikasi basis data.
Salah satu basis data yang paling populer adalah MySQL.

MySQL Database¶

Agar dapat bereksperimen dengan contoh kode dalam tutorial ini, Anda harus menginstal MySQL di komputer Anda.

Anda dapat mengunduh basis data MySQL di https://www.mysql.com/downloads/.

Install MySQL Driver¶

Python membutuhkan driver MySQL untuk mengakses basis data MySQL.
Dalam tutorial ini, kita akan menggunakan driver "MySQL Connector".
Kami menyarankan Anda menggunakan PIP untuk menginstal "MySQL Connector".
PIP kemungkinan besar sudah terpasang di lingkungan Python Anda.

Buka baris perintah Anda dan temukan lokasi PIP, lalu ketik:downloads/.

#Download and install "MySQL Connector":
C:\Users\Your Name\AppData\Local\Programs\Python\Python36-32\Scripts>python -m pip install mysql-connector-python

Test MySQL Connector¶

Untuk menguji apakah instalasi berhasil, atau jika Anda sudah menginstal "MySQL Connector", buat halaman Python dengan konten berikut:

#demo_mysql_test.py:

import mysql.connector

Jika kode di atas dijalankan tanpa kesalahan, "MySQL Connector" terinstal dan siap digunakan.

Create Connection¶

Mulailah dengan membuat koneksi ke basis data.

Gunakan nama pengguna dan kata sandi dari basis data MySQL Anda:

#demo_mysql_connection.py:

import mysql.connector

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

print(mydb)

Sekarang Anda dapat mulai melakukan kueri pada basis data menggunakan pernyataan SQL.

Creating a Database¶

Untuk membuat database di MySQL, gunakan pernyataan "CREATE DATABASE":

#create a database named "mydatabase":

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE mydatabase")

Jika kode di atas dijalankan tanpa kesalahan, Anda telah berhasil membuat basis data.

Check if Database Exists¶

Anda dapat memeriksa keberadaan suatu basis data dengan mencantumkan semua basis data di sistem Anda menggunakan pernyataan "SHOW DATABASES":

#Return a list of your system's databases:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SHOW DATABASES")

for x in mycursor:
  print(x)

#result:
# ('information_scheme',)
# ('mydatabase',)
# ('performance_schema',)
# ('sys',)

Atau Anda dapat mencoba mengakses basis data saat membuat koneksi:

#Try connecting to the database "mydatabase":

import mysql.connector

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

Jika basis data tidak ada, Anda akan mendapatkan kesalahan.

Creating a Table¶

Untuk membuat tabel di MySQL, gunakan pernyataan "CREATE TABLE".

Pastikan Anda menentukan nama database saat membuat koneksi.

#Create a table named "customers":

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

Jika kode di atas dijalankan tanpa kesalahan, Anda kini berhasil membuat tabel.

Check if Table Exists¶

Anda dapat memeriksa apakah suatu tabel ada dengan mencantumkan semua tabel dalam database Anda dengan pernyataan "SHOW TABLES":

#Return a list of your system's databases:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SHOW TABLES")

for x in mycursor:
  print(x)

#result:
# ('customers',)

Primary Key¶

Saat membuat tabel, Anda juga harus membuat kolom dengan kunci unik untuk setiap rekaman.

Hal ini dapat dilakukan dengan menentukan PRIMARY KEY.

Kita menggunakan pernyataan "INT AUTO_INCREMENT PRIMARY KEY" yang akan memasukkan nomor unik untuk setiap rekaman. Dimulai dari 1, dan ditambah satu untuk setiap record.

#Create primary key when creating the table:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

Jika tabel sudah ada, gunakan kata kunci ALTER TABLE:

#Create primary key on an existing table:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

Insert Into Table¶

Untuk mengisi tabel di MySQL, gunakan pernyataan "INSERT INTO".

#Insert a record in the "customers" table:

import mysql.connector

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

mycursor = mydb.cursor()

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

mydb.commit()

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

Catatan

Perhatikan pernyataan: mydb.commit(). Pernyataan ini diperlukan untuk membuat perubahan, jika tidak, tidak ada perubahan yang dibuat pada tabel.

Insert Multiple Rows¶

Untuk menyisipkan beberapa baris ke dalam tabel, gunakan metode executemany().

Parameter kedua dari metode executemany() adalah daftar tupel yang berisi data yang ingin Anda sisipkan:

#Fill the "customers" table with data:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2'),
  ('Betty', 'Green Grass 1'),
  ('Richard', 'Sky st 331'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
]

mycursor.executemany(sql, val)

mydb.commit()

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

Get Inserted ID¶

Anda bisa mendapatkan id baris yang baru saja Anda sisipkan dengan menanyakan objek kursor.

Catatan

Jika Anda menyisipkan lebih dari satu baris, id baris terakhir yang disisipkan akan dikembalikan.

#Insert one row, and return the ID:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("Michelle", "Blue Village")
mycursor.execute(sql, val)

mydb.commit()

print("1 record inserted, ID:", mycursor.lastrowid)

Select From a Table¶

Untuk memilih dari tabel di MySQL, gunakan pernyataan "SELECT":

#Select all records from the "customers" table, and display the result:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

#result:
# (1, 'John', 'Highway 21')
# (2, 'Peter', 'Lowstreet 27')
# (3, 'Amy', 'Apple st 652')
# (4, 'Hannah', 'Mountain 21')
# (5, 'Michael', 'Valley 345')
# (6, 'Sandy', 'Ocean blvd 2')
# (7, 'Betty', 'Green Grass 1')
# (8, 'Richard', 'Sky st 331')
# (9, 'Susan', 'One way 98')
# (10, 'Vicky', 'Yellow Garden 2')
# (11, 'Ben', 'Park Lane 38')
# (12, 'William', 'Central st 954')
# (13, 'Chuck', 'Main Road 989')
# (14, 'Viola', 'Sideway 1633')
# (15, 'Michelle', 'Blue Village')

Catatan

Kami menggunakan metode fetchall(), yang mengambil semua baris dari pernyataan terakhir yang dieksekusi.

Selecting Columns¶

Untuk memilih hanya beberapa kolom dalam tabel, gunakan pernyataan "SELECT" diikuti dengan nama kolom:

#Select only the name and address columns:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SELECT name, address FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Using the fetchone() Method¶

Jika Anda hanya tertarik pada satu baris, Anda dapat menggunakan metode fetchone().

Metode fetchone() akan mengembalikan baris pertama dari hasil:

#Fetch only one row:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchone()

print(myresult)

#result: (1, 'John', 'Highway 21')

Select With a Filter¶

Saat memilih rekaman dari tabel, Anda dapat memfilter pilihan dengan menggunakan pernyataan "WHERE":

#Select record(s) where the address is "Park Lane 38": result:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT * FROM customers WHERE address ='Park Lane 38'"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

#result: (11, 'Ben', 'Park Lane 38')

Wildcard Characters¶

Anda juga dapat memilih rekaman yang dimulai, berisi, atau diakhiri dengan huruf atau frasa tertentu.

Gunakan % untuk mewakili karakter wildcard:

#Select records where the address contains the word "way":

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT * FROM customers WHERE address LIKE '%way%'"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Prevent SQL Injection¶

Ketika nilai kueri diberikan oleh pengguna, Anda harus melakukan escape terhadap nilai tersebut.

Hal ini untuk mencegah injeksi SQL, yang merupakan teknik peretasan web umum untuk menghancurkan atau menyalahgunakan basis data Anda.

Modul mysql.connector memiliki metode untuk melakukan escape terhadap nilai kueri:

#Escape query values by using the placholder %s method:

import mysql.connector

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

mycursor = mydb.cursor()

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

mycursor.execute(sql, adr)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Sort the Result¶

Gunakan pernyataan ORDER BY untuk mengurutkan hasil dalam urutan menaik atau menurun.

Kata kunci ORDER BY mengurutkan hasil secara menaik secara default. Untuk mengurutkan hasil dalam urutan menurun, gunakan kata kunci DESC.

#Sort the result alphabetically by name: result:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT * FROM customers ORDER BY name"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

ORDER BY DESC¶

#Sort the result reverse alphabetically by name:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT * FROM customers ORDER BY name DESC"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Delete Record¶

Anda dapat menghapus rekaman dari tabel yang ada dengan menggunakan pernyataan "DELETE FROM":

#Delete any record where the address is "Mountain 21":

import mysql.connector

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

mycursor = mydb.cursor()

sql = "DELETE FROM customers WHERE address = 'Mountain 21'"

mycursor.execute(sql)

mydb.commit()

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

Catatan

Perhatikan pernyataan: mydb.commit(). Pernyataan ini diperlukan untuk membuat perubahan, jika tidak, tidak ada perubahan yang dibuat pada tabel.

Prevent SQL Injection¶

Meng-escape nilai dari setiap kueri, termasuk dalam pernyataan delete, dianggap sebagai praktik yang baik.

Hal ini untuk mencegah injeksi SQL, yang merupakan teknik peretasan web umum untuk menghancurkan atau menyalahgunakan basis data Anda.

Modul mysql.connector menggunakan placeholder %s untuk meng-escape nilai dalam pernyataan delete:

#Escape values by using the placeholder %s method:

import mysql.connector

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

mycursor = mydb.cursor()

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

mycursor.execute(sql, adr)

mydb.commit()

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

Delete a Table¶

Anda dapat menghapus tabel yang ada dengan menggunakan pernyataan "DROP TABLE":

#Delete the table "customers":

import mysql.connector

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

mycursor = mydb.cursor()

sql = "DROP TABLE customers"

mycursor.execute(sql)

Drop Only if Exist¶

Jika tabel yang ingin Anda hapus sudah terhapus, atau karena alasan lain tidak ada, Anda dapat menggunakan kata kunci IF EXISTS untuk menghindari kesalahan.

#Delete the table "customers" if it exists:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "DROP TABLE IF EXISTS customers"

mycursor.execute(sql)

Update Table¶

Anda dapat memperbarui catatan yang ada dalam tabel dengan menggunakan pernyataan "UPDATE":

#Overwrite the address column from "Valley 345" to "Canyon 123":

import mysql.connector

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

mycursor = mydb.cursor()

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

mycursor.execute(sql)

mydb.commit()

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

Penting

Perhatikan pernyataan: mydb.commit(). Pernyataan ini diperlukan untuk membuat perubahan, jika tidak, tidak ada perubahan yang dibuat pada tabel.

Perhatikan klausa WHERE dalam sintaks UPDATE: Klausa WHERE menentukan rekaman mana yang harus diperbarui. Jika Anda menghilangkan klausa WHERE, semua rekaman akan diperbarui!

Prevent SQL Injection¶

#Escape values by using the placeholder %s method:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "UPDATE customers SET address = %s WHERE address = %s"
val = ("Valley 345", "Canyon 123")

mycursor.execute(sql, val)

mydb.commit()

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

Limit the Result¶

Anda dapat membatasi jumlah rekaman yang dikembalikan dari kueri dengan menggunakan pernyataan "LIMIT":

#Select the 5 first records in the "customers" table:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers LIMIT 5")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Start From Another Position¶

Jika Anda ingin mengembalikan lima catatan, dimulai dari catatan ketiga, Anda dapat menggunakan kata kunci "OFFSET":

#Start from position 3, and return 5 records:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Join Two or More Tables¶

Anda dapat menggabungkan baris dari dua tabel atau lebih, berdasarkan kolom terkait di antara keduanya, dengan menggunakan pernyataan JOIN.

Misalkan Anda memiliki tabel "users" dan tabel "products":

users
{ id: 1, name: 'John', fav: 154},
{ id: 2, name: 'Peter', fav: 154},
{ id: 3, name: 'Amy', fav: 155},
{ id: 4, name: 'Hannah', fav:},
{ id: 5, name: 'Michael', fav:}

products
{ id: 154, name: 'Chocolate Heaven' },
{ id: 155, name: 'Tasty Lemons' },
{ id: 156, name: 'Vanilla Dreams' }

Kedua tabel ini dapat digabungkan dengan menggunakan kolom users.fav dan kolom products.id.

#Join users and products to see the name of the users favorite product:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  INNER JOIN products ON users.fav = products.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

#result
# ('John', 'Chocolate Heaven')
# ('Peter', 'Chocolate Heaven')
# ('Amy', 'Tasty Lemon')

Catatan

Anda bisa menggunakan JOIN, bukan INNER JOIN. Keduanya akan memberikan hasil yang sama.

LEFT JOIN¶

Dalam contoh di atas, Hannah dan Michael tidak dimasukkan dalam hasil, karena INNER JOIN hanya menampilkan data yang cocok.

Jika Anda ingin menampilkan semua pengguna, meskipun mereka tidak memiliki produk favorit, gunakan pernyataan LEFT JOIN:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  LEFT JOIN products ON users.fav = products.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

#result
# ('John', 'Chocolate Heaven')
# ('Peter', 'Chocolate Heaven')
# ('Amy', 'Tasty Lemon')
# ('Hannah', None)
# ('Michael', None)

RIGHT JOIN¶

Jika Anda ingin mengembalikan semua produk, dan pengguna yang menjadikannya favorit, bahkan jika tidak ada pengguna yang menjadikannya favorit, gunakan pernyataan RIGHT JOIN:

#Select all products, and the user(s) who have them as their favorite:

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  RIGHT JOIN products ON users.fav = products.id"

Catatan

Hannah dan Michael, yang tidak memiliki produk favorit, tidak disertakan dalam hasil.

MySQL with Python¶

MySQL Database¶

Install MySQL Driver¶

Test MySQL Connector¶

Create Connection¶

Creating a Database¶

Check if Database Exists¶

Creating a Table¶

Check if Table Exists¶

Primary Key¶

Insert Into Table¶

Insert Multiple Rows¶

Get Inserted ID¶

Select From a Table¶

Selecting Columns¶

Using the fetchone() Method¶

Select With a Filter¶

Wildcard Characters¶

Prevent SQL Injection¶

Sort the Result¶

ORDER BY DESC¶

Delete Record¶

Prevent SQL Injection¶

Delete a Table¶

Drop Only if Exist¶

Update Table¶

Prevent SQL Injection¶

Limit the Result¶

Start From Another Position¶

Join Two or More Tables¶

LEFT JOIN¶

RIGHT JOIN¶

Daftar Isi

Topik sebelumnya

Topik berikutnya