Все приложения взаимодействуют с данными, чаще всего через
жүктеу/скачать 94,65 Kb.
|
Практика 11 12 13 Как подружить Python и базы данных SQL
| Примечание. Описываемый далее скрипт – часть того же файла, в котором мы описали соединение с базой данных 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: create_posts_table = """ CREATE TABLE IF NOT EXISTS posts ( id INT AUTO_INCREMENT, title TEXT NOT NULL, description TEXT NOT NULL, user_id INTEGER NOT NULL, FOREIGN KEY fk_user_id (user_id) REFERENCES users(id), PRIMARY KEY (id) ) ENGINE = InnoDB """ execute_query(connection, create_posts_table) Аналогично для создания таблиц 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: create_users_table = """ CREATE TABLE IF NOT EXISTS users ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, age INTEGER, gender TEXT, nationality TEXT ) """ execute_query(connection, create_users_table) Запрос на создание таблицы 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 шесть записей: create_posts = """ INSERT INTO posts (title, description, user_id) VALUES ("Happy", "I am feeling very happy today", 1), ("Hot Weather", "The weather is very hot today", 2), ("Help", "I need some help with my work", 2), ("Great News", "I am getting married", 1), ("Interesting Game", "It was a fantastic game of tennis", 5), ("Party", "Anyone up for a late-night party today?", 3); """ execute_query(connection, create_posts) Важно отметить, что столбец user_id таблицы posts является внешним ключом, который ссылается на столбец таблицы users. Это означает, что столбец user_id должен содержать значение, которое уже существует в столбце id таблицы users. Если его не существует, мы получим сообщение об ошибке. Следующий скрипт вставляет записи в таблицы comments и likes: create_comments = """ INSERT INTO comments (text, user_id, post_id) VALUES ('Count me in', 1, 6), ('What sort of help?', 5, 3), ('Congrats buddy', 2, 4), ('I was rooting for Nadal though', 4, 5), ('Help with your thesis?', 2, 3), ('Many congratulations', 5, 4); """ create_likes = """ INSERT INTO likes (user_id, post_id) VALUES (1, 6), (2, 3), (1, 5), (5, 4), (2, 4), (4, 2), (3, 6); """ execute_query(connection, create_comments) execute_query(connection, create_likes) MySQL
Ранее мы определили функцию-оболочку 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: posts = [ ("Happy", "I am feeling very happy today", 1), ("Hot Weather", "The weather is very hot today", 2), ("Help", "I need some help with my work", 2), ("Great News", "I am getting married", 1), ("Interesting Game", "It was a fantastic game of tennis", 5), ("Party", "Anyone up for a late-night party today?", 3), ] post_records = ", ".join(["%s"] * len(posts)) insert_query = ( f"INSERT INTO posts (title, description, user_id) VALUES {post_records}" ) connection.autocommit = True cursor = connection.cursor() cursor.execute(insert_query, 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. жүктеу/скачать 94,65 Kb. Достарыңызбен бөлісу:
|