Python, SQL и PostgreSQL: примеры использования

5 (100%) 2 vote[s]

SQL (язык структурированных запросов) — это язык, предназначенный для взаимодействия с этими реляционными базами данных. Рассмотрим использование PostgreSQL, но есть много других версий с немного другими функциями. Базы данных используются для упрощения хранения, использования и управления данными веб-приложениями. Особенно полезны реляционные базы данных; другими словами, таблицы.

Использование SQL

При создании базы данных или таблицы важно отметить, какой тип данных будет храниться в данном столбце. Некоторые типы данных SQL:

    • INTEGER.
    • DECIMAL.
    • SERIAL : автоматически увеличивающееся целое число.
    • VARCHAR : переменная длина символов, т.е. строка.
    • TIMESTAMP.
    • BOOLEAN.
    • ENUM : одно из возможных значений дискретного числа.

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

      • NOT NULL: поле должно иметь значение; если поле не имеет значения, запись будет отклонена.
      • UNIQUE : никакие два поля в этом столбце не могут иметь одинаковое значение.
      • PRIMARY KEY : основной способ индексации таблицы.
      • DEFAULT : установить значение по умолчанию для столбца, если не указано другое значение.
      • CHECK: связанные значения; например, значения больше 50.

Чтобы запустить базу данных, необходимо настроить сервер Postgres. Чтобы запустить сервер локально на компьютере, используйте команду psql <database>. Чтобы подключиться к онлайн-серверу, используйте psql <databaseURL>.

После запуска сервера Postgres команды SQL можно вводить непосредственно в терминал. Некоторые другие полезные команды включают в себя:

    • \d : распечатать все различные части текущей базы данных.

Основные операции

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

 CREATE TABLE flights (
       id SERIAL PRIMARY KEY,
       origin VARCHAR NOT NULL,
       destination VARCHAR NOT NULL,
       duration INTEGER NOT NULL
    );

Вставка данных в таблицу:

 INSERT INTO flights
          (origin, destination, duration)
         VALUES ('New York', 'London', 415);
    • Обратите внимание, что здесь нет id поля. Поскольку он idимеет тип SERIAL, он будет увеличиваться и устанавливаться автоматически.
    • Порядок значений в VALUESдолжен соответствовать порядку, указанному ранее в команде.
    • Эта команда также может быть введена все в одну строку.

Чтение данных из таблицы:

SELECT * FROM flights;
SELECT origin, destination FROM flights;
SELECT * FROM flights WHERE id = 3;
SELECT * FROM flights WHERE origin = 'New York';
SELECT * FROM flights WHERE duration > 500;
SELECT * FROM flights WHERE destination = 'Paris' AND duration > 500;
SELECT * FROM flights WHERE destination = 'Paris' OR duration > 500;
SELECT AVG(duration) FROM flights WHERE origin = 'New York';
SELECT * FROM flights WHERE origin LIKE '%a%';
SELECT * FROM flights LIMIT 2;
SELECT * FROM flights ORDER BY duration ASC;
SELECT * FROM flights ORDER BY duration ASC LIMIT 3;
SELECT origin, COUNT(*) FROM flights GROUP BY origin;
SELECT origin, COUNT(*) FROM flights GROUP BY origin HAVING COUNT(*) > 1;
    • Запрос после SELECT показывает, какие столбцы выбираются.
    • Запрос после WHERE указывает ограничения на то, какие строки выбираются.
    • * — подстановочный знак, который указывает «все».
    • Если в качестве селектора столбца передается функция SQL, возвращается столбец с возвращаемым значением этой функции. Полезные функции включают в себя:
      • AVG(column) : возвращает среднее значение;
      • COUNT(*) : возвращает количество строк, возвращаемых базой данных;
      • MIN(column) : возвращает минимальное значение;
      • MAX(column) : возвращает максимальное значение.
    • LIKE — это ключевое слово, которое принимает строку шаблона и возвращает все строки, где столбец соответствует этому шаблону. % — подстановочный знак, который будет соответствовать любому тексту. В приведенном выше примере origin будет возвращена любая строка с «a» в столбце.
    • LIMIT устанавливает максимальное количество строк, которые будут возвращены.
    • ORDER BY организует строки по заданному столбцу в порядке возрастания (ASC) или убывания (DESC) перед возвратом строк.
    • GROUP BY организует строки, группируя одинаковые значения в заданном столбце.
    • HAVINGнеобязательный спецификатор, для GROUP BY которого ограничивается то, какие строки будут возвращаться, аналогично WHERE.

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

UPDATE flights
 SET duration = 430
 WHERE origin = 'New York'
 AND destination = 'London';
    • SET перезаписывает столбец во всех строках, соответствующих WHERE запросу.

Удаление данных из таблицы:

DELETE FROM flights
destination = 'Tokyo'

Связанные таблицы и составные запросы

SQL — это реляционная база данных, что означает, что таблицы внутри базы данных могут быть связаны друг с другом в некотором роде. Чтобы сделать это, мы можем сослаться, скажем, на idстолбец одной таблицы A в некотором другом столбце таблицы B. Внутри таблицы B значение id (которое соответствует таблице A) называется «внешним ключом».

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

CREATE TABLE passengers (
 id SERIAL PRIMARY KEY,
 name VARCHAR NOT NULL,
 flight_id INTEGER REFERENCES flights
 );

Как только эти две таблицы созданы, они могут быть запрошены одновременно:

  SELECT origin, destination, name FROM flights JOIN passengers ON passengers.flight_id = flights.id;
  SELECT origin, destination, name FROM flights JOIN passengers ON passengers.flight_id = flights.id WHERE name = 'Alice';
  SELECT origin, destination, name FROM flights LEFT JOIN passengers ON passengers.flight_id = flights.id;
    • JOIN указывает на то, что таблицы flights и passengers запрашиваются вместе.
    • JOIN выполняет «внутреннее соединение»: будут возвращены только строки, в которых обе таблицы соответствуют запросу. В этом примере будут возвращены только рейсы с пассажирами.
    • ON указывает, как две таблицы связаны. В этом примере столбец flight_id в passengers отражает значения в столбце id в flights.
    • Как и прежде, запросы могут быть ограничены с WHERE.
    • LEFT JOIN включает строки из первой таблицы в списке, даже если нет совпадения (например, на этом рейсе нет пассажиров). RIGHT JOIN аналогично (например, пассажиры без рейсов).

Когда базы данных становятся большими, часто полезно «проиндексировать» их, что позволяет быстрее быстро ссылаться на данный столбец в таблице каждый раз, когда  делается SELECT запрос. Обратите внимание, однако, что это занимает дополнительное пространство, а также время. При обновлении таблицы индекс также должен быть обновлен. Поэтому неразумно индексировать каждый столбец каждой таблицы без необходимости.

Вложенные запросы — еще один способ сделать более сложные выборы:

SELECT * FROM flights WHERE id IN
(SELECT flight_id FROM passengers GROUP BY flight_id HAVING COUNT(*)  1);
    • Во-первых, во внутреннем запросе flight_id будет возвращена таблица, содержащая рейсы с более чем одним пассажиром.
    • Затем во внешнем запросе flights будут выбраны все строки из id таблицы, возвращенные внутренним запросом.
    • Другими словами, этот вложенный запрос возвращает информацию о рейсе для рейсов с более чем 1 пассажиром.

Проблемы безопасности

Одна потенциальная проблема при использовании SQL заключается в том, что пользователь сможет вводить вредоносные команды в базу данных. Возьмите, например, простую форму входа в систему, которая запрашивает пароль и имя пользователя. То, что пользователь вводит в эти поля, можно вставить в команду SQL, чтобы выбрать свою учетную запись из таблицы учетных записей, например так:

SELECT * FROM users
 WHERE (username = 'username')
 AND (password = 'password')

Если кто — то догадывается , что есть SQL — код , что работает «за кулисами», онb потенциально могут получить доступ к чужой учетной записи, введя follwing в качестве пароля: 1′ OR ‘1’ = ‘1. Хотя это может показаться странным вне контекста, при обработке в SELECT запросе это даст результат:

SELECT * FROM users
 WHERE (username = 'hacker')
 AND (password = '1' OR '1' = '1');
    • Размещая одинарные кавычки в «нужных» местах, пользователь хитро отредактировал SQL-запрос. ‘1’ всегда равно ‘1’, поэтому не имеет значения, какой пароль пользователя. Аккаунт с именем пользователя hacker все равно будет возвращен.

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

Другой путь, по которому все может пойти не так, — это если два пользователя одновременно пытаются изменить или получить доступ к базе данных, а команды SQL выполняются в неожиданном порядке. Это проблема «условий гонки». Рассмотрим случай, когда банковская информация хранится в базе данных, и два клиента, которые имеют общий счет, пытаются одновременно снять деньги. Команды SQL, которые выполняются при снятии денег, могут выглядеть следующим образом:

SELECT balance FROM bank WHERE user_id = 1;
 UPDATE bank SET balance = balance - 100 WHERE user_id = 1;
    • Во-первых, необходимо проверить баланс клиента, чтобы убедиться, что у него достаточно денег.
    • Затем баланс обновляется, чтобы отразить их снятие.

Поскольку выполнение каждой команды занимает некоторое время, возможно, что два клиента в двух банкоматах производят снятие средств только с правильным временем: запрос SELECT клиента B выполняется перед запросом UPDATE клиента A. Даже если клиент А, возможно, уже взял последние 100 долларов на счете, поскольку база данных не была обновлена, когда клиент Б запрашивает 100 долларов, база данных разрешит снятие средств.

Решение гоночных условий заключается в реализации транзакций SQL. Во время транзакции база данных по существу блокируется, так что другой пользователь не может сделать запрос, пока он не будет завершен. Транзакция открывается с BEGIN и закрывается с помощью COMMIT.

Python и SQL

Чтобы интегрировать эти базы данных в веб-приложения, код Python, на котором работает веб-сервер, также должен иметь возможность выполнять команды SQL. SQLAlchemy — это библиотека Python, которая обеспечивает эту функциональность.
Начиная с простого Python вне веб-контекста, вот как можно распечатать все полеты в flights таблице:

  1. import os
  2.  
  3.   from sqlalchemy import create_engine
  4.   from sqlalchemy.orm import scoped_session, sessionmaker
  5.  
  6.   engine = create_engine(os.getenv("DATABASE_URL")) # объект механизма базы данных из SQLAlchemy, который управляет подключениями к базе данных
  7.                                                     # DATABASE_URL - это переменная среды, которая указывает, где находится база данных
  8.   db = scoped_session(sessionmaker(bind=engine))    # создать сеанс с заданной областью, который обеспечивает взаимодействие различных пользователей с
  9.                                                     # базой данных
  10.  
  11.   flights = db.execute("SELECT origin, destination, duration FROM flights").fetchall() # выполнить эту команду SQL и вернуть все результаты
  12.   for flight in flights
  13.       print(f"{flight.origin} to {flight.destination}, {flight.duration} minutes.") # для каждого полета распечатайте рейс
    • flight — это список строк, возвращенных из SQL-запроса. Доступ к отдельным столбцам в каждой строке можно получить с помощью точечной нотации.

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

  1. import csv
  2.  
  3.   # те же операторы импорта и настройки, что и выше
  4.  
  5.   f = open("flights.csv")
  6.   reader = csv.reader(f)
  7.   for origin, destination, duration in reader: # цикл дает каждому столбцу имя
  8.       db.execute("INSERT INTO flights (origin, destination, duration) VALUES (:origin, :destination, :duration)",
  9.                   {"origin": origin, "destination": destination, "duration": duration}) # подставить значения из строки CSV в команду SQL, в соответствии с этим
  10.       print(f"Added flight from {origin} to {destination} lasting {duration} minutes.")
  11.   db.commit() # транзакции предполагаются, поэтому закрыть транзакцию (завершено)
  • Нотация двоеточия, используемая в вызове db.execute (), является нотацией-заполнителем Postgres для значений. Это позволяет заменять переменные Python на команды SQL. Кроме того, SQLAlchemy автоматически заботится о дезинфекции переданных значений.

Включение SQL в веб-приложения с помощью
Flask

Все, что обсуждалось до сих пор, может быть реализовано точно так же внутри приложения Flask. Часть кода, добавляемого в application.py (вместе с необходимыми инструкциями import и set), может выглядеть следующим образом:

  1. @app.route("/")
  2.   def index():
  3.       flights = db.execute("SELECT * FROM flights").fetchall()
  4.       return render_template("index.html", flights=flights)
  5.  
  6.   @app.route("/book", methods=["POST"])
  7.   def book():
  8.       # Получить информацию о форме.
  9.       name = request.form.get("name")
  10.       try:
  11.           flight_id = int(request.form.get("flight_id"))
  12.       except ValueError:
  13.           return render_template("error.html", message="Invalid flight number.")
  14.  
  15.       # Убедитесь, что рейс существует.
  16.       if db.execute("SELECT * FROM flights WHERE id = :id", {"id": flight_id}).rowcount == 0:
  17.           return render_template("error.html", message="No such flight with that id.")
  18.       db.execute("INSERT INTO passengers (name, flight_id) VALUES (:name, :flight_id)",
  19.               {"name": name, "flight_id": flight_id})
  20.       db.commit()
  21.       return render_template("success.html")
  • Блок кода try всегда выполняется. Если есть ошибка, в частности, ValueError, в блоке выполняется код except. Затем программа продолжает работать как обычно.
  • rowcount является функцией SQLAlchemy, которая является свойством db.execute (), равным количеству строк, возвращаемых запросом.
  • error.html и success.html могут быть общими шаблонами, которые отображают message об ошибке и некоторые операторы успеха соответственно.

Соответствующий index.html:

<form action="{{ url_for('book') }}" method="post">
<div class="form-group"><select class="form-control" name="flight_id">{% for flight in flights %}
<option value="{{ flight.id }}">{{ flight.origin }} to {{ flight.destination }}</option>
{% endfor %}</select></div>
<div class="form-group"><input class="form-control" name="name" type="text" placeholder="Passenger Name"></div>
<div class="form-group"><button class="btn btn-primary">Book Flight</button></div>
</form>
  • Обратите внимание, что некоторые элементы, такие как класс form-control, являются компонентами Bootstrap.
  • Атрибуты name важны для ссылки на них в коде Python.
  • Как видно, те же обозначения, которые можно использовать в Python, также можно использовать в шаблоне Jinja2.

Если продвинуться на один шаг вперед в этом примере, можно настроить отдельные веб-страницы для каждого рейса, которые отображают некоторую информацию об этом рейсе. Вот код Python, который позаботится о маршрутизации для этих новых страниц:

  1. @app.route("/flights")
  2.   def flights():
  3.       flights = db.execute("SELECT * FROM flights").fetchall()
  4.       return render_template("flights.html", flights=flights)
  5.  
  6.   @app.route("/flights/<int:flight_id>")
  7.   def flight(flight_id):
  8.       # Убедитесь, что рейс существует.
  9.       flight = db.execute("SELECT * FROM flights WHERE id = :id", {"id": flight_id}).fetchone()
  10.       if flight is None:
  11.           return render_template("error.html", message="No such flight.")
  12.  
  13.       # Получить всех пассажиров.
  14.       passengers = db.execute("SELECT name FROM passengers WHERE flight_id = :flight_id",
  15.                               {"flight_id": flight_id}).fetchall()
  16.       return render_template("flight.html", flight=flight, passengers=passengers)
  • /flights — это будет общий маршрут, чтобы просто отобразить список всех рейсов.
  • Кроме того, / flight / <int: flight_id> предоставляет информационную страницу любого отдельного рейса. <int: flight_id> — это переменная, которая будет передана в Flask с помощью HTML-кода в flight.html. Затем эта переменная передается в функцию flight, которая передает идентификатор в запрос SQL, чтобы получить всю информацию о рейсе, включая всех пассажиров этого рейса.

flights.html:

<ul>
      {% for flight in flights %}
          <li>
              <a href="{{ url_for('flight', flight_id=flight.id) }}">
                  {{ flight.origin }} to {{ flight.destination }}
              </a>
          </li>
      {% endfor %}
  </ul>

В ссылке здесь находится flight.id, который представляет собой столбец строки flight, проходящей через цикл flights, который, в свою очередь, был передан из кода Python для /flights. Ему присваивается имя переменной flight_id, которое python route ожидает для /flights/<int:flight_id>.

flight.html:

<h1>Flight Details</h1>
 
  <ul>
      <li>Origin: {{ flight.origin }}</li>
      <li>Destination: {{ flight.destination }}</li>
      <li>Duration: {{ flight.duration}} minutes</li>
  </ul>
 
  <h2>Passengers</h2>
  <ul>
      {% for passenger in passengers %}
          <li>{{ passenger.name }}</li>
      {% else %}
          <li>No passengers.</li>
      {% endfor %}
  </ul>

Единственным новым элементом здесь является использование {% else%} с циклом for для учета случая, когда в passengers пусто.

Читайте больше по теме:

Подписаться
Уведомление о
guest
0 Комментарий
Inline Feedbacks
View all comments
Просмотры: 664

Популярные записи