[PostgreSQL] Создание БД. Подключение к БД через Python с помощью pyodbc.

Введение

Статья является продолжением статьи Поднятие службы на Windows.

В статье рассматривается создание БД, таблиц со связями и подключение к БД через python.

Цель статьи рассмотреть создание БД и таблиц через консоль из файла. Синтаксис SQL (DDL) в статье не рассматривается.

В конце статьи есть ссылка на исходники в GitLab

Используемые технологии

ОС: Windows 10

БД: PostgreSQL 17.5

Язык: Python 3.12

Библиотека: pyodbc

Драйвер: psqlodbc 17

План работ

• создаем базу данных
• настраиваем виртуальную среду
• подключаемся к базе данных

Создаем базу данных

В рамках статьи рассматривается создание БД через cmd считыванием скрипта из файла. Мы можем обойтись без создания БД и таблиц, так как в рамках поднятия службы из предыдущей статьи, у нас уже есть БД с названием postgres и есть пользователь postgres, а также есть множество системных таблиц, но создать новую БД интересней.

Поэтому cоздадим какую-нибудь папку для работы. Пусть это будет: CreateDataBasePG

В ней создадим файл "createDB.sql", который будет создавать БД и пользователя.

Будем создавать БД с названием testbase. У команды CREATE DATABASE есть множество настроек, но мы их опустим

CREATE DATABASE testbase;

Создадим пользователя test_user и укажем ему права на вход, на создание БД и укажем его владельцем БД.

CREATE USER test_user WITH PASSWORD '123';

ALTER USER test_user WITH LOGIN;

ALTER USER test_user CREATEDB;

ALTER DATABASE testbase OWNER TO test_user;

Создадим отдельный файл "createTABs.sql", который создаст таблицы и заполнит их данными.

Первой строкой укажем кодировку utf8. Это нужно для того, чтобы с файла корректно считывалась кириллица.

SET client_encoding TO 'utf8';

Создадим 3 таблицы:

Компании (Companies)

CREATE TABLE Companies

(

Id SERIAL PRIMARY KEY,

FirmName VARCHAR(100) NOT NULL,

INN VARCHAR(10) NOT NULL

);

Закупки (Purchases)

CREATE TABLE Purchases

(

Id SERIAL PRIMARY KEY,

TotalSum INTEGER NOT NULL,

Status INTEGER NOT NULL

);

КомпанииЗакупки (Companies_Purchases) (для связки многие ко многим)

CREATE TABLE Companies_Purchases

(

Id SERIAL,

CompanyId INTEGER NOT NULL,

PurchaseId INTEGER NOT NULL,

FOREIGN KEY (CompanyId) REFERENCES Companies (Id),

FOREIGN KEY (PurchaseId) REFERENCES Purchases (Id),

PRIMARY KEY (Id, CompanyId, PurchaseId)

);

Заполним таблицы данными

insert into Companies (FirmName, INN) values ('ООО Золото', '1234567890');

insert into Companies (FirmName, INN) values ('ООО Серебро', '0245674274');

insert into Companies (FirmName, INN) values ('ООО Медь', '3457654890');

insert into Purchases (TotalSum, Status) values (100000, 0);

insert into Purchases (TotalSum, Status) values (100000, 1);

insert into Purchases (TotalSum, Status) values (50000, 0);

insert into Purchases (TotalSum, Status) values (100000, 0);

insert into Companies_Purchases (CompanyId, PurchaseId) values (1, 1);

insert into Companies_Purchases (CompanyId, PurchaseId) values (1, 2);

insert into Companies_Purchases (CompanyId, PurchaseId) values (1, 3);

insert into Companies_Purchases (CompanyId, PurchaseId) values (2, 2);

insert into Companies_Purchases (CompanyId, PurchaseId) values (3, 4);

Теперь создадим bat-файл "createDB.bat", который будет запускать предыдущие 2 скрипта.

Первой строкой указываем кодировку для корректного считывания файлов.

chcp 1251

Подключаемся под пользователем postgres к БД postgres и запускаем файл createDB.sql

psql -h localhost -p 5432 -d postgres -U postgres -a -f createDB.sql

Подключаемся под пользователем test_user к БД testbase и запускаем файл createTABs.sql

psql -h localhost -p 5432 -d testbase -U test_user -a -f createTABs.sql

pause

Запускаем bat-файл. Необходимо будет ввести пароль от пользователя postgres, его мы создавали в предыдущей статье, а потом еще от пользователя test_user, от него пароль мы указали в файле "createDB.sql"

Скорее всего будут кракозябры при работе insert into. Ничего страшного. Это проблема Windows и Postgres. В Linux такого не было бы. Как мы убедимся позже, в БД записалось все корректно.

Настраиваем виртуальную среду

Открываем папку, где лежат наш bat-файл и sql скрипты.

Создадим файл requirements.txt. И укажем в нем всего одну строку

pyodbc==5.2.0

В cmd открываем текущую папку и создаем виртуальную среду

py -3.12 -m venv venv

Активируем среду

venv\Scripts\activate

Произведём установку библиоти из файла

pip install -r requirements.txt

Выходим из виртуальной среды

deactivate

Подключаемся к базе данных

Для работы с PostgreSQL одной библиотеки pyodbc недостаточно. Библиотеке в помощь нужен драйвер для работы с базой данных. Такой драйвер нам любезно предоставили .

Скачиваем exe файл

Создаем скрипт на Python. Создаем файл app.py

Первой строкой указываем шебанг для работы с env

Только потом указываем библиотеку

Подключаемся к БД.

Пишем sql запрос. Пробуем найти сумму активных закупок по компаниям. (Пусть status = 0 будет означать активный)

Делаем запрос, обрабатываем результат и не забываем закрыть подключение

Итоговый скрипт:

app.py

Можем проверять наполнение нашей БД. Открываем cmd там, где расположен файл app.py и прописываем команду

py app.py

Получаем результат

Данные из БД получаем

Исходники проекта

Подписывайтесь на Дзен, а также приглашаю в мой телеграмм канал, там публикую другой, но не менее интересный контент.