Python - Akses Database MySQL

Standar Python untuk antarmuka database adalah Python DB-API. Sebagian besar antarmuka database Python mengikuti standar ini.

Anda dapat memilih database yang tepat untuk aplikasi Anda. Python Database API mendukung berbagai server database seperti -

• GadFly
• mSQL
• MySQL
• PostgreSQL
• Microsoft SQL Server 2000
• Informix
• Interbase
• Oracle
• Sybase

Berikut adalah daftar antarmuka database Python yang tersedia: Python Database Interfaces dan API . Anda harus mengunduh modul DB API terpisah untuk setiap database yang perlu Anda akses. Misalnya, jika Anda perlu mengakses database Oracle serta database MySQL, Anda harus mendownload modul database Oracle dan MySQL.

DB API menyediakan standar minimal untuk bekerja dengan database menggunakan struktur dan sintaks Python jika memungkinkan. API ini meliputi:

• Mengimpor modul API.
• Mendapatkan koneksi dengan database.
• Menerbitkan pernyataan SQL dan prosedur tersimpan.
• Menutup koneksi

Kita akan mempelajari semua konsep menggunakan MySQL, jadi mari kita bahas tentang modul MySQLdb.

Apa itu MySQLdb?

MySQLdb adalah antarmuka untuk menghubungkan ke server database MySQL dari Python. Ini mengimplementasikan Python Database API v2.0 dan dibangun di atas MySQL C API.

Bagaimana cara menginstal MySQLdb?

Sebelum melanjutkan, pastikan Anda telah menginstal MySQLdb di komputer Anda. Cukup ketikkan yang berikut ini dalam skrip Python Anda dan jalankan -

#!/usr/bin/python

import MySQLdb

Jika menghasilkan hasil berikut, berarti modul MySQLdb tidak diinstal -

Traceback (most recent call last):
   File "test.py", line 3, in <module>
      import MySQLdb
ImportError: No module named MySQLdb

Untuk menginstal modul MySQLdb, gunakan perintah berikut -

For Ubuntu, use the following command -
$ sudo apt-get install python-pip python-dev libmysqlclient-dev
For Fedora, use the following command -
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
For Python command prompt, use the following command -
pip install MySQL-python

Note - Pastikan Anda memiliki hak root untuk menginstal modul di atas.

Koneksi Database

Sebelum menghubungkan ke database MySQL, pastikan hal-hal berikut -

• Anda telah membuat TESTDB database.

• Anda telah membuat tabel EMPLOYEE di TESTDB.

• Tabel ini memiliki bidang FIRST_NAME, LAST_NAME, AGE, SEX dan INCOME.

• ID pengguna "testuser" dan kata sandi "test123" disetel untuk mengakses TESTDB.

• Modul Python MySQLdb diinstal dengan benar di komputer Anda.

• Anda telah melalui tutorial MySQL untuk memahami Dasar-Dasar MySQL.

Contoh

Berikut adalah contoh koneksi dengan database MySQL "TESTDB"

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print "Database version : %s " % data

# disconnect from server
db.close()

Saat menjalankan skrip ini, ia menghasilkan hasil berikut di mesin Linux saya.

Database version : 5.0.45

Jika koneksi dibuat dengan sumber data, maka Objek Koneksi dikembalikan dan disimpan ke db untuk penggunaan lebih lanjut, jika tidak dbdisetel ke Tidak Ada. Lanjut,db objek digunakan untuk membuat cursorobjek, yang pada gilirannya digunakan untuk mengeksekusi kueri SQL. Akhirnya, sebelum keluar, ini memastikan bahwa koneksi database ditutup dan sumber daya dilepaskan.

Membuat Tabel Database

Setelah koneksi database dibuat, kita siap membuat tabel atau record ke dalam tabel database menggunakan execute metode kursor yang dibuat.

Contoh

Mari kita buat tabel Database EMPLOYEE -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create table as per requirement
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""

cursor.execute(sql)

# disconnect from server
db.close()

Operasi INSERT

Ini diperlukan ketika Anda ingin membuat rekaman Anda ke dalam tabel database.

Contoh

Contoh berikut, mengeksekusi pernyataan SQL INSERT untuk membuat record ke dalam tabel EMPLOYEE -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Contoh di atas dapat ditulis sebagai berikut untuk membuat kueri SQL secara dinamis -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Contoh

Segmen kode berikut adalah bentuk eksekusi lain di mana Anda dapat meneruskan parameter secara langsung -

..................................
user_id = "test123"
password = "password"

con.execute('insert into Login values("%s", "%s")' % \
             (user_id, password))
..................................

BACA Operasi

Operasi BACA pada database apa pun berarti mengambil beberapa informasi berguna dari database.

Setelah koneksi database kami dibuat, Anda siap untuk membuat kueri ke dalam database ini. Anda bisa menggunakan keduanyafetchone() metode untuk mengambil rekaman tunggal atau fetchall() metode untuk menyesuaikan beberapa nilai dari tabel database.

• fetchone()- Ini mengambil baris berikutnya dari kumpulan hasil kueri. Kumpulan hasil adalah objek yang dikembalikan saat objek kursor digunakan untuk membuat kueri tabel.

• fetchall()- Ini mengambil semua baris dalam set hasil. Jika beberapa baris telah diekstraksi dari set hasil, maka ia mengambil baris yang tersisa dari set hasil.

• rowcount - Ini adalah atribut hanya-baca dan mengembalikan jumlah baris yang dipengaruhi oleh metode execute ().

Contoh

Prosedur berikut menanyakan semua catatan dari tabel KARYAWAN yang memiliki gaji lebih dari 1000 -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # Now print fetched result
      print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fecth data"

# disconnect from server
db.close()

Ini akan menghasilkan hasil sebagai berikut -

fname=Mac, lname=Mohan, age=20, sex=M, income=2000

Perbarui Operasi

UPDATE Operasi pada database apa pun berarti memperbarui satu atau lebih rekaman, yang sudah tersedia dalam database.

Prosedur berikut memperbarui semua catatan yang memiliki SEX sebagai 'M'. Di sini, kami menambah USIA semua pria satu tahun.

Contoh

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

HAPUS Operasi

Operasi HAPUS diperlukan saat Anda ingin menghapus beberapa rekaman dari database Anda. Berikut adalah prosedur untuk menghapus semua record dari KARYAWAN di mana USIA lebih dari 20 -

Contoh

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Melakukan Transaksi

Transaksi adalah mekanisme yang memastikan konsistensi data. Transaksi memiliki empat properti berikut -

• Atomicity - Transaksi selesai atau tidak terjadi sama sekali.

• Consistency - Sebuah transaksi harus dimulai dalam keadaan yang konsisten dan membiarkan sistem dalam keadaan yang konsisten.

• Isolation - Hasil antara transaksi tidak terlihat di luar transaksi saat ini.

• Durability - Setelah transaksi dilakukan, efeknya tetap ada, bahkan setelah kegagalan sistem.

Python DB API 2.0 menyediakan dua metode baik melakukan atau rollback transaksi.

Contoh

Anda sudah tahu cara mengimplementasikan transaksi. Ini lagi contoh serupa -

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

Operasi COMMIT

Komit adalah operasi, yang memberikan sinyal hijau ke database untuk menyelesaikan perubahan, dan setelah operasi ini, tidak ada perubahan yang dapat dikembalikan.

Berikut adalah contoh sederhana untuk menelepon commit metode.

db.commit()

Operasi ROLLBACK

Jika Anda tidak puas dengan satu atau beberapa perubahan dan Anda ingin mengembalikan perubahan itu sepenuhnya, gunakan rollback() metode.

Berikut adalah contoh sederhana untuk menelepon rollback() metode.

db.rollback()

Memutus Database

Untuk memutuskan koneksi Database, gunakan metode close ().

db.close()

Jika koneksi ke database ditutup oleh pengguna dengan metode close (), setiap transaksi yang belum diselesaikan akan dibatalkan oleh DB. Namun, daripada bergantung pada detail implementasi tingkat bawah DB, aplikasi Anda akan lebih baik memanggil commit atau rollback secara eksplisit.

Penanganan Error

Ada banyak sumber kesalahan. Beberapa contoh adalah kesalahan sintaks dalam pernyataan SQL yang dieksekusi, kegagalan koneksi, atau memanggil metode pengambilan untuk pegangan pernyataan yang sudah dibatalkan atau selesai.

DB API mendefinisikan sejumlah kesalahan yang harus ada di setiap modul database. Tabel berikut mencantumkan pengecualian ini.

Sr.No.	Pengecualian & Deskripsi
1	Warning Digunakan untuk masalah non-fatal. Harus membuat subclass StandardError.
2	Error Kelas dasar untuk kesalahan. Harus membuat subclass StandardError.
3	InterfaceError Digunakan untuk kesalahan dalam modul database, bukan database itu sendiri. Harus subclass Error.
4	DatabaseError Digunakan untuk kesalahan dalam database. Harus subclass Error.
5	DataError Subclass DatabaseError yang mengacu pada kesalahan dalam data.
6	OperationalError Subclass DatabaseError yang merujuk ke kesalahan seperti hilangnya koneksi ke database. Kesalahan ini umumnya di luar kendali skrip Python.
7	IntegrityError Subclass DatabaseError untuk situasi yang akan merusak integritas relasional, seperti batasan keunikan atau kunci asing.
8	InternalError Subkelas DatabaseError yang merujuk ke kesalahan internal modul database, seperti kursor tidak lagi aktif.
9	ProgrammingError Subkelas DatabaseError yang merujuk ke kesalahan seperti nama tabel yang buruk dan hal-hal lain yang dapat disalahkan dengan aman kepada Anda.
10	NotSupportedError Subkelas DatabaseError yang merujuk pada mencoba memanggil fungsionalitas yang tidak didukung.

Skrip Python Anda harus menangani kesalahan ini, tetapi sebelum menggunakan salah satu pengecualian di atas, pastikan MySQLdb Anda memiliki dukungan untuk pengecualian itu. Anda bisa mendapatkan lebih banyak informasi tentang mereka dengan membaca spesifikasi DB API 2.0.