Python dasturlash tilida ma’lumotlar bazasi bilan ishlash

PyMySQL-ni qanday o'rnataman?
Davom etishdan oldin, kompyuteringizda PyMySQL o'rnatilganligiga ishonch hosil qilasiz. 
#!/usr/bin/python3
import pymysql
Agar u quyidagi natijani keltiradigan bo'lsa, demak MySQLdb moduli o'rnatilmagan -
Traceback (most recent call last):
File "test.py", line 3, in
Import pymysql
ImportError: No module named pymysql
Oxirgi barqaror versiya PyPI-da mavjud va uni pip bilan o'rnatish mumkin -
pip install pymysql
Shu bilan bir qatorda (masalan, pip bo'lmasa), tarballni GitHub- dan yuklab olish va Setuptools bilan quyidagi tarzda o'rnatish mumkin -
$ # X.X is the desired pymysql version (e.g. 0.5 or 0.6).
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # The folder PyMySQL* can be safely removed now.

Eslatma - Yuqoridagi modulni o'rnatish uchun root huquqiga ega ekanligingizga ishonch hosil qiling.

MySQL ma'lumotlar bazasiga ulanishdan oldin quyidagi bandlarga ;

• 
  Siz TESTDB ma'lumotlar bazasini yaratdingiz.
  
• 
  Siz TESTDB-da EMPLOYEE jadvalini yaratdingiz.
  

Ushbu jadval FIRST_NAME, LAST_NAME, AGE, SEX va INCOME maydonlariga ega.
TESTDB-ga kirish uchun "testuser" foydalanuvchi identifikatori va "test123" paroli o'rnatilgan.
Python moduli PyMySQL sizning kompyuteringizga to'g'ri o'rnatilgan.
Siz MySQL asoslarini tushunish uchun MySQL qo'llanmasidan o'tdingiz .
Misol

Quyida MySQL ma'lumotlar bazasi bilan bog'lanishning misoli keltirilgan "TESTDB" -

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.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()
Agar ma'lumotlar manbai bilan aloqa o'rnatilsa, u holda Connection Object qaytariladi va undan keyingi foydalanish uchun db-ga saqlanadi , aks holda db None-ga o'rnatiladi. Keyingi, db ob'ekti kursor ob'ekti yaratish uchun ishlatiladi , bu esa o'z navbatida SQL so'rovlarini bajarish uchun ishlatiladi. Va nihoyat, chiqishdan oldin, ma'lumotlar bazasi ulanishining yopilishi va resurslarning chiqarilishini ta'minlaydi.
Ma'lumotlar bazasi aloqasi o'rnatilgandan so'ng, biz yaratilgan kursorning bajarish usuli yordamida ma'lumotlar bazasi jadvallariga jadvallar yoki yozuvlar yaratishga tayyormiz .

Misol
EMPLOYEE ma'lumotlar bazasi jadvalini yarataylik -

#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.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()
Ma'lumotlar bazasi jadvalida yozuvlaringizni yaratmoqchi bo'lganingizda INSERT operatsiyasi talab qilinadi.
Misol
Quyidagi misol, EMPLOYEE jadvalida yozuv yaratish uchun SQL INSERT operatorini bajaradi -
#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.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()