Как заполнить базу данных postgresql

Как заполнить базу данных postgresql

Рассмотрим добавление в базу данных 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 делает это автоматически.

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

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

Метод 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

Операции с данными

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

INSERT INTO имя_таблицы (столбец1, столбец2, . столбецN) VALUES (значение1, значение2, . значениеN)

После INSERT INTO идет имя таблицы, затем в скобках указываются все столбцы через запятую, в которые надо добавлять данные. И в конце после слова VALUES в скобках перечисляются добавляемые значения.

Допустим, у нас в базе данных есть следующая таблица:

CREATE TABLE Products ( Id SERIAL PRIMARY KEY, ProductName VARCHAR(30) NOT NULL, Manufacturer VARCHAR(20) NOT NULL, ProductCount INTEGER DEFAULT 0, Price NUMERIC );

Добавим в нее одну строку с помощью команды INSERT:

INSERT INTO Products VALUES (1, 'Galaxy S9', 'Samsung', 4, 63000)

После удачного выполнения в pgAdmin в поле сообщений должно появиться сообщение «INSERT 0 1»:

Стоит учитывать, что значения для столбцов в скобках после ключевого слова VALUES передаются по порядку их объявления. Например, в выражении CREATE TABLE выше можно увидеть, что первым столбцом идет Id, поэтому этому столбцу передаетсячисло 1. Второй столбец называется ProductName, поэтому второе значение — строка «Galaxy S9» будет передано именно этому столбцу и так далее. То есть значения передаются столбцам следующим образом:

• Id: 1
• ProductName: ‘Galaxy S9’
• Manufacturer: ‘Samsung’
• ProductCount: 4
• Price: 63000

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

INSERT INTO Products (ProductName, Price, Manufacturer) VALUES ('iPhone X', 71000, 'Apple');

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

• ProductName: ‘iPhone X’
• Manufacturer: ‘Apple’
• Price: 71000

Для столбца Id значение будет генерироваться автоматически базой данных, так как он представляет тип Serial. То есть к значению из последней строки будет добавляться единица.

Для остальных столбцов будет добавляться значение по умолчанию, если задан атрибут DEFAULT (например, для столбца ProductCount), значение NULL. При этом неуказанные столбцы (за исключением тех, которые имеют тип Serial) должны допускать значение NULL или иметь атрибут DEFAULT.

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

Также мы можем добавить сразу несколько строк:

INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) VALUES ('iPhone 6', 'Apple', 3, 36000), ('Galaxy S8', 'Samsung', 2, 46000), ('Galaxy S8 Plus', 'Samsung', 1, 56000)

В данном случае в таблицу будут добавлены три строки.

Возвращение значений

Если мы добавляем значения только для части столбцов, то мы можем не знать, какие значения будут у других столбцов. Например, какое значени получит столбец Id у товара. С помощью оператора RETURNING мы можем получить это значение:

INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) VALUES('Desire 12', 'HTC', 8, 21000) RETURNING id;

Создаём свою БД на 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

Для создания базы данных сервер PostgreSQL должен быть развёрнут и запущен (см. Раздел 17.3).

База данных создаётся SQL-командой CREATE DATABASE :

CREATE DATABASE имя;

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

Создание баз данных это привилегированная операция. Как предоставить права доступа, описано в Разделе 20.2.

Поскольку для выполнения команды CREATE DATABASE необходимо подключение к серверу базы данных, возникает вопрос как создать самую первую базу данных. Первая база данных всегда создаётся командой initdb при инициализации пространства хранения данных (см. Раздел 17.2.) Эта база данных называется postgres . Далее для создания первой « обычной » базы данных можно подключиться к postgres .

Вторая база данных template1 , также создаётся во время инициализации кластера. При каждом создании новой базы данных в рамках кластера по факту производится клонирование шаблона template1 . При этом любые изменения сделанные в template1 распространяются на все созданные впоследствии базы данных. Следует избегать создания объектов в template1 , за исключением ситуации, когда их необходимо автоматически добавлять в новые базы. Более подробно в Разделе 21.3.

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

createdb dbname 

Утилита createdb не делает ничего волшебного, она просто подключается к базе данных postgres и выполняет ранее описанную SQL-команду CREATE DATABASE . Подробнее о её вызове можно узнать в createdb . Обратите внимание, что команда createdb без параметров создаст базу данных с именем текущего пользователя.

Примечание

Глава 19 содержит информацию о том, как ограничить права на подключение к заданной базе данных.

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

CREATE DATABASE имя_базы OWNER имя_роли;

из среды SQL, или:

createdb -O имя_роли имя_базы

из командной строки ОС. Лишь суперпользователь может создавать базы данных для других (для ролей, членом которых он не является).

Пред.	Наверх	След.
21.1. Обзор	Начало	21.3. Шаблоны баз данных