Все приложения взаимодействуют с данными, чаще всего через
жүктеу/скачать 105,52 Kb.
|
Как подружить Python и базы данных SQL (1)
| Примечание. Описываемый далее скрипт – часть того же файла, в котором мы описали соединение с базой данных SQLite.
Итак, начнем с определения функции execute_query(): def execute_query(connection, query): cursor = connection.cursor() try: cursor.execute(query) connection.commit() print("Query executed successfully") except Error as e: print(f"The error '{e}' occurred") Теперь напишем передаваемый запрос (query): create_users_table = """ CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER, gender TEXT, nationality TEXT ); """ В запросе говорится, что нужно создать таблицу users со следующими пятью столбцами: id name age gender nationality Наконец, чтобы появилась таблица, вызываем execute_query(). Передаём объект connection, который мы описали в предыдущем разделе, вместе с только что подготовленной строкой запроса create_users_table: execute_query(connection, create_users_table) Следующий запрос используется для создания таблицы posts: create_posts_table = """ CREATE TABLE IF NOT EXISTS posts( id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, description TEXT NOT NULL, user_id INTEGER NOT NULL, FOREIGN KEY (user_id) REFERENCES users (id) ); """ Поскольку между users и posts имеет место отношение один-ко-многим, в таблице появляется ключ user_id, который ссылается на столбец id в таблице users. Выполняем следующий скрипт для построения таблицы posts: execute_query(connection, create_posts_table) Наконец, формируем следующим скриптом таблицы comments и likes: create_comments_table = """ CREATE TABLE IF NOT EXISTS comments ( id INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT NOT NULL, user_id INTEGER NOT NULL, post_id INTEGER NOT NULL, FOREIGN KEY (user_id) REFERENCES users (id) FOREIGN KEY (post_id) REFERENCES posts (id) ); """ create_likes_table = """ CREATE TABLE IF NOT EXISTS likes ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, post_id integer NOT NULL, FOREIGN KEY (user_id) REFERENCES users (id) FOREIGN KEY (post_id) REFERENCES posts (id) ); """ execute_query(connection, create_comments_table) execute_query(connection, create_likes_table) Вы могли заметить, что создание таблиц в SQLite очень похоже на использование чистого SQL. Все, что вам нужно сделать, это сохранить запрос в строковой переменной и затем передать эту переменную cursor.execute(). MySQL Так же, как с SQLite, чтобы создать таблицу в MySQL, нужно передать запрос в cursor.execute(). Создадим новый вариант функции execute_query(): def execute_query(connection, query): cursor = connection.cursor() try: cursor.execute(query) connection.commit() print("Query executed successfully") except Error as e: print(f"The error '{e}' occurred") Описываем таблицу users: create_users_table = """ CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT, name TEXT NOT NULL, age INT, gender TEXT, nationality TEXT, PRIMARY KEY (id) ) ENGINE = InnoDB """ execute_query(connection, create_users_table) Запрос для реализации отношения внешнего ключа в MySQL немного отличается от SQLite. Более того, MySQL использует ключевое слово AUTO_INCREMENT для указания столбцов, значения которых автоматически увеличиваются при вставке новых записей. Следующий скрипт составит таблицу posts, содержащую внешний ключ user_id, который ссылается на id столбца таблицы users:
Аналогично для создания таблиц comments и likes, передаём соответствующие CREATE-запросы функции execute_query(). PostgreSQL Применение библиотеки psycopg2 в execute_query() также подразумевает работу с cursor: def execute_query(connection, query): connection.autocommit = True cursor = connection.cursor() try: cursor.execute(query) print("Query executed successfully") except OperationalError as e: print(f"The error '{e}' occurred") Мы можем использовать эту функцию для организации таблиц, вставки, изменения и удаления записей в вашей базе данных PostgreSQL. Создадим внутри базы данных sm_app таблицу users:
Запрос на создание таблицы users в PostgreSQL немного отличается от SQLite и MySQL. Здесь для указания столбцов с автоматическим инкрементом используется ключевое слово SERIAL. Кроме того, отличается способ указания ссылок на внешние ключи: create_posts_table = """ CREATE TABLE IF NOT EXISTS posts ( id SERIAL PRIMARY KEY, title TEXT NOT NULL, description TEXT NOT NULL, user_id INTEGER REFERENCES users(id) ) """ execute_query(connection, create_posts_table) 4. Вставка записей В предыдущем разделе мы разобрали, как развертывать таблицы в базах данных SQLite, MySQL и PostgreSQL с использованием различных модулей Python. В этом разделе мы узанем, как вставлять записи. SQLite
Чтобы вставить записи в базу данных SQLite, мы можем использовать ту же execute_query() функцию, что и для создания таблиц. Для этого сначала нужно сохранить в виде строки запрос INSERT INTO. Затем нужно передать объект connection и строковый запрос в execute_query(). Вставим для примера пять записей в таблицу users: create_users = """ INSERT INTO users (name, age, gender, nationality) VALUES ('James', 25, 'male', 'USA'), ('Leila', 32, 'female', 'France'), ('Brigitte', 35, 'female', 'England'), ('Mike', 40, 'male', 'Denmark'), ('Elizabeth', 21, 'female', 'Canada'); """ execute_query(connection, create_users) Поскольку мы установили автоинкремент для столбца id, нам не нужно указывать его дополнительно. Таблица users будет автоматически заполнена пятью записями со значениями id от 1 до 5. Вставим в таблицу posts шесть записей:
Важно отметить, что столбец user_id таблицы posts является внешним ключом, который ссылается на столбец таблицы users. Это означает, что столбец user_id должен содержать значение, которое уже существует в столбце id таблицы users. Если его не существует, мы получим сообщение об ошибке. Следующий скрипт вставляет записи в таблицы comments и likes:
MySQL
Есть два способа вставить записи в базы данных MySQL из приложения Python. Первый подход похож на SQLite. Можно сохранить запрос INSERT INTO в строке, а затем использовать для вставки записей cursor.execute(). Ранее мы определили функцию-оболочку execute_query(), которую использовали для вставки записей. Мы можем использовать ту же функцию: create_users = """ INSERT INTO `users` (`name`, `age`, `gender`, `nationality`) VALUES ('James', 25, 'male', 'USA'), ('Leila', 32, 'female', 'France'), ('Brigitte', 35, 'female', 'England'), ('Mike', 40, 'male', 'Denmark'), ('Elizabeth', 21, 'female', 'Canada'); """ execute_query(connection, create_users) Второй подход использует метод cursor.executemany(), который принимает два параметра: Строка query, содержащая заполнители для вставляемых записей. Список записей, которые мы хотим вставить. Посмотрите на следующий пример, который вставляет две записи в таблицу likes: sql = "INSERT INTO likes ( user_id, post_id ) VALUES ( %s, %s )" val = [(4, 5), (3, 4)] cursor = connection.cursor() cursor.executemany(sql, val) connection.commit() Какой подход выбрать – зависит от вас. Если вы не очень хорошо знакомы с SQL, проще использовать метод курсора executemany(). PostgreSQL В предыдущем подразделе мы познакомились с двумя подходами для вставки записей в таблицы баз данных MySQL. В psycopg2 используется второй подход: мы передаем SQL-запрос с заполнителями и списком записей методу execute(). Каждая запись в списке должна являться кортежем, значения которого соответствуют значениям столбца в таблице БД. Вот как мы можем вставить пользовательские записи в таблицу users: users = [ ("James", 25, "male", "USA"), ("Leila", 32, "female", "France"), ("Brigitte", 35, "female", "England"), ("Mike", 40, "male", "Denmark"), ("Elizabeth", 21, "female", "Canada"), ] user_records = ", ".join(["%s"] * len(users)) insert_query = ( f"INSERT INTO users (name, age, gender, nationality) VALUES {user_records}" ) connection.autocommit = True cursor = connection.cursor() cursor.execute(insert_query, users) Список users содержит пять пользовательских записей в виде кортежей. Затем мы создаём строку с пятью элементами-заполнителями (%s), соответствующими пяти пользовательским записям. Строка-заполнитель объединяется с запросом, который вставляет записи в таблицу users. Наконец, строка запроса и пользовательские записи передаются в метод execute(). Следующий скрипт вставляет записи в таблицу posts:
По той же методике можно вставить записи в таблицы comments и likes. 5. Извлечение данных из записей SQLite
Чтобы выбрать записи в SQLite, можно снова использовать cursor.execute(). Однако после этого потребуется вызвать метод курсора fetchall(). Этот метод возвращает список кортежей, где каждый кортеж сопоставлен с соответствующей строкой в извлеченных записях. Чтобы упростить процесс, напишем функцию execute_read_query(): def execute_read_query(connection, query): cursor = connection.cursor() result = None try: cursor.execute(query) result = cursor.fetchall() return result except Error as e: print(f"The error '{e}' occurred") Эта функция принимает объект connection и SELECT-запрос, а возвращает выбранную запись. SELECT Давайте выберем все записи из таблицы users: select_users = "SELECT * from users" users = execute_read_query(connection, select_users) for user in users: print(user) В приведенном выше скрипте запрос SELECT забирает всех пользователей из таблицы users. Результат передается в написанную нами функцию execute_read_query(), возвращающую все записи из таблицы users. жүктеу/скачать 105,52 Kb. Достарыңызбен бөлісу:
|