Как создать базу данных postgresql через python
Перейти к содержимому

Как создать базу данных postgresql через python

  • автор:

Как создать базу данных postgresql через python

Для создания базы данных применяется SQL-команда CREATE DATABASE , которой передается имя базы данных. Например, создадим базу данных с именем «metanit»:

import psycopg2 conn = psycopg2.connect(dbname="postgres", user="postgres", password="123456", host="127.0.0.1") cursor = conn.cursor() conn.autocommit = True # команда для создания базы данных metanit sql = "CREATE DATABASE metanit" # выполняем код sql cursor.execute(sql) print("База данных успешно создана") cursor.close() conn.close()

Обратите внимание, что для выражения «CREATE DATABASE» необходимо установиить автокоммит:

conn.autocommit = True

Благодаря этому команда SQL, во-первых, выполняется немедленно. А во-вторых, выполняется вне транзакции (выражение «CREATE DATABASE» должно выполняться именно вне транзакции)

После этого на сервере мы сможем найти базу данных «metanit»

Создание базы данных PostgreSQL и CREATE DATABASE в Python

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

Для создания таблицы в PostgreSQL применяется инструкция CREATE TABLE . Например, в вышесозданной базе данных «metanit» создадим таблицу people:

import psycopg2 conn = psycopg2.connect(dbname="metanit", user="postgres", password="123456", host="127.0.0.1") cursor = conn.cursor() # создаем таблицу people cursor.execute("CREATE TABLE people (id SERIAL PRIMARY KEY, name VARCHAR(50), age INTEGER)") # поддверждаем транзакцию conn.commit() print("Таблица people успешно создана") cursor.close() conn.close()

В метод cursor.execute() передается инструкция CREATE TABLE , которая создает таблицу people с тремя столбцами. Столбец id представляет идентификатор пользователя, хранит данные типа Serial, то есть число, которое будет автоматически генерироваться и инкрементироваться с каждой новой строкой и которое представляет первичный ключ. Второй столбец — name представляет строку — имя пользователя. И третий столбец — age представляет возраст пользователя.

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

Как создать базу данных postgresql через python

Рассмотрим добавление в базу данных PostgreSQL на примере следующей таблицы:

CREATE TABLE people ( id SERIAL PRIMARY KEY, name VARCHAR(50), age INTEGER)

Добавление данных

Для добавления данных применяется SQL-инструкция INSERT . Для добавления одной строки используем метод execute() :

import psycopg2 conn = psycopg2.connect(dbname="metanit", user="postgres", password="123456", host="127.0.0.1") cursor = conn.cursor() # добавляем строку в таблицу people cursor.execute("INSERT INTO people (name, age) VALUES ('Tom', 38)") # выполняем транзакцию conn.commit() print("Данные добавлены") cursor.close() conn.close()

Здесь добавляется одна строка, где name = «Tom», а age = 38. Перед выполнением команды INSERT открывается транзакция, для завершения которой необходимо вызвать метод commit() текущего объекта Connection.

Установка параметров

С помощью второго параметра в метод execute() можно передать значения для параметров SQL-запроса:

import psycopg2 conn = psycopg2.connect(dbname="metanit", user="postgres", password="123456", host="127.0.0.1") cursor = conn.cursor() # данные для добавления bob = ("Bob", 42) cursor.execute("INSERT INTO people (name, age) VALUES (%s, %s)", bob) conn.commit() print("Данные добавлены") cursor.close() conn.close()

В данном случае добавляемые в БД значения представляют кортеж bob. В SQL-запросе вместо конкретных значений используются знаки подстановки %s . Вместо этих символов при выполнении запроса будут вставляться данные из кортежа data. Так, первый элемент кортежа — строка «Bob» передается на место первого плейсхолдера %s, второй элемент — число 42 передается на место второго плейсхолдера %s. То есть в итоге команды SQL будет выглядеть следующим образом:

INSERT INTO people (name, age) VALUES ('Bob', 42)

Также обратите внимание, что НЕ надо помещать плейсхолдер %s в кавычки — psycopg2 делает это автоматически.

И если мы посмотрим на содержимое базы данных, то найдем там все добавленные объекты:

Добавление данных в PostgreSQL в Python

Множественная вставка

Метод executemany() позволяет вставить набор строк:

import psycopg2 conn = psycopg2.connect(dbname="metanit", user="postgres", password="123456", host="127.0.0.1") cursor = conn.cursor() # данные для добавления people = [("Sam", 28), ("Alice", 33), ("Kate", 25)] cursor.executemany("INSERT INTO people (name, age) VALUES (%s, %s)", people) conn.commit() print("Данные добавлены") cursor.close() conn.close()

В метод cursor.executemany() по сути передается то же самое выражение SQL, только теперь данные определены в виде списка кортежей people. Фактически каждый кортеж в этом списке представляет отдельную строку — данные отдельного пользователя, и при выполнении метода для каждого кортежа будет создаваться свое выражение INSERT INTO

Базы данных в Python: как подключить PostgreSQL и что это такое

Базы данных в Python: как подключить PostgreSQL и что это такое главное изображение

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

  • Psycopg2
  • py-postgresql
  • pg8000

Мы расскажем именно про модуль Psycopg2. И выбрали мы его по таким причинам:

  • Распространенность — Psycopg2 использует большинство фреймворков Python
  • Поддержка — Psycopg2 активно развивается и поддерживает основные версии Python
  • Многопоточность — Psycopg2 позволяет нескольким потокам поддерживать одно и то же соединение

Python-разработчик — с нуля до трудоустройства за 10 месяцев

  • Постоянная поддержка от наставника и учебного центра
  • Помощь с трудоустройством
  • Готовое портфолио к концу обучения
  • Практика с первого урока

Вы получите именно те инструменты и навыки, которые позволят вам найти работу

Установка Psycopg2

Для начала работы с модулем достаточно установить пакет при помощи pip:

install psycopg2-binary 

Если в вашем проекте используется poetry, то при первоначальной настройке проекта нужно добавить psycopg2-binary в зависимости. Для добавления в уже существующий проект воспользуйтесь командой:

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

Подключение к БД:

Для подключения к существующей базе данных необходимо знать основную информацию о вашей БД. Если вы не знаете, где ее взять, то пройдите сначала наш большой курс по Основам баз данных:

  • Username — имя пользователя, которое вы используете для работы с PostgreSQL
  • Password — пароль, который используется пользователем
  • Host Name — имя сервера или IP-адрес, на котором работает PostgreSQL
  • Database Name — имя базы данных, к которой мы подключаемся.

Для подключения к базе данных мы используем метод connect() , которому в качестве аргументов передаются вышеперечисленные данные:

import psycopg2 try: # пытаемся подключиться к базе данных conn = psycopg2.connect(dbname='test', user='postgres', password='secret', host='host') except: # в случае сбоя подключения будет выведено сообщение в STDOUT print('Can`t establish connection to database') 

Также подключение к базе данных может осуществляться с помощью Connection URI :

import psycopg2 try: # пытаемся подключиться к базе данных conn = psycopg2.connect('postgresql://user:password@host:port/database_name') except: # в случае сбоя подключения будет выведено сообщение в STDOUT print('Can`t establish connection to database') 

Читайте также: Вышел Python 3.11.0. В два раза быстрее, c детальным описанием ошибок и кучей новых типов

Взаимодействие Python с PostgreSQL

Итак, подключение к базе данных успешно выполнено. Дальше мы будем взаимодействовать с ней через объект cursor , который можно получить через метод cursor() объекта соединения. Он помогает выполнять SQL-запросы из Python.

# получение объекта курсора cursor = conn.cursor() 

С помощью cursor происходит передача запросов базе данных:

# Получаем список всех пользователей cursor.execute('SELECT * FROM users') all_users = cursor.fetchall() cursor.close() # закрываем курсор conn.close() # закрываем соединение 

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

  • cursor.fetchone() — вернуть одну строку
  • cursor.fetchall() — вернуть все строки
  • cursor.fetchmany(size=10) — вернуть указанное количество строк

Хорошей практикой при работе с базой данных является закрытие объекта cursor и соединения с базой. Для автоматизации этого процесса удобно взаимодействовать через контекстный менеджер , используя конструкцию with :

with conn.cursor as curs: curs.execute('SELECT * FROM users') all_users = curs.fetchall() 

В тот момент, когда объект cursor выходит за пределы конструкции with , происходит его закрытие и освобождение связанных с ним ресурсов.

По умолчанию результат возвращается в виде кортежа. Такое поведение возможно изменить, передав параметр cursor_factory в момент открытия объекта cursor , например, использовать NamedTupleCursor. Это вернет данные в виде именованного кортежа:

from psycopg2.extras import NamedTupleCursor # … with conn.cursor(cursor_factory=NamedTupleCursor) as curs: curs.execute('SELECT * FROM users WHERE name=%s', (name='Alfred')) alfred = curs.fetchone() alfred # (id=10, name='Alfred', age='90') # … 

Выполнение запросов

Psycopg2 преобразует переменные Python в SQL значения с учетом их типа. Все стандартные типы Python адаптированы для правильного представления в SQL.

Передача параметров в SQL-запрос происходит с помощью подстановки плейсхолдеров %s и цепочки значений в качестве второго аргумента функции:

with conn.cursor() as curs: curs.execute('SELECT id, name FROM users WHERE name=%s', ('John',)) curs.fetchall() # … with conn.cursor() as curs: curs.execute(INSERT INTO users (name, age) VALUES (%s, %s), ('John', 19)) # … conn.close() 

Подстановка значений в SQL-запрос используется для того, чтобы избежать атак типа SQL Injection. Также несколько полезных советов по построению запросов:

  • Плейсхолдер должен быть %s даже если тип подставляемого значения отличается от строки
  • Не заключайте плейсходер в кавычки
  • Если в запросе используется знак % , он должен быть указан как %%

Профессия «Python-разработчик»

  • Изучите Python — язык с простым и понятным синтаксисом
  • Научитесь создавать полноценные сайты и веб-приложения
  • Освойте популярный фреймворк Python — Django
  • Разберитесь в базах данных и научитесь управлять ими с помощью SQL

Работа с PostgreSQL в Python

17 Ноя. 2018 , Python, 265074 просмотров, How to Work with PostgreSQL in Python

PostgreSQL, пожалуй, это самая продвинутая реляционная база данных в мире Open Source Software. По своим функциональным возможностям она не уступает коммерческой БД Oracle и на голову выше собрата MySQL.

Если вы создаёте на Python веб-приложения, то вам не избежать работы с БД. В Python самой популярной библиотекой для работы с PostgreSQL является psycopg2. Эта библиотека написана на Си на основе libpq.

Установка

Тут всё просто, выполняем команду:

pip install psycopg2

Для тех, кто не хочет ставить пакет прямо в системный python, советую использовать pyenv для отдельного окружения. В Unix системах установка psycopg2 потребует наличия вспомогательных библиотек (libpq, libssl) и компилятора. Чтобы избежать сборки, используйте готовый билд:

pip install psycopg2-binary 

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

Начало работы

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

import psycopg2 conn = psycopg2.connect(dbname='database', user='db_user', password='mypassword', host='localhost') cursor = conn.cursor() 

Через курсор происходит дальнейшее общение в базой.

cursor.execute('SELECT * FROM airport LIMIT 10') records = cursor.fetchall() . cursor.close() conn.close() 

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

  • cursor.fetchone() — возвращает 1 строку
  • cursor.fetchall() — возвращает список всех строк
  • cursor.fetchmany(size=5) — возвращает заданное количество строк

Также курсор является итерируемым объектом, поэтому можно так:

for row in cursor: print(row) 

Хорошей практикой при работе с БД является закрытие курсора и соединения. Чтобы не делать это самому, можно воспользоваться контекстным менеджером:

 from contextlib import closing with closing(psycopg2.connect(. )) as conn: with conn.cursor() as cursor: cursor.execute('SELECT * FROM airport LIMIT 5') for row in cursor: print(row) 

По умолчанию результат приходит в виде кортежа. Кортеж неудобен тем, что доступ происходит по индексу (изменить это можно, если использовать NamedTupleCursor ). Если хотите работать со словарём, то при вызове .cursor передайте аргумент cursor_factory :

from psycopg2.extras import DictCursor with psycopg2.connect(. ) as conn: with conn.cursor(cursor_factory=DictCursor) as cursor: . 

Формирование запросов

Зачастую в БД выполняются запросы, сформированные динамически. Psycopg2 прекрасно справляется с этой работой, а также берёт на себя ответственность за безопасную обработку строк во избежание атак типа SQL Injection:

cursor.execute('SELECT * FROM airport WHERE city_code = %s', ('ALA', )) for row in cursor: print(row) 

Метод execute вторым аргументом принимает коллекцию (кортеж, список и т.д.) или словарь. При формировании запроса необходимо помнить, что:

  • Плейсхолдеры в строке запроса должны быть %s , даже если тип передаваемого значения отличается от строки, всю работу берёт на себя psycopg2.
  • Не нужно обрамлять строки в одинарные кавычки.
  • Если в запросе присутствует знак %, то его необходимо писать как %%.

Именованные аргументы можно писать так:

>>> cursor.execute('SELECT * FROM engine_airport WHERE city_code = %(city_code)s', ) . 

Модуль psycopg2.sql

Начиная с версии 2.7, в psycopg2 появился модуль sql. Его цель — упростить и обезопасить работу при формировании динамических запросов. Например, метод execute курсора не позволяет динамически подставить название таблицы.

>>> cursor.execute('SELECT * FROM %s WHERE city_code = %s', ('airport', 'ALA')) psycopg2.ProgrammingError: ОШИБКА: ошибка синтаксиса (примерное положение: "'airport'") LINE 1: SELECT * FROM 'airport' WHERE city_code = 'ALA' 

Это можно обойти, если сформировать запрос без участия psycopg2, но есть высокая вероятность оставить брешь (привет, SQL Injection!). Чтобы обезопасить строку, воспользуйтесь функцией psycopg2.extensions.quote_ident , но и про неё легко забыть.

from psycopg2 import sql . >>> with conn.cursor() as cursor: columns = ('country_name_ru', 'airport_name_ru', 'city_code') stmt = sql.SQL('SELECT <> FROM <> LIMIT 5').format( sql.SQL(',').join(map(sql.Identifier, columns)), sql.Identifier('airport') ) cursor.execute(stmt) for row in cursor: print(row) ('Французская Полинезия', 'Матайва', 'MVT') ('Индонезия', 'Матак', 'MWK') ('Сенегал', 'Матам', 'MAX') ('Новая Зеландия', 'Матамата', 'MTA') ('Мексика', 'Матаморос', 'MAM') 

Транзакции

По умолчанию транзакция создаётся до выполнения первого запроса к БД, и все последующие запросы выполняются в контексте этой транзакции. Завершить транзакцию можно несколькими способами:

  • закрыв соединение conn.close()
  • удалив соединение del conn
  • вызвав conn.commit() или conn.rollback()

Старайтесь избегать длительных транзакций, ни к чему хорошему они не приводят. Для ситуаций, когда атомарные операции не нужны, существует свойство autocommit для connection класса. Когда значение равно True , каждый вызов execute будет моментально отражен на стороне БД (например, запись через INSERT).

with conn.cursor() as cursor: conn.autocommit = True values = [ ('ALA', 'Almaty', 'Kazakhstan'), ('TSE', 'Astana', 'Kazakhstan'), ('PDX', 'Portland', 'USA'), ] insert = sql.SQL('INSERT INTO city (code, name, country_name) VALUES <>').format( sql.SQL(',').join(map(sql.Literal, values)) ) cursor.execute(insert) 

Интересные записи:

  • Django Channels: работа с WebSocket и не только
  • Введение в logging на Python
  • Работа с MySQL в Python
  • FastAPI, asyncio и multiprocessing
  • Что нового появилось в Django Channels?
  • Pyenv: удобный менеджер версий python
  • Обзор Python 3.9
  • Почему Python?
  • Руководство по работе с HTTP в Python. Библиотека requests
  • Celery: начинаем правильно
  • Разворачиваем Django приложение в production на примере Telegram бота
  • Авторизация через Telegram в Django и Python
  • Python-RQ: очередь задач на базе Redis
  • Как написать Telegram бота: практическое руководство
  • Введение в pandas: анализ данных на Python
  • Django, RQ и FakeRedis
  • Итоги первой встречи Python программистов в Алматы
  • Обзор Python 3.8
  • Участие в подкасте TalkPython
  • Интеграция Trix editor в Django
  • Строим Data Pipeline на Python и Luigi
  • Видео презентации ETL на Python
  • Авторизация через Telegram в Django приложении

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *