Как получить данные из postgresql в python?

Question

[FlashDok]

def generateRep(cursor, period):

    for dev in cursor: #срабатывает 1 раз видимо из за cursor.execute используемый в цикле
        print(dev[0])

        cursor.execute("SELECT * from rawdata where deveui = '" + str(dev[0]) + "' and time between " + str(int((period[0]-86400*1)*1000)) + " and " + str(int((period[1]+(86400*4))*1000))+ " and data like '%'  ORDER BY id DESC")
		
        response = cursor.fetchall()
        print(response)


connection = psycopg2.connect(user="pguser",
                                  password="localpass",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="wan_server")

cursor = connection.cursor()

cursor.execute("SELECT * from devices LEFT JOIN devatt on devices.dev = devatt.dev WHERE devatt.att = 'otherinfo' AND devatt.value like '0%'")

generateRep(cursor, period)

в genetateRep цикл выполняется 1 раз, я так понимаю из-за того что я использую вновь cursor (cursor.execute) внутри цикла он его обновляет и цикл дальше не идет?

Answer 1

[Александр]

На скорую руку

import psycopg2
from datetime import datetime, timedelta
from pprint import pprint


def get_dt(dt: str):
    return datetime.strptime(dt, '%Y%m%d')


def get_dt_delta(dt: datetime, s=1, e=4):
    return dt - timedelta(days=s), dt + timedelta(days=e)


def execute(config, sql, param=None, select_mode=True):
    connection = None
    try:
        connection = psycopg2.connect(**config)
        cursor = connection.cursor()
        if param:
            cursor.execute(sql, param)
        else:
            cursor.execute(sql)
        if select_mode:
            return cursor.fetchall()
    except Exception as E:
        print(E)
    finally:
        connection.close()


def get_devices():
    devices_q = "SELECT * from devices LEFT JOIN devatt on devices.dev = devatt.dev " \
                "WHERE devatt.att = 'otherinfo' AND devatt.value like '0%'"
    return [x[0] for x in execute(cfg, devices_q)]


def generate_rep(devs, period):
    r = {}
    period_dt = get_dt(period)
    period_s, period_e = get_dt_delta(period_dt)
    for dev in devs:
        q = 'SELECT * from rawdata where deveui = %s and time between %s AND %s ORDER BY id DESC'
        rawdata = execute(config=cfg, sql=q, param=(dev, period_s, period_e))
        r[dev] = rawdata
    return r


if __name__ == "__main__":
    cfg = dict(user="pguser", password="localpass", host="127.0.0.1", port="5432", database="wan_server")
    devices = get_devices()
    date = '20220308'
    result = generate_rep(devs=devices, period=date)
    pprint(result)

1. смотрите как правильно работать с датой и временем
2. не используйте интерполяцию в sql запросе

Comments

[FlashDok]

Сделал по примеру (изменил в коде время (оставил как было) т.к в базе оно в unix формате).
Возник вопрос информация о приборе в таком виде лежит в БД "\x01a2643e00885efc610203040100d8065d20040000ffffd4b92303120700000001000000", а ответ r[dev][0][0]
от БД в коде выдает это <memory at 0x0000012ED1BF9400>

Answer 2

[Vindicar]

Ну во-первых, да, ваш цикл не имеет смысла именно по указанной причине.
Последовательность должна быть сначала execute(), потом цикл for.
Во-вторых...

"SELECT * from rawdata where deveui = '" + str(dev[0]) + "'

Не делайте так. Освойте подстановку параметров.

Comments

[FlashDok]

Но как реализовать? я не могу выполнить execute() до цикла.
Т.е я получаю список устройств, а далее с каждого устройства я собираю данные через цикл.
Устройства у меня "до цикла" лежат в cursor, проходит одна итерация и cursor из списка устройств становится данными первого устройства.

[Vindicar]

FlashDok, просто используйте отдельный курсор. У вас может быть несколько активных курсоров. Ну или освойте запросы с JOIN, если объём данных позволяет.