Пособие по MySQL на Python | MySQL

Character set

The character set defines how characters are encoded. In the early days of the Internet, the ASCII character set was often used. ASCII uses seven bits for each character, which is space efficient but it means there are only 128 different characters available.

The locale-specific character sets help support all languages, but with the downside that different encodings are required for different languages. One response to that is the Unicode Transformation Format (UTF) encodings; UTF-8 in particular has become popular.

Until and including MySQL Server 5.7, the default character set for the server side was Latin1, but this changed in MySQL 8.0 when utf8mb4 became the default. The mb4 suffix indicates that up to four bytes are used for each character (mb = multi-byte).

The reason this is required is that utf8 in MySQL previously has meant up to three bytes per character is supported per character. However, a three-byte UTF-8 implementation misses out on several emojis and it has been deprecated, so it is better to use the four-byte variant.

The default character set for Connector/Python until version 8.0.12 is utf8, which is a three-byte implementation of UTF-8 (called utf8 or utf8mb3 in MySQL Server). Starting from version 8.0.12 the default is utf8mb4 as in MySQL Server.

There is also the concept of collation to consider. The collation defines how to compare two characters or character sequences with each other, such as whether ä and a should be considered the same character in comparisons and whether ss is considered equal to ß (German sharp s).

Tip

Unless you have specific country requirements, the default collation in MySQL Server is often a good choice when choosing utf8 or utf8mb4 as the character set.

The character sets and collations that are available in MySQL do not, in general, change much between versions. However, one of the major changes for MySQL Server 8.0 is the addition of a range of UCA 9.0.0 collations. Information about the available character sets and their default collation can be found using the CHARACTER_SETS table in the Information Schema, as shown in Listing 7.

mysql> SELECT CHARACTER_SET_NAME AS Name,
              DEFAULT_COLLATE_NAME
         FROM information_schema.CHARACTER_SETS
        ORDER BY CHARACTER_SET_NAME;
 ---------- ---------------------- 
| Name     | DEFAULT_COLLATE_NAME |
 ---------- ---------------------- 
| armscii8 | armscii8_general_ci  |
| ascii    | ascii_general_ci     |
| big5     | big5_chinese_ci      |
| binary   | binary               |
...
| ujis     | ujis_japanese_ci     |
| utf16    | utf16_general_ci     |
| utf16le  | utf16le_general_ci   |
| utf32    | utf32_general_ci     |
| utf8     | utf8_general_ci      |
| utf8mb4  | utf8mb4_0900_ai_ci   |
 ---------- ---------------------- 
41 rows in set (0.00 sec)

Listing 7Character Set Collations in MySQL 8.0.11

Similarly, the collations available for a specific character set can be determined using the COLLATIONS table. Listing 8 shows the output for the utf8mb4 character set.

mysql> SELECT COLLATION_NAME, IS_DEFAULT
         FROM information_schema.COLLATIONS
        WHERE CHARACTER_SET_NAME = 'utf8mb4';
 ---------------------------- ------------ 
| COLLATION_NAME             | IS_DEFAULT |
 ---------------------------- ------------ 
| utf8mb4_general_ci         |            |
| utf8mb4_bin                |            |
| utf8mb4_unicode_ci         |            |
...
| utf8mb4_0900_ai_ci         | Yes        |
| utf8mb4_de_pb_0900_ai_ci   |            |
| utf8mb4_is_0900_ai_ci      |            |
| utf8mb4_lv_0900_ai_ci      |            |
...
| utf8mb4_vi_0900_as_cs      |            |
| utf8mb4_ja_0900_as_cs      |            |
| utf8mb4_ja_0900_as_cs_ks   |            |
| utf8mb4_0900_as_ci         |            |
| utf8mb4_ru_0900_ai_ci      |            |
| utf8mb4_ru_0900_as_cs      |            |
 ---------------------------- ------------ 
73 rows in set (0.00 sec)

Listing 8The Collations Available for the utf8mb4 Character Set

The output shows the 73 collations that are available for utf8mb4 in MySQL Server 8.0.11. The collation names consist of several parts:

  • The character set name
  • Which country the collation is for (for example ja for Japan) or whether it is of a more general nature
  • Modifiers (accents): These are not present for all collations. Examples are ai for accent insensitive, as for accent sensitive, ci for case insensitive, and cs for case sensitive.

Creating tables using the create table statement

Now, to create a new table in MySQL, you need to use the CREATE TABLE statement. The following MySQL query will create the movies table for your online_movie_rating database:

If you’ve looked at SQL statements before, then most of the above query might make sense. But there are some differences in the MySQL syntax that you should be aware of.

For example, MySQL has a wide variety of data types for your perusal, including YEAR, INT, BIGINT, and so on. Also, MySQL uses the AUTO_INCREMENT keyword when a column value has to be incremented automatically on the insertion of new records.

To create a new table, you need to pass this query to cursor.execute(), which accepts a MySQL query and executes the query on the connected MySQL database:

Now you have the movies table in your database. You pass create_movies_table_query to cursor.execute(), which performs the required execution.

Note: The connection variable refers to the MySQLConnection object that was returned when you connected to your database.

Creating, altering, and dropping a table

In this section, you’ll learn how to perform some basic DDL queries like CREATE, DROP, and ALTER with Python. You’ll get a quick look at the MySQL database that you’ll use in the rest of this tutorial.

Defining the database schema

You can start by creating a database schema for an online movie rating system. The database will consist of three tables:

  1. movies contains general information about movies and has the following attributes:
    • id
    • title
    • release_year
    • genre
    • collection_in_mil
  2. reviewers contains information about people who posted reviews or ratings and has the following attributes:
  3. ratings contains information about ratings that have been posted and has the following attributes:
    • movie_id (foreign key)
    • reviewer_id (foreign key)
    • rating

A real-world movie rating system, like IMDb, would need to store a bunch of other attributes, like emails, movie cast lists, and so on. If you want, you can add more tables and attributes to this database. But these three tables will suffice for the purpose of this tutorial.

The image below depicts the database schema:

Schema Diagram for an Online Movie Rating System
Schema Diagram for an Online Movie Rating System

The tables in this database are related to each other. movies and reviewers will have a many-to-many relationship since one movie can be reviewed by multiple reviewers and one reviewer can review multiple movies. The ratings table connects the movies table with the reviewers table.

Defining the database schema

You can start by creating a database schema for an online movie rating system. The database will consist of three tables:

  1. movies contains general information about movies and has the following attributes:
    • id
    • title
    • release_year
    • genre
    • collection_in_mil
  2. reviewers contains information about people who posted reviews or ratings and has the following attributes:
  3. ratings contains information about ratings that have been posted and has the following attributes:
    • movie_id (foreign key)
    • reviewer_id (foreign key)
    • rating

A real-world movie rating system, like IMDb, would need to store a bunch of other attributes, like emails, movie cast lists, and so on. If you want, you can add more tables and attributes to this database. But these three tables will suffice for the purpose of this tutorial.

The image below depicts the database schema:

Schema Diagram for an Online Movie Rating System
Schema Diagram for an Online Movie Rating System

The tables in this database are related to each other. movies and reviewers will have a many-to-many relationship since one movie can be reviewed by multiple reviewers and one reviewer can review multiple movies. The ratings table connects the movies table with the reviewers table.

Enabling and disabling features using client flags

from mysql.connector.constants import ClientFlag
print 'n'.join(ClientFlag.get_full_info())

If client_flags is not specified (that is, it
is zero), defaults are used for MySQL 4.1 and higher. If you
specify an integer greater than 0, make sure
all flags are set properly.

flags = [ClientFlag.FOUND_ROWS, -ClientFlag.LONG_FLAG]
mysql.connector.connect(client_flags=flags)

Modifying a table schema using the alter statement

In the movies table, you have a column called collection_in_mil, which contains a movie’s box office collection in millions of dollars. You can write the following MySQL statement to modify the data type of collection_in_mil attribute from INT to DECIMAL:

DECIMAL(4,1) means a decimal number that can have a maximum of 4 digits, of which 1 is decimal, such as 120.1, 3.4, 38.0, and so on. After executing the ALTER TABLE statement, you can show the updated table schema using DESCRIBE:

>>>

>>> alter_table_query="""... ALTER TABLE movies... MODIFY COLUMN collection_in_mil DECIMAL(4,1)... """>>> show_table_query="DESCRIBE movies">>> withconnection.cursor()ascursor:... cursor.execute(alter_table_query)... cursor.execute(show_table_query)... # Fetch rows from last executed query... result=cursor.fetchall()... print("Movie Table Schema after alteration:")... forrowinresult:... print(row)...Movie Table Schema after alteration('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment')('title', 'varchar(100)', 'YES', '', None, '')('release_year', 'year(4)', 'YES', '', None, '')('genre', 'varchar(100)', 'YES', '', None, '')('collection_in_mil', 'decimal(4,1)', 'YES', '', None, '')

As shown in the output, the collection_in_mil attribute is now of type DECIMAL(4,1). Also note that in the code above, you call cursor.execute() twice. But cursor.fetchall() fetches rows from only the last executed query, which is the show_table_query.

Reading records using the select statement

To retrieve records, you need to send a SELECT query to cursor.execute(). Then you use cursor.fetchall() to extract the retrieved table in the form of a list of rows or records.

Похожее:  Гигалинк Личный Кабинет Вход — Официальный сайт

Try writing a MySQL query to select all records from the movies table and send it to .execute():

>>>

>>> select_movies_query="SELECT * FROM movies LIMIT 5">>> withconnection.cursor()ascursor:... cursor.execute(select_movies_query)... result=cursor.fetchall()... forrowinresult:... print(row)...(1, 'Forrest Gump', 1994, 'Drama', Decimal('330.2'))(2, '3 Idiots', 2009, 'Drama', Decimal('2.4'))(3, 'Eternal Sunshine of the Spotless Mind', 2004, 'Drama', Decimal('34.5'))(4, 'Good Will Hunting', 1997, 'Drama', Decimal('138.1'))(5, 'Skyfall', 2022, 'Action', Decimal('304.6'))

The result variable holds the records returned from using .fetchall(). It’s a list of tuples representing individual records from the table.

In the query above, you use the LIMIT clause to constrain the number of rows that are received from the SELECT statement. Developers often use LIMIT to perform pagination when handling large volumes of data.

Showing a table schema using the describe statement

Now, that you’ve created all three tables, you can look at their schema using the following SQL statement:

To get some results back from the cursor object, you need to use cursor.fetchall(). This method fetches all rows from the last executed statement. Assuming you already have the MySQLConnection object in the connection variable, you can print out all the results fetched by cursor.fetchall():

>>>

>>> show_table_query="DESCRIBE movies">>> withconnection.cursor()ascursor:... cursor.execute(show_table_query)... # Fetch rows from last executed query... result=cursor.fetchall()... forrowinresult:... print(row)...('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment')('title', 'varchar(100)', 'YES', '', None, '')('release_year', 'year(4)', 'YES', '', None, '')('genre', 'varchar(100)', 'YES', '', None, '')('collection_in_mil', 'int(11)', 'YES', '', None, '')

Once you execute the above code, you should receive a table containing information about all the columns in movies table. For each column, you’ll receive details like the column’s data type, whether the column is a primary key, and so on.

Syntax

There are several ways to create the connection. Four of them are

  • The mysql.connector.connect() function: This is the most flexible connection method. It provides a uniform way of creating connections using the C Extension or enabling the connection pooling and the failover-related options. This function works as a wrapper that returns an object of the appropriate class depending on the settings.
  • The MySQLConnection() constructor
  • The MySQLConnection.connect() method: It requires first instantiating the MySQLConnection class without arguments and then creating the connection.
  • The same as before using the MySQLConnection.connect() method, but with the difference that the MySQLConnection.config() method is called explicitly to configure the connection.

The MySQLConnection class is the pure Python implementation. Alternatively, the CMySQLConnection class can be used, which provides implementation of the C Extension backend to the Python Database API.

All of the methods end up with the same connection object, and they all take the connections options as keyword arguments. This means that you can choose whatever way to create the connection that works best for the program. However, since the mysql.connector.connect() function is the most powerful, it is the preferred way to connect because it makes it easier to switch between the pure Python and C Extension implementations or to enable connection pooling or failover.

Tip

Creating a connection using the mysql.connector.connect() function gives access to all connection-related features.

Figure 1 shows the basic flow of using the four ways to create a connection. The red (dark grey) boxes are called directly from the application code, and the yellow (light grey) boxes are called by the last method called indirectly. The figure uses the MySQLConnection class; however, the same applies if the CMySQLConnection class is used.

 Figure 1The flow of creating a connection

The leftmost route is the one using the mysql.connector.connect() function. The Python program calls the function with the connection arguments, and the function then handles the rest. The figure assumes a MySQLConnection connection (using the pure Python implementation) is created, but the function can also return a CMySQLConnection object if the C Extension is used. The basic syntax for the mysql.connector.connect() function is

db = mysql.connector.connect(**kwargs)

The route second from the left has the Python program send the connections arguments to the constructor when instantiating the MySQLConnection class. This triggers the constructor to call the connect() method, which in turn calls the config() method. The syntax when using the MySQLConnection class is

db = mysql.connector.MySQLConnection(**kwargs)

In the third route from the left, the MySQLConnection class is first instantiated and then the connect() method is explicitly invoked. The code syntax becomes

db = mysql.connector.MySQLConnection()
db.connect(**kwargs)

Finally, in the rightmost route, all steps are done explicitly. Notice that the order of calling the connect() and config() methods becomes reversed in this case compared with the three other ways of creating the connection.

db = mysql.connector.MySQLConnection()
db.config(**kwargs)
db.connect()

Before creating some real connections, it is necessary to take a look at the most common options used when creating a connection.

Using .execute()

The first approach uses the same cursor.execute() method that you’ve been using until now. You write the INSERT INTO query in a string and pass it to cursor.execute(). You can use this method to insert data into the movies table.

For reference, the movies table has five attributes:

  1. id
  2. title
  3. release_year
  4. genre
  5. collection_in_mil

You don’t need to add data for id as the AUTO_INCREMENT automatically calculates id for you. The following script inserts records into the movies table:

insert_movies_query="""INSERT INTO movies (title, release_year, genre, collection_in_mil)VALUES    ("Forrest Gump", 1994, "Drama", 330.2),    ("3 Idiots", 2009, "Drama", 2.4),    ("Eternal Sunshine of the Spotless Mind", 2004, "Drama", 34.5),    ("Good Will Hunting", 1997, "Drama", 138.1),    ("Skyfall", 2022, "Action", 304.6),    ("Gladiator", 2000, "Action", 188.7),    ("Black", 2005, "Drama", 3.0),    ("Titanic", 1997, "Romance", 659.2),    ("The Shawshank Redemption", 1994, "Drama",28.4),    ("Udaan", 2022, "Drama", 1.5),    ("Home Alone", 1990, "Comedy", 286.9),    ("Casablanca", 1942, "Romance", 1.0),    ("Avengers: Endgame", 2022, "Action", 858.8),    ("Night of the Living Dead", 1968, "Horror", 2.5),    ("The Godfather", 1972, "Crime", 135.6),    ("Haider", 2022, "Action", 4.2),    ("Inception", 2022, "Adventure", 293.7),    ("Evil", 2003, "Horror", 1.3),    ("Toy Story 4", 2022, "Animation", 434.9),    ("Air Force One", 1997, "Drama", 138.1),    ("The Dark Knight", 2008, "Action",535.4),    ("Bhaag Milkha Bhaag", 2022, "Sport", 4.1),    ("The Lion King", 1994, "Animation", 423.6),    ("Pulp Fiction", 1994, "Crime", 108.8),    ("Kai Po Che", 2022, "Sport", 6.0),    ("Beasts of No Nation", 2022, "War", 1.4),    ("Andadhun", 2022, "Thriller", 2.9),    ("The Silence of the Lambs", 1991, "Crime", 68.2),    ("Deadpool", 2022, "Action", 363.6),    ("Drishyam", 2022, "Mystery", 3.0)"""withconnection.cursor()ascursor:cursor.execute(insert_movies_query)connection.commit()

Using .executemany()

The previous approach is more suitable when the number of records is fairly small and you can write these records directly into the code. But this is rarely true. You’ll often have this data stored in some other file, or the data will be generated by a different script and will need to be added to the MySQL database.

This is where .executemany() comes in handy. It accepts two parameters:

  1. A query that contains placeholders for the records that need to be inserted
  2. A list that contains all records that you wish to insert

The following example inserts records for the reviewers table:

insert_reviewers_query="""INSERT INTO reviewers(first_name, last_name)VALUES ( %s, %s )"""reviewers_records=[("Chaitanya","Baweja"),("Mary","Cooper"),("John","Wayne"),("Thomas","Stoneman"),("Penny","Hofstadter"),("Mitchell","Marsh"),("Wyatt","Skaggs"),("Andre","Veiga"),("Sheldon","Cooper"),("Kimbra","Masters"),("Kat","Dennings"),("Bruce","Wayne"),("Domingo","Cortes"),("Rajesh","Koothrappali"),("Ben","Glocker"),("Mahinder","Dhoni"),("Akbar","Khan"),("Howard","Wolowitz"),("Pinkie","Petit"),("Gurkaran","Singh"),("Amy","Farah Fowler"),("Marlon","Crafford"),]withconnection.cursor()ascursor:cursor.executemany(insert_reviewers_query,reviewers_records)connection.commit()

Пособие по mysql на python

Это руководство поможет вам начать работу с MySQL на Python. Вы узнаете об особенностях MySQL на Python и как установить MySQL Connector / Python на вашей локальной системе.

Для доступа к базе данных MySQL из Python, вам нужен драйвер базы данных. MySQL Connector / Python является стандартизированным драйвером базы данных, предоставляемым MySQL.

MySQL Connector / Python поддерживает почти все функции, предоставляемые MySQL версии 5.7. Он позволяет конвертировать значения параметров между Python и MySQL, например, Python DateTime и MySQL DATETIME.

MySQL Connector / Python разработан специально для MySQL. Он поддерживает все расширения MySQL для стандартного SQL, такие как условие LIMIT.

MySQL Connector / Python позволяет сжимать поток данных между Python и сервером базы данных MySQL с использованием сжатия протоколов. Он поддерживает соединения с использованием сокета TCP / IP и безопасные TCP / IP соединения, использующие SSL.

MySQL Connector / Python представляет собой API, реализованнный с помощью чистого Python. Это означает, что вам не нужно устанавливать какую-либо клиентскую библиотеку MySQL или модули Python, кроме стандартной библиотеки.

В этом пособии мы будем рассматривать MySQL / Python 2.0, который поддерживает Python версий 2.6, 2.7 и 3.3.

Похожее:  OAuth на практике. Аутентификация и авторизация пользователей сайта через популярные социалки / Хабр

Для работы с коннектором MySQL Python вам необходимо скачать и установить его в вашей локальной системе. Доступны версии для различных платформ: Mac OS X, Microsoft Windows, Ubuntu Linux и т.д. Вам просто нужно выбрать нужную платформу и запустить скачивание.

Процесс установки коннектора MySQL Python довольно прост. Например, чтобы установить его в среде Windows, нужно выполнить следующие действия:

  • Открыть окно консоли и переключиться на папку, в которую вы распаковали коннектор:
  • В папке C: Temp использовать следующую команду:

После установки коннектора MySQL Python вы должны проверить его, чтобы убедиться, что он работает правильно, и вы можете подключаться к серверу базы данных MySQL без каких-либо проблем. Для проверки правильности установки выполните следующие действия:

Если на экране появится приведенный ниже текст, значит, вы успешно установили коннектор MySQL Python на вашей системе:

Давайте перейдем к следующему разделу, чтобы узнать, как подключаться к базе данных MySQL из Python.

В этом разделе вы узнаете о различных способах подключения к базам данных MySQL из Python с использованием MySQL Connector / Python API.

Во-первых, для этого пособия мы создаем новую базу данных с именем python_mysql. Чтобы создать новую базу данных, вы можете запустить MySQL Workbench или клиентский инструмент MySQL и использовать оператор CREATE DATABASE следующим образом:

Во-вторых, вам нужно загрузить данные в базу данных python_mysql.sql из файла python_mysql.sql.

Пример базы данных Python MySQL

Давайте рассмотрим следующий модуль Python (python_mysql_connect1.py):

Давайте рассмотрим этот модуль в деталях:

  • Во-первых, мы импортируем объекты mysql.connector и Error из пакета MySQL Connector / Python;
  • Во-вторых, для подключения к базе данных MySQL мы используем функцию connect(), которая принимает следующие параметры: хост, база данных, пользователь и пароль. Функция connect() устанавливает соединение с базой данных python_mysql и возвращает объект MySQLConnection;
  • В-третьих, мы проверяем, было ли успешно установлено соединение с базой данных MySQL с помощью метода is_connected(). В случае возникновения исключений, например, если сервер базы данных не доступен, база данных не существует, имя пользователя или пароль неверны и т.д., Python вызовет исключение Error. Мы обрабатываем это исключение, используя блок try except;
  • В-четвертых, если не произошло исключение, мы закрываем соединение с базой данных, вызвав метод Close() объекта MySQLConnection.

Для тестирования модуля python_mysql_connect1.py, используется следующая команда:

В этом примере, мы жестко задали настройки базы данных, такие как localhost, python_mysql,root, что нельзя назвать хорошей практикой. Поэтому давайте исправим это.

В этом примере мы создадим конфигурационный файл базы данных с именем config.ini и определим раздел с четырьмя параметрами следующим образом:

Мы можем создать новый модуль с именем python_mysql_dbconfig.py, который считывает конфигурацию базы данных из файла config.ini и возвращает словарь следующим образом:

Обратите внимание, что мы использовали пакет ConfigureParser, чтобы считать файл конфигурации.

Давайте проверим этот модуль в REPL:

Он работает, как ожидалось.

Теперь мы можем создать новый модуль python_mysql_connect2.py, который использует объект MySQLConnection для подключения к базе данных python_mysql:

Давайте рассмотрим приведенный выше код более подробно:

Когда мы запускаем python_mysql_connect2 в окне консоли, мы получаем следующий результат:

В этом разделе мы рассмотрели, как подключаться к базам данных MySQL с помощью функцию connect() и объекта MySQLConnection. Оба способа дают тот же результат – устанавливают соединение с базой данных MySQL и возвращают объект MySQLConnection.

В этом разделе мы покажем, как запрашивать данные из базы данных MySQL в Python с использованием MySQL Connector / Python API, таких как fetchone(), fetchmany() и fetchall().

Для запроса данных из базы данных MySQL из Python вам нужно сделать следующее:

Мы расскажем, как использовать методы fetchone(), fetchmany() и fetchall() более подробно в следующих разделах.

Метод fetchone() возвращает следующую строку набора результатов запроса или None в случае, если строк не осталось. Давайте посмотрим на следующий код:

Давайте рассмотрим его более подробно:

  • Во-первых, мы подключаемся к базе данных, создав новый объект MySQLConnection;
  • Во-вторых, из объекта MySQLConnection мы устанавливаем новый объект MySQLCursor;
  • В-третьих, мы выполняем запрос, который выбирает все строки из таблицы books;
  • В-четвертых, мы вызываем метод fetchone(), чтобы выбрать следующую строку из набора результатов. В блоке while loop мы выводим содержимое строки и переходим к следующей строке, пока все строки не будут выбраны;
  • В-пятых, мы закрываем курсор и объект подключения через вызов метода close() соответствующего объекта.

В том случае, если число строк в таблице мало, вы можете использовать для извлечения всех строк из таблицы базы данных метод fetchall(). Рассмотрим следующий код:

Логика тут почти та же, что и в примере с использованием метода fetchone(), за исключением вызова метода fetchall(). Так как мы выбрали в память все строки из таблицы books, мы можем получить общее количество возвращаемых строк с помощью свойства rowcount объекта курсора.

Для сравнительно больших таблиц извлечение всех строк и возвращение набора результатов может занять значительное время. Кроме того, для fetchall() необходимо выделение достаточного объема памяти для хранения всего набора результатов. Это не слишком эффективно.

MySQL Connector / Python предоставляет нам метод fetchmany(), который возвращает следующее количество строк (n) набора результатов, что позволяет нам эффективно использовать объем памяти за оптимальное время. Давайте рассмотрим, как используется метод fetchmany().

Во-первых, мы разрабатываем генератор, который разбивает вызовы базы данных на серию вызовов fetchmany() следующим образом:

Во-вторых, мы можем использовать генератор iter_row() для извлечения 10 строк за раз, как это показано ниже:

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

В этом разделе мы расскажем, как вставлять данные в таблицы MySQL с использованием MySQL Connector / Python API.

Чтобы вставить новые строки в таблицу MySQL необходимо выполнить следующие действия:

MySQL Connector / Python предоставляет API, который позволяет вставить за один раз одну или несколько строк в таблицу. Давайте рассмотрим каждый метод более подробно.

Следующий код вставляет новую книгу в таблицу books:

В приведенном выше коде мы:

  • Во-первых, импортируем объекты MySQLConnection и Error из пакета MySQL Connector / Python и функцию read_db_config() из модуля python_mysql_dbconfig;
  • Во-вторых, определяем новую функцию под названием insert_book(), которая принимает два аргумента: название и ISBN. Внутри функции insert_book(), мы готовим оператор INSERT (запрос) и данные (аргументы), которые мы будем вставлять в таблицу books. Обратите внимание, что данные, которые мы передаем в функцию, это кортеж;
  • В-третьих, внутри блока try except мы создаем новое подключение, выполняем оператор и утверждаем изменения. Обратите внимание, что вы должны вызвать метод commit() явно для того, чтобы изменения в базу данных были внесены. В случае, если новая строка была вставлена успешно, мы можем получить последний вставленный id столбца АUTO INCREMENT с помощью свойство lastrowid объекта MySQLCursor;
  • В-четвертых, в конце функции insert_book() мы закрываем курсор и соединение с базой данных;
  • В-пятых, в функции main()мы вызываем функцию insert_book() и передаем title и isbn, чтобы вставить новую строку в таблицу books.

Оператор MySQL INSERT позволяет вставить сразу несколько строк с помощью синтаксиса VALUES. Вам просто нужно включить несколько списков значений столбцов. Каждый список заключен в скобки и разделен запятыми. Например, чтобы вставить несколько книг в таблицу books используется следующий оператор:

INSERT INTO books(title,isbn)
VALUES('Harry Potter And The Order Of The Phoenix', '9780439358071'),
       ('Gone with the Wind', '9780446675536'),
       ('Pride and Prejudice (Modern Library Classics)', '9780679783268');
Чтобы вставить несколько строк в таблицу в Python используется метод executemany() объекта MySQLCursor. Смотрите следующий код:
	from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config

def insert_books(books):
    query = "INSERT INTO books(title,isbn) " 
            "VALUES(%s,%s)"

    try:
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.executemany(query, books)

        conn.commit()
    except Error as e:
        print('Error:', e)

    finally:
        cursor.close()
        conn.close()

def main():
    books = [('Harry Potter And The Order Of The Phoenix', '9780439358071'),
             ('Gone with the Wind', '9780446675536'),
             ('Pride and Prejudice (Modern Library Classics)', '9780679783268')]
    insert_books(books)

if __name__ == '__main__':
    main()

Логика в этом примере аналогична логике первого примера. Только вместо вызова метода execute() мы используем метод executemany().

В функции main() мы передаем список кортежей, каждый из которых содержит название и ISBN книги.

Вызвав метод executemany() объекта MySQLCursor, MySQL Connector / Python переводит оператор INSERT в оператор, который содержит несколько списков значений.

В этом разделе мы рассмотрели, как вставить одну или несколько строк в таблицу MySQL в Python.

В этом разделе мы рассмотрим действия, необходимые для обновления данных в таблице MySQL с помощью MySQL Connector / Python API.

Для обновления данных в таблице MySQL в Python, вам нужно выполнить следующие действия:

В следующем примере, мы будем обновлять название книги, указанной через ID книги:

В этом модуле мы использовали функцию read_db_config() из модуля python_mysql_dbconfig, который мы создали в разделе Подключение к базе данных через Python.

Внутри оператора UPDATE мы размещаем два заполнителя (%), один для названия книги, второй – для ID книги. Мы передали оба кортежа оператора UPDATE (query) и (title,id) в метод execute(). Коннектор интерпретирует запрос следующим образом:

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

Давайте проверим наш новый модуль, чтобы убедиться, если он работает.

Похожее:  СтатГрад 2 — справочная информация

Во-первых, мы выбираем книгу с ID 37:

Во-вторых, мы запускаем модуль.

В-третьих, мы выбираем запись книги, снова выполнив оператор SELECT, чтобы увидеть, действительно ли запись изменилась.

Все работает, как ожидалось.

В этом разделе вы рассказали, как обновлять данные с помощью MySQL Connector / Python API.

В этом разделе мы рассмотрим этапы удаления данных из базы данных MySQL с помощью MySQL Python.

Для удаления строк в таблице MySQL через Python вам нужно совершить следующие действия:

В следующем примере показано, как удалить книгу с указанным ID:

Обратите внимание, что мы используем функцию read_db_config() из модуля python_mysql_dbconfig, который мы разработали в предыдущих разделах.

Так как нам нужно удалить из таблицы books конкретную строку, мы должны разместить заполнитель (%) на стороне оператора DELETE.

Когда мы вызываем метод execute(), мы передаем ему и оператор DELETE и кортеж (book_id,). Коннектор интерпретирует оператор DELETE в следующую форму:

Вы должны всегда использовать заполнители внутри любого запроса, который вы передаете в метод execute().
Это помогает нам предотвратить потенциально вредоносные действия.

Перед запуском кода, давайте проверим таблицу books, чтобы просмотреть данные, прежде чем мы удалим запись:

После запуска приведенного выше модуля, мы снова выполняем оператор SELECT. Строка не возвращается. Это означает, что модуль успешно удалил запись.

В этом разделе мы рассмотрели, как удалить данные из таблицы MySQL с использованием MySQL Connector / Python API.

В этом разделе мы покажем, как вызывать в Python хранимые процедуры MySQL с использованием MySQL Connector / Python API.

В этом разделе в качестве демонстрации мы создадим две хранимые процедуры. Первая – для получения всех книг с информацией об авторе из таблиц books и authors:

Хранимая процедура find_all() содержит оператор SELECT с условием JOIN, который извлекает название, ISBN и полное имя автора из таблиц books и authors. Когда мы выполняем хранимую процедуру find_all(), она возвращает следующий результат:

find_by_isbn() принимает два параметра: первый параметр ISBN (параметр IN), второй – заголовок (OUT параметр). Когда вы передаете в хранимую процедуру ISBN, вы получаете название книги, например:

Для вызова хранимой процедуры в Python, вам нужно выполнить следующие действия:

Следующий пример демонстрирует, как вызывать хранимую процедуру find_all()в Python и выводить набор результатов:

В следующем примере показано, как вызвать хранимую процедуру find_by_isbn():

Для хранимой процедуры find_by_isbn() требуются два параметра, следовательно, мы должны передать список (args), который содержит два элемента: первый из них ISBN (1236400967773), а второй . Второй элемент списке аргументов () – это просто заполнитель содержащий параметр p_title.

Метод callproc() возвращает список (result_args), который содержит два элемента, где второй элемент (result_args[1]) содержит значение параметра p_title.

В этом разделе мы рассмотрели, как вызываются хранимые процедуры через Python с использованием метода callproc() объекта MySQLCursor.

В этом разделе мы рассмотрим, как работать в Python с данными MySQL BLOB, а именно примеры обновления и чтения данных BLOB.

В таблице authors содержится столбец с именем photo, в котором хранятся данные типа BLOB. Мы считаем данные из файла изображения и обновим ими столбец photo.

Во-первых, мы разрабатываем функцию с именем read_file(), которая считывает файл и возвращает содержимое файла:

Во-вторых, мы создаем новую функцию под названием update_blob(), которая обновляет фото автора, указанного с помощью author_id.

Давайте подробно рассмотрим этот код:

  • Во-первых, мы вызываем функцию read_file(), которая считывает данные из файла и возвращает их;
  • Во-вторых, мы составляем оператор UPDATE, который обновляет столбец фото автора, указанного с помощью author_id. Переменная args – это кортеж, который содержит данные файла и author_id. Мы передаем эту переменную в метод execute() вместе с query;
  • В-третьих, внутри блока try except мы подключаемся к базе данных, устанавливаем курсор и выполняем запрос с args. Для того чтобы изменения вступили в силу, мы вызываем метод commit() объекта MySQLConnection;
  • В-четвертых, мы закрываем курсора и соединение с базой данных в блоке finally.

Обратите внимание, что мы импортировали объекты MySQLConnection и Error из пакета MySQL Connector / Python и функцию read_db_config() из модуля python_mysql_dbconfig, который мы разработали в предыдущих разделах.

Давайте протестируем функцию update_blob():

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

Внутри основной функции, мы вызываем функцию update_blob() для обновления столбца фото для автора с идентификатором 144. Чтобы проверить результат, мы выбираем данные из таблицы authors:

Все работает, как ожидалось.

В этом примере мы выбираем BLOB-данные из таблицы авторов и записываем их в файл.

Во-первых, мы разрабатываем функцию write_file(), которая записывает двоичные данные в файл следующим образом:

Во-вторых, мы создаем новую функцию под названием read_blob():

Функция read_blob() считывает BLOB-данные из таблицы authors и записывает их в файл, указанный в параметре имени файла.

Этот код действует очень просто:

  • Во-первых, мы составляем оператор SELECT, который извлекает фотографию конкретного автора;
  • Во-вторых, мы получаем конфигурацию базы данных, вызвав функцию read_db_config();
  • В-третьих, внутри блока try except мы подключаемся к базе данных, устанавливаем курсор и выполняем запрос. После того, как мы получили BLOB-данные, мы используем функцию write_file(), чтобы записать их в файл, указанный в имени файла;
  • В-четвертых, в конечном блоке мы закрываем курсор и соединение с базой данных.

Теперь, давайте проверим функцию read_blob():

Если вы откроете папку вывода в проекте и увидите там картинку, это означает, что вы успешно считали BLOB-данные.
В этом разделе, мы рассказали, как обновлять и считывать BLOB-данные в MySQL из Python с использованием MySQL Connector / API.

Filtering results using the where clause

You can filter table records by specific criteria using the WHERE clause. For example, to retrieve all movies with a box office collection greater than $300 million, you could run the following query:

You can also use ORDER BY clause in the last query to sort the results from the highest to the lowest earner:

>>>

>>> select_movies_query="""... SELECT title, collection_in_mil... FROM movies... WHERE collection_in_mil > 300... ORDER BY collection_in_mil DESC... """>>> withconnection.cursor()ascursor:... cursor.execute(select_movies_query)... formovieincursor.fetchall():... print(movie)...('Avengers: Endgame', Decimal('858.8'))('Titanic', Decimal('659.2'))('The Dark Knight', Decimal('535.4'))('Toy Story 4', Decimal('434.9'))('The Lion King', Decimal('423.6'))('Deadpool', Decimal('363.6'))('Forrest Gump', Decimal('330.2'))('Skyfall', Decimal('304.6'))

MySQL offers a plethora of string formatting operations like CONCAT for concatenating strings. Often, websites will show the movie title along with its release year to avoid confusion. To retrieve the titles of the top five grossing movies, concatenated with their release years, you can write the following query:

>>>

>>> select_movies_query="""... SELECT CONCAT(title, " (", release_year, ")"),...       collection_in_mil... FROM movies... ORDER BY collection_in_mil DESC... LIMIT 5... """>>> withconnection.cursor()ascursor:... cursor.execute(select_movies_query)... formovieincursor.fetchall():... print(movie)...('Avengers: Endgame (2022)', Decimal('858.8'))('Titanic (1997)', Decimal('659.2'))('The Dark Knight (2008)', Decimal('535.4'))('Toy Story 4 (2022)', Decimal('434.9'))('The Lion King (1994)', Decimal('423.6'))

If you don’t want to use the LIMIT clause and you don’t need to fetch all the records, then the cursor object has .fetchone() and .fetchmany() methods as well:

Try retrieving the titles of the five highest-grossing movies concatenated with their release years again, but this time use .fetchmany():

>>>

>>> select_movies_query="""... SELECT CONCAT(title, " (", release_year, ")"),...       collection_in_mil... FROM movies... ORDER BY collection_in_mil DESC... """>>> withconnection.cursor()ascursor:... cursor.execute(select_movies_query)... formovieincursor.fetchmany(size=5):... print(movie)... cursor.fetchall()...('Avengers: Endgame (2022)', Decimal('858.8'))('Titanic (1997)', Decimal('659.2'))('The Dark Knight (2008)', Decimal('535.4'))('Toy Story 4 (2022)', Decimal('434.9'))('The Lion King (1994)', Decimal('423.6'))

The output with .fetchmany() is similar to what you received when you used the LIMIT clause. You might have noticed the additional cursor.fetchall() call at the end. You do this to clean all the remaining results that weren’t read by .fetchmany().

It’s necessary to clean all unread results before executing any other statements on the same connection. Otherwise, an InternalError: Unread result found exception will be raised.

Conclusion

In this tutorial, you saw how to use MySQL Connector/Python to integrate a MySQL database with your Python application. You also saw some unique features of a MySQL database that differentiate it from other SQL databases.

Along the way, you learned some programming best practices that are worth considering when it comes to establishing a connection, creating tables, and inserting and updating records in a database application. You also developed a sample MySQL database for an online movie rating system and interacted with it directly from your Python application.

In this tutorial, you learned how to:

  • Connect your Python app with a MySQL database
  • Bring data from a MySQL database into Python for further analysis
  • Execute SQL queries from your Python application
  • Handle exceptions while accessing the database
  • Prevent SQL injection attacks on your application

If you’re interested, Python also has connectors for other DBMSs like MongoDB and PostgreSQL. For more information, check out Python Database Tutorials.

1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (1 оценок, среднее: 4,00 из 5)
Загрузка...

Добавить комментарий

Ваш адрес email не будет опубликован.

Adblock
detector