Операция CRUD в Python с использованием MySQL

В этой статье мы увидим, как выполнять операции CRUD (CREATE, READ, UPDATE и DELETE) в Python с использованием MySQL. Для этого мы будем использовать коннектор Python MySQL. Для MySQL мы использовали код Visual Studio для python.

Прежде чем начать, нам нужно установить коннектор MySQL с помощью команды:

pip install mysql-connector-python-rf

Для выполнения операций CRUD нам нужна база данных и таблица. Сначала мы создадим базу данных.

Мы собираемся создать базу данных сотрудников с именем employee_db и таблицу с именем tblemployee, которая состоит из следующих столбцов:

Column name	Data type	Description
empid	INT	Сохраняет идентификатор сотрудника и имеет автоинкремент, т.е. увеличивается каждый раз, когда добавляется запись
empname	VARCHAR(45)	Сохраняет имя сотрудника
department	VARCHAR(45)	Хранит отдел, к которому принадлежит сотрудник, т.е. бухгалтерию, HR.
salary	INT	Сохраняет зарплату сотрудников.

Содержание

Создание базы данных
Создание таблицы
Вставка данных
Чтение/выбор данных
Обновление данных
Удаление данных

Создание базы данных

После того, как мы подключили сервер MySQL с помощью соединителя, мы создадим объект курсора, а затем передаем команду SQL с помощью функции выполнения.

Синтаксис для создания базы данных:

CREATE DATABASE <DATABASE_NAME>

Python

# Python implementation to create a Database in MySQL

import mysql.connector

# connecting to the mysql server

db = mysql.connector.connect(

host="localhost",

user="root",

passwd="password"

)

# cursor object c

c = db.cursor()

# executing the create database statement

c.execute("CREATE DATABASE employee_db")

# fetching all the databases

c.execute("SHOW DATABASES")

# printing all the databases

for i in c:

print(i)

c = db.cursor()

# finally closing the database connection

db.close()

Выход:

Обратите внимание на employee_db во 2-й строке.

Создание таблицы

Теперь, чтобы создать таблицу, мы используем команду создания таблицы. Рекомендуется всегда хранить первичный ключ, который в этом случае является эмпидным и помогает однозначно идентифицировать сотрудников.

Общий синтаксис для создания таблицы:

CREATE TABLE
(
    column1 column1_data_type,
    column2 column2_data_type,
    column3 column3_data_type...

);

Python3

# Python implementation to create a table in MySQL

import mysql.connector

# connecting to the mysql server

db = mysql.connector.connect(

    host="localhost",

    user="root",

    passwd="password",

    database="employee_db"

)

# cursor object c

c = db.cursor()

# create statement for tblemployee

employeetbl_create = """CREATE TABLE `employee_db`.`tblemployee` (

  `empid` INT NOT NULL AUTO_INCREMENT,

  `empname` VARCHAR(45) NULL,

  `department` VARCHAR(45) NULL,

  `salary` INT NULL,

   PRIMARY KEY (`empid`))"""

c.execute(employeetbl_create)

c = db.cursor()

# fetch tblemployee details in the database

c.execute("desc tblemployee")

# print the table details

for i in c:

    print(i)

# finally closing the database connection

db.close()

Выход:

Распечатываются данные о сотруднике

Вставка данных

Вставка данных в таблицы является важной частью, необходимо убедиться, что нет несоответствия данных, т.е. тип отправляемых данных должен соответствовать типу данных конкретного столбца.

Общий синтаксис для операторов вставки:

INSERT INTO <TABLE_NAME> (column1,column2,column3...) VALUES (data1,data2,data3...);

Мы будем вставлять несколько строк одного типа, однако вы даже можете вставлять по одной строке за раз. После написания оператора вставки мы создадим список или наборы данных строк, которые будут переданы. Это должно быть создано непосредственно перед выполнением запроса.

Поскольку несколько строк будут отправлены вместе, нам нужно использовать функцию executemany() вместо execute().

Python3

# Python implementation to insert data into a table in MySQL

import mysql.connector

# connecting to the mysql server

db = mysql.connector.connect(

    host="localhost",

    user="root",

    passwd="password",

    database="employee_db"

)

# cursor object c

c = db.cursor()

# insert statement for tblemployee

# this statement will enable us to insert multiple rows at once.

employeetbl_insert = """INSERT INTO tblemployee (

   empname,

   department,

   salary)

   VALUES (%s, %s, %s)"""

# we save all the row data to be inserted in a data variable

data = [("Vani", "HR", "100000"),

        ("Krish", "Accounts", "60000"),

        ("Aishwarya", "Sales", "25000"),

        ("Govind", "Marketing", "40000")]

# execte the insert commands for all rows and commit to the database

c.executemany(employeetbl_insert, data)

db.commit()

# finally closing the database connection

db.close()

ПРИМЕЧАНИЕ. Чтобы убедиться, что данные были успешно вставлены, мы посмотрим на таблицу в рабочей среде Microsoft, а затем используем оператор выбора через сам python, т.е. для ПРОЧТЕНИЯ

Данные успешно вставлены

Чтение/выбор данных

Чтение/выбор или выборка данных из базы данных выполняется командой:

SELECT * FROM <TABLE_NAME>

Эта команда извлекает все столбцы/атрибуты из таблицы.

Однако иногда может потребоваться получить только определенные столбцы. Для этого запускаем следующую команду:

SELECT COLUMN1,COLUMN2... FROM <TABLE_NAME>

Давайте посмотрим на реализацию для выбора всех столбцов.

Python3

# Python implementation to fetch data from a table in MySQL

import mysql.connector

# connecting to the mysql server

db = mysql.connector.connect(

    host="localhost",

    user="root",

    passwd="password",

    database="employee_db"

)

# cursor object c

c = db.cursor()

# select statement for tblemployee which retruns all columns

employeetbl_select = """SELECT * FROM tblemployee"""

# execte the select query to fetch all rows

c.execute(employeetbl_select)

# fetch all the data returned by the database

employee_data = c.fetchall()

# print all the data returned by the database

for e in employee_data:

    print(e)

# finally closing the database connection

db.close()

Выход:

Записи сотрудников, полученные из tblemployee

Обновление данных

Обновление данных — это изменение существующих значений в базе данных. Допустим, в наших записях о сотрудниках зарплата сотрудника по имени «Вани» была увеличена на 15%, поэтому зарплата в записях должна стать 115000. Следовательно, мы запустим запрос на обновление, общий синтаксис которого:

UPDATE <TABLE_NAME> SET <COLUMN_NAME> = <VALUE> WHERE <PRIMARY KEY NAME> =<PRIMARY KEY VALUE>

Python3

# Python implementation to update data of a table in MySQL

import mysql.connector

# connecting to the mysql server

db = mysql.connector.connect(

    host="localhost",

    user="root",

    passwd="password",

    database="employee_db"

)

# cursor object c

c = db.cursor()

# update statement for tblemployee

# which modifies the salary of Vani

employeetbl_update = "UPDATE tblemployee\

SET salary = 115000 WHERE empid = 1"

# execte the update query to modify

# the salary of employee with

# employee id = 1 and commit to the database

c.execute(employeetbl_update)

db.commit()

# finally closing the database connection

db.close()

Выход:

Заработная плата сотрудника с идентификатором 1 была обновлена

Удаление данных

Удаление данных из таблиц должно выполняться с особой осторожностью, так как иногда это может привести к потере важных данных. Часто мягкое удаление выполняется там, где есть дополнительный столбец с именем «активный», значения которого равны только 1 или 0. 1 означает, что он присутствует в таблице, а 0 означает, что он удален из отображения, т. е. он все еще может быть восстановлен, но не отображается и не обрабатывается.

Однако в этом руководстве мы будем выполнять обычное или жесткое удаление, общий синтаксис которого:

DELETE FROM <TABLE_NAME> WHERE <PRIMARY KEY NAME> = <PRIMARY KEY VALUE>

Предложение where не обязательно должно быть первичным ключом.

Мы удалим Айшварию(empid=3) из наших записей.

Python3

import mysql.connector

# connecting to the mysql server

db = mysql.connector.connect(

    host="localhost",

    user="root",

    passwd="password",

    database="employee_db"

)

# cursor object c

c = db.cursor()

# delete statement for tblemployee

# which deletes employee Aishwarya having empid 3

employeetbl_delete = "DELETE FROM tblemployee WHERE empid=3"

# execte the delete statement and commit to the database

c.execute(employeetbl_delete)

db.commit()

# finally closing the database connection

db.close()

Выход:

Сотрудник Айшвария с empid=3 удален

Читайте также: Что такое ASCII