VDW081r0

1from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, MetaData
2from sqlalchemy import create_engine
3from sqlalchemy.sql import select
4import psycopg2
5
6import pandas as pd
7
8# create_engine создаёт движок для общения с базой данных
9# общий вид: 'postgresql+psycopg2://postgres:Jdghsieurgmx@localhost:5432/postgres'
10dialect = "postgresql" 
11db_engine = "psycopg2" # библиотека реализующая доступ к БД. В документации называется engine, небольшая коллизия
12user = "postgres"
13password = "Jdghsieurgmx"
14hostname = "localhost"
15db_port = "5432"
16db_name = "postgres"
17# PosgreSQL является СУБД, т.е. системой управления базами данных. 
18# Внутри можно создавать много баз с табличками, и вот имя такой базы и передаётся в параметрах
19
20engine = create_engine('{0}+{1}://{2}:{3}@{4}:{5}/{6}'.format(dialect, db_engine, user, password, hostname, db_port, db_name))
21
22# взаимодействие с базой осуществляется через соединение
23conn = engine.connect()
24
25# БЛОК С SQL ЗАПРОСАМИ
26# SQL алхимия позволяет исполнять готовые SQL запросы
27
28# Смотрим какие таблицы уже есть
29for name in conn.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = 'test_schema'"): print(name)
30# Результат:
31('test_table',)
32('gwas_descriptors',)
33('df_sql_test',)
34
35# во второй части запроса: WHERE table_schema='test_schema' указываем для какой схемы выписать имена таблиц
36# если не указывать схему, то запрос будет в виде SELECT table_name FROM information_schema.tables и покажет имена ещё и системных таблиц
37
38# при обращении с таблицами надо указать схему которой принадлежит таблица: test_schema.gwas_descriptors
39# Сколько записей в gwas_description?
40for result in conn.execute("SELECT COUNT(*) FROM test_schema.gwas_descriptors"): print(result)
41# Результат:
42(5,)
43
44# Зачем постоянно писать for in?
45# на самом деле conn.execute возвращает итератор(в терминах базы данных казывается курсор) по результатам. Это нужно для всяких хитрых оптимизаций
46# подробности https://postgrespro.ru/docs/postgrespro/9.6/plpgsql-cursors
47# Если вы ЗНАЕТЕ что результатов будет не много, то можете обернуть запрос в лист
48# А если не уверены, то выберите только первые N вот так: 
49list(conn.execute("SELECT * FROM test_schema.gwas_descriptors LIMIT 2"))
50# хотим посмотреть на дживасы из gwas_descriptors
51list(conn.execute("SELECT * FROM test_schema.gwas_descriptors"))
52# Результат:
53[(1, '/home/storage/data/gwas_raw/FG/FG.txt',...)
54(1, '/home/storage/data/gwas_raw/FG/FG.txt',...)
55...
56
57# круто, но какие же столбцы
58list(conn.execute("SELECT column_name FROM information_schema.columns WHERE table_name = 'gwas_descriptors'"))
59# Результат:
60[('gwas_id',)
61('path',)
62('base_dir',)
63...
64
65# особенностью работы с conn.execute является то, что курсор уничтожается когда все данные выбраны.
66# например
67gwases = conn.execute("SELECT * FROM test_schema.gwas_descriptors")
68for gwas in gwases: print(gwas[1])
69# Result 
70/home/storage/data/gwas_raw/FG/FG.txt
71/home/storage/data/gwas_raw/FG/FG.txt
72...
73# но если запустить ешё раз, то результаты будут пустыми, т.к. для базы курсор уже выбрал результаты в предыдущий раз
74
75# И всегда работать с массивом?
76# Результаты можно загружать в pandas dataframe вот так:
77df = pd.read_sql_query("""SELECT * FROM test_schema.gwas_descriptors""", con = engine)
78
79# Для записи данных из pd обратно в таблицу есть несколько стратегий:
80# if_exists
81#     fail: If table exists, do nothing.
82#     replace: If table exists, drop it, recreate it, and insert data.
83#     append: If table exists, insert data. Create if does not exist.
84# Например нижняя команда допишет в конец данные из df
85df.to_sql('gwas_descriptors', engine, schema = 'test_schema', index = False, if_exists = 'append')
86
87
88# ПАМЯТКА ПО БАЗОВЫМ SQL КОМАНДАМ
89
90# выбрать только пути до gwas и gwas_id (именно в таком порядке)
91list(conn.execute("SELECT path, gwas_id FROM test_schema.gwas_descriptors"))
92# на месте path может быть любое поле из базы данных
93
94# чтобы выбирать не для всех, надо описать параметры по которым будут выбраны записи
95# например только дживасы с gwas_id=1
96list(conn.execute("SELECT path FROM test_schema.gwas_descriptors WHERE gwas_id = 1"))
97
98# изменить gwas_id:
99conn.execute("UPDATE test_schema.gwas_descriptors SET gwas_id=2 WHERE gwas_id = 1")
100
101# удалить у которых gwas_id
102conn.execute("DELETE FROM test_schema.gwas_descriptors WHERE gwas_id = 1")
103
104# Все SQL команды вы можете запускать в консоли базы данных, и они оч универсальны
105# больше гуглите по SQL cheat sheet и документации к PostgreSQL
106# https://www.postgresql.org/docs/9.1/static/index.html
107# когда поработали с conn, закройте его
108conn.close()
109
110# SQL очень удобный, но SQLAlchemy позволяет оперировать объектами базы данных 
111# ЧЕРЕЗ ОБЪЕКТЫ Python
112metadata = MetaData(engine)
113
114# подключимся к таблице с дескрипторами
115# 
116table_name = 'gwas_descriptors'
117schema = "test_schema"
118autoload = True # получить описание из таблицы автоматически, а не писать его самому
119descriptors = Table(table_name, metadata, autoload = autoload, schema = schema)
120
121# так как мы передали объект metadata при связи, то можем посмотреть таблицы:
122metadata.tables.keys()
123#Result
124dict_keys(['test_schema.gwas_descriptors'])
125# metadata.tables возвращает dict с подробным описанием таблиц
126
127# посмотреть колонки в таблице
128list(descriptors.columns)
129
130# если нужно выбрать данные
131selector = select([descriptors])
132list(conn.execute(selector))
133# Result
134[(1, '/home/storage/data/gwas_raw/FG/FG.txt', None, ,...),
135 (1, '/home/storage/data/gwas_raw/FG/FG.txt', None, ,...),
136 ...
137# нужно понимать, что это просто обвязка над SQL запросами, и результат аналогичный курсор из запроса:
138# SELECT test_schema.gwas_descriptors.gwas_id, ...  FROM test_schema.gwas_descriptors
139
140# работать с результатами можно через именованные столбцы
141# fetchone() вернёт первую запись из результата. Напоминаю, что следующий fetchone() вернет вторую, и так далее пока не будет выбрана последняя
142result = conn.execute(selector)
143result.fetchone()["gwas_id"]
144# или аналогично pandas (descriptors.c возвращает колонки)
145result.fetchone()[descriptors.c.gwas_id]
146
147# выбрать 
148selector = select([descriptors]).where(descriptors.c.gwas_id == 1)
149conn.execute(selector).fetchone()
150
151# добавить запись
152# описание синтаксиса insert для таблицы:
153ins = descriptors.insert()
154str(ins)
155# результат не очень понятный, так как много полей, общий синтаксис как для SQL
156# есть вариант описывать поля
157ins = descriptors.insert().values(gwas_id=3, path='path/222', ...)
158# перед выполнением запроса хорошо посмотреть как он выглядит
159str(ins)
160
161# чтобы выполнить запрос, нужно передать его в conn.execute()
162conn.execute(ins)
163
164# аналогичные методы есть и для update/delete
165# Подробности http://docs.sqlalchemy.org/en/latest/core/tutorial.html#inserts-updates-and-deletes
166
167# больше подробностей:
168# http://docs.sqlalchemy.org/en/latest/core/tutorial.html