Как создать базу данных 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 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 представляет возраст пользователя.

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

Базы данных в 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 из CSV

Kaggle — — платформа созданная для проведение конкурсов по исследованию данных. Организаторы выкладывают Datasets , описывают задачи , метрики по которым будут выявляться победители конкурса , призы и время проведения. Каждый желающий может выставить свою работа по этим данных , красиво описать её , показать свои умения и надеяться на победу.

Мы будем использовать Used Cars Dataset

Также мы можем посмотреть Code других участников соревнования

• подчерпнуть оттуда интересную информацию
• найти нестандартные подходы к обработке данных
• На примере других работа , научиться чему-то новому
• и даже наткнуться на боже зачем это тут ? интересную работу по »Ускорение рабочего процесса Pandas с Modin»

• Найти друзей
• Заставить других сделать свою работу
• Узнать ответ на интересующий тебя вопрос(есть шанс)

Перейдём к делу, Pgadmin4

pgAdmin — это платформа с открытым исходным кодом для администрирования и разработки на PostgreSQL и связанных с ней систем управления базами данных.

pgAdmin будет предложен в установке PostgreSQL, я пользуюсь 14.3. Багов и проблем не боюсь , беру самую новую версию сразу видно профессионал. Если боитесь устанавливать приложение без ведения за ручку , вам поможет интернет()_(). Уже 1000 раз было рассказывать как это делать и что за чему , так что не буду тратить наше драгоценное.

Перейдём к делу 2, Python

Python — — высокоуровневый язык программирования. и нам нужна библиотека pandas

Перейдём к делу 3, Pycharm

Pycharm — — среда разработки(IDE) созданная специально для языка программирования Python.

• Предоставляет средства для анализа кода
• графический отладчик
• инструменты для отладки юнит-тестов
• интуитивно понятный интерфейс
• очень много полезных функций для продвинутых пользователей

Начнём кодить(0)_(з)

экспорт данных + получение основной информации

для начала открываем Pycharm, создаём там новый проект и в терминале инсталлируем библиотеку pаndas Открываем терминал и пишем там pip install pandas, нажимаем enter и ждём установки.

pip install pandas

Далее нам надо открыть для чтения наш файл —

import pandas as pd # загружаем наш csv car = pd.read_csv(r'C:\Users\ratmu\PycharmProjects\Cars\vehicles.csv') # просмотр первыйх 5 строк print(car.head(5)) 

видим что из-за 26 столбцов, Pycharm не подгружает всё таблицу( в дальнейшем исправим)

# Cведения о датафрейме, выходит общая информация о нём вроде заголовка, количества значений, типов данных столбцов. print(car.info())

Получаем основные данные из таблицы.

• Название всех столбцов
• Количество значений в них
• Типы данных

# загружаем нашу csv , смотрим тольна на первые 100 строк ибо долго грузиться полный файл ) car = pd.read_csv(r'C:\Users\ratmu\PycharmProjects\Cars\vehicles.csv',nrows=100) # просмотр всей таблици без ограничений колличество знаков , на строку) print(car.to_csv(None))

Получаем гигантский DF который я не могу передать как картинку , так что переходим сразу обработке этих данных

Очистка данных

Убирает лишние столбцы

Нам точно не нужны url ссылки, и пустая строка country , так же нам не надо описание автомобиля на 1000+ символов(description) Так что пишемс простой код

import pandas as pd # загружаем нашу csv car = pd.read_csv(r'C:\Users\ratmu\PycharmProjects\Cars\vehicles.csv') # удлаляем столбци с которыми не будем работать car.drop(['description', 'county', 'url', 'region_url', 'image_url', 'posting_date'], axis=1, inplace=True)

drop удаления столбцов , Axis: указывает, что столбцы или строки должны быть удалены, inplace = True, он возвращает Data Frame с удаленными столбцами или None

После этого сохраняем наш изменённый df в новый файл , что бы в дальнейшем работать только с нужными данными

# сохраняем обработанный df в csv файл car.to_csv('car_info.csv')

Убираем выбросы

Выбросы — — это данные, которые существенно отличаются от других наблюдений. Они могут соответствовать реальным отклонениям, но могут быть и просто ошибками

Нас интересуют выбросы в колонке price, согласитесь если цена на машину будет 5 000 000 000 долларов это будет сильно менять среднее значение цены и мешать нашим вычислениям

Находим выбросы

Узнаем самые часто встречаемые цены с помощью value_counts И_И узнаем статистику по цене в нашем df с помощью describe

print(car['price'].value_counts().iloc[:5]) print(car.price.describe())

слева видим что у нас есть 32к значений = 0, которые стоят обрезать , и множество значений цены = около 3к

справа у нас показатели зашкаливают и выдают огромные цифры. ЧТО ТО ТУТ НЕ ТАК.

А теперь сделаем грубую и ужасную профессиональную вырезку. Я называю её «и так сойдёт»(объясняю, мы как бы не готовим данные для отчётов и т.д , а просто убираем самый явный бред)

Импортируем 2 крутые штуки seaborn

pip install seaborn pip install matplotlib

Теперь в шапку нашего кода добавляем

import matplotlib.pyplot as plt import seaborn as sns

Строем простецкий графии

plt.figure(figsize=(5,8)) sns.boxplot(y='price', data=car,showfliers=True) plt.show()

Тут мы смотря на значения Y будем постепенно обрезать наши выбросы , пока они не станут чуть-чуть адекватными( код ниже)

Убираем выбросы

# price > 300000 если труе обрезаем ( а эту цифру берём из значение Y с графика выше) car.drop(car[car.price > 300000].index, inplace = True) # + убираем все лишнии значения car.drop(car[car.price == 0].index, inplace = True)

да это всё можно делать с помощью IQR (но это совершенно другая история)

И с помощью value_counts, describe проверяем похоже ли это на правду

Сохраняем то что сделали

# сохраняем обработанный df в csv файл без заголовка и интекса , для экспорта в pgadmin car.to_csv('car_info.csv', index=False)

Переноcим данные в СУБД

Создаём пустую бд под экспорт

Осталось дело за малым, открываем pgAdmin4(и подключаемся к серверу)

Далее нам нужно, создать базу данных

Выбираем нашу базу данных и открываем запросник

Вводим туда простейший код

CREATE TABLE car ( car_id int8, region text, price int8, year float4, manufacture text, model text, condition_car text, cylinders text, fuel text, odometer float4, tittle_status text, transmision text, VIN text, drive text, size text, type text, paint_color text, state text, lat float4, long float4 )

дааааааа — можно использовать CHARACTER VARYING , int4 , date . Но мы сейчас не про экономию места на диске

Далее нам надо импортировать наши данные в таблицу

Занимаемся экспортом данных

Находим и открываем sql Shell (psql) — терминальный интерфейс для PostgreSQL

просто нажимаем на enter везде кроме, Database(название вашей базы данных) и Пароль пользователя postgres. И у нас начинается подключение

Далее вводим команду

\COPY car FROM 'C:\Users\ratmu\PycharmProjects\Cars\vehicles.csv' DELIMITER ',' CSV HEADER;

и бежим проверять в pgAdmin всё ли сработало

SELECT * FROM car

Если увидели таблицу значит вы молодец

Введение в PostgreSQL с Python +Psycopg2

Это руководство по PostgreSQL в Python описывает, как использовать модуль Psycopg2 для подключения к PostgreSQL, выполнения SQL-запросов и других операций с базой данных.

Здесь не инструкции по установки локального сервера, так как это не касается python. Скачайте и установите PostgreSQL с официального сайта https://www.postgresql.org/download/. Подойдут версии 10+, 11+, 12+.

Вот список разных модулей Python для работы с сервером базы данных PostgreSQL:

• Psycopg2,
• pg8000,
• py-postgreql,
• PyGreSQL,
• ocpgdb,
• bpsql,
• SQLAlchemy. Для работы SQLAlchemy нужно, чтобы хотя бы одно из перечисленных выше решений было установлено.

Примечание: все модули придерживаются спецификации Python Database API Specification v2.0 (PEP 249). Этот API разработан с целью обеспечить сходство разных модулей для доступа к базам данных из Python. Другими словами, синтаксис, методы и прочее очень похожи во всех этих модулях.

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

• Он используется в большинстве фреймворков Python и Postgres;
• Он активно поддерживается и работает как с Python 3, так и с Python 2;
• Он потокобезопасен и спроектирован для работы в многопоточных приложениях. Несколько потоков могут работать с одним подключением.

В этом руководстве пройдемся по следующим пунктам:

• Установка Psycopg2 и использование его API для доступа к базе данных PostgreSQL;
• Вставка, получение, обновление и удаление данных в базе данных из приложения Python;
• Дальше рассмотрим управление транзакциями PostgreSQL, пул соединений и методы обработки исключений, что понадобится для разработки сложных программ на Python с помощью PostgreSQL.

Установка Psycopg2 с помощью pip

Для начала нужно установить текущую версию Psycopg2 для использования PostgreSQL в Python. С помощью команды pip можно установить модуль в любую операцию систему: Windows, macOS, Linux:

pip install psycopg2

Также можно установить конкретную версию программы с помощью такой команды:

pip install psycopg2=2.8.6

Если возникает ошибка установки, например «connection error: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:598)», то ее можно решить, сделав files.pythonhosted.org доверенным хостом:

python -m pip install --trusted-host pypi.org --trusted-host files.pythonhosted.org --trusted-host pypi.python.org psycopg2 

Модуль psycopg2 поддерживает:

• Python 2.7 и Python 3, начиная с версии 3.4.
• Сервер PostgreSQL от 7.4 до 12.
• Клиентскую библиотеку PostgreSQL от 9.1.

Проверка установки Psycopg2

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

• Collecting psycopg2
• Downloading psycopg2-2.8.6
• Installing collected packages: psycopg2
• Successfully installed psycopg2-2.8.6

При использовании anaconda подойдет следующая команда.

conda install -c anaconda psycopg2

Подключение к базе данных PostgreSQL из Python

В этом разделе рассмотрим, как подключиться к PostgreSQL из Python с помощью модуля Psycopg2.

Вот какие аргументы потребуются для подключения:

• Имя пользователя: значение по умолчанию для базы данных PostgreSQL – postgres.
• Пароль: пользователь получает пароль при установке PostgreSQL.
• Имя хоста: имя сервера или IP-адрес, на котором работает база данных. Если она запущена локально, то нужно использовать localhost или 127.0.0.0.
• Имя базы данных: в этом руководстве будем использовать базу postgres_db .

Шаги для подключения:

• Использовать метод connect() с обязательными параметрами для подключения базы данных.
• Создать объект cursor с помощью объекта соединения, который возвращает метод connect . Он нужен для выполнения запросов.
• Закрыть объект cursor и соединение с базой данных после завершения работы.
• Перехватить исключения, которые могут возникнуть в процессе.

Создание базы данных PostgreSQL с Psycopg2

Для начала создадим базу данных на сервере. Во время установки PostgreSQL вы указывали пароль, его нужно использовать при подключении.

 
import psycopg2 
from psycopg2 import Error 
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT try: 
# Подключение к существующей базе данных 
connection = psycopg2.connect(user="postgres", 
# пароль, который указали при установке PostgreSQL 
password="1111", 
host="127.0.0.1", 
port="5432") 
connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) 
# Курсор для выполнения операций с базой данных 
cursor = connection.cursor() 
sql_create_database = 'create database postgres_db' 
cursor.execute(sql_create_database) except (Exception, Error) as error: 
print("Ошибка при работе с PostgreSQL", error) 
finally: 
if connection: 
cursor.close() 
connection.close() 
print("Соединение с PostgreSQL закрыто") 

Пример кода для подключения к базе данных PostgreSQL из Python
Для подключения к базе данных PostgreSQL и выполнения SQL-запросов нужно знать название базы данных. Ее нужно создать прежде чем пытаться выполнить подключение.
 
import psycopg2 
from psycopg2 import Error 
 
try: 
# Подключение к существующей базе данных 
connection = psycopg2.connect(user="postgres", 
# пароль, который указали при установке PostgreSQL 
password="1111", 
host="127.0.0.1", 
port="5432", 
database="postgres_db") 
 
# Курсор для выполнения операций с базой данных 
cursor = connection.cursor() 
# Распечатать сведения о PostgreSQL 
print("Информация о сервере PostgreSQL") 
print(connection.get_dsn_parameters(), "\n") 
# Выполнение SQL-запроса 
cursor.execute("SELECT version();") 
# Получить результат 
record = cursor.fetchone() 
print("Вы подключены к - ", record, "\n") 
 
except (Exception, Error) as error: 
print("Ошибка при работе с PostgreSQL", error) 
finally: 
if connection: 
cursor.close() 
connection.close() 
print("Соединение с PostgreSQL закрыто") 

После подключения появится следующий вывод:
Информация о сервере PostgreSQL Вы подключены к - ('PostgreSQL 10.13, compiled by Visual C++ build 1800, 64-bit',) Соединение с PostgreSQL закрыто
Разбор процесса подключения в деталях
import psycopg2 — Эта строка импортирует модуль Psycopg2 в программу. С помощью классов и методов модуля можно взаимодействовать с базой.
from psycopg2 import Error — С помощью класса Error можно обрабатывать любые ошибки и исключения базы данных. Это сделает приложение более отказоустойчивым. Этот класс также поможет понять ошибку в подробностях. Он возвращает сообщение об ошибке и ее код.
psycopg2.connect() — С помощью метода connect() создается подключение к экземпляру базы данных PostgreSQL. Он возвращает объект подключения. Этот объект является потокобезопасным и может быть использован на разных потоках.
Метод connect() принимает разные аргументы, рассмотренные выше. В этом примере в метод были переданы следующие аргументы: user = "postgres", password = "1111", host = "127.0.0.1", port = "5432", database = "postgres_db" .
cursor = connection.cursor() — С базой данных можно взаимодействовать с помощью класса cursor . Его можно получить из метода cursor() , который есть у объекта соединения. Он поможет выполнять SQL-команды из Python.
Из одного объекта соединения можно создавать неограниченное количество объектов cursor . Они не изолированы, поэтому любые изменения, сделанные в базе данных с помощью одного объекта, будут видны остальным. Объекты cursor не являются потокобезопасными.
После этого выведем свойства соединения с помощью connection.get_dsn_parameters() .
cursor.execute() — С помощью метода execute объекта cursor можно выполнить любую операцию или запрос к базе данных. В качестве параметра этот метод принимает SQL-запрос. Результаты запроса можно получить с помощью fetchone() , fetchmany() , fetchall() .
В этом примере выполняем SELECT version(); для получения сведений о версии PosgreSQL.
Блок try-except-finally — Разместим код в блоке try-except для перехвата исключений и ошибок базы данных.
cursor.close() и connection.close() — Правильно всегда закрывать объекты cursor и connection после завершения работы, чтобы избежать проблем с базой данных.
Создание таблицы PostgreSQL из Python
В этом разделе разберем, как создавать таблицу в PostgreSQL из Python. В качестве примера создадим таблицу Mobile.
Выполним следующие шаги:

Подготовим запрос для базы данных
Подключимся к PosgreSQL с помощью psycopg2.connect() .
Выполним запрос с помощью cursor.execute() .
Закроем соединение с базой данных и объект cursor .

Теперь рассмотрим пример.