How to get data from postgresql in python?

Question

Python

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)

in genetateRep the cycle is executed 1 time, as I understand it, because I use the cursor (cursor.execute) again inside the cycle, it updates it and the cycle does not go further?

Answer 1

Hastily

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. see how to work with date and time correctly
2. do not use interpolation in sql query

Answer 2

Well, first of all, yes, your loop does not make sense for the exact reason indicated.
The sequence should be execute() first, then the for loop.
Secondly...

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

Don't do it. Learn parameter substitution.