Das deutsche Python-Forum

Code: Alles auswählen

from configparser import ConfigParser
import psycopg2
import config

def configsql(filename='database.ini', section='postgresql'):
    # create a parser
    parser = ConfigParser()
    # read config file
    parser.read(filename)
 
    # get section, default to postgresql
    db = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            db[param[0]] = param[1]
    else:
        raise Exception('Section {0} not found in the {1} file'.format(section, filename))
 
    return db
    
  def get_vendors():
    """ query data from the vendors table """
    conn = None
    try:
        params = configsql()
        conn = psycopg2.connect(**params)
        cur = conn.cursor()
        cur.execute("SELECT vendor_id, vendor_name FROM vendors ORDER BY vendor_id")
        print("The number of parts: ", cur.rowcount)
        row = cur.fetchone()
 
        while row is not None:
            print(row)
            row = cur.fetchone()
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()  

Code: Alles auswählen

(68, '{"2018-11-29 20:26",43.70,24.60,44.20,24.10,620,"16.67\r\n"}')

Code: Alles auswählen

def insert_vendor(vendor_name):
    """ insert a new vendor into the vendors table """
    sql = """INSERT INTO vendors(vendor_name)
             VALUES(%s) RETURNING vendor_id;"""
    conn = None
    vendor_id = None
    try:
        # read database configuration
        params = configsql()
        # connect to the PostgreSQL database
        conn = psycopg2.connect(**params)
        # create a new cursor
        cur = conn.cursor()
        # execute the INSERT statement
        cur.execute(sql, (vendor_name,))
        # get the generated id back
        vendor_id = cur.fetchone()[0]
        # commit the changes to the database
        conn.commit()
        # close communication with the database
        cur.close()
    finally:
        if conn is not None:
            conn.close()
 
    return vendor_id

Code: Alles auswählen

def get_vendors(**params):
    """ query data from the vendors table """
    conn = psycopg2.connect(**params)
    try:
        cur = conn.cursor()
        cur.execute("SELECT vendor_id, vendor_name FROM vendors ORDER BY vendor_id")
        print("The number of parts: ", cur.rowcount)
        for row in cur:
            print(row)
        cur.close()
    finally:
        conn.close()

Code: Alles auswählen

def insert_data(luftfeuchtigkeitdrin,temperaturdrin,luftfeuchtigkeitausen,temperaturausen,erdfeuchtigkeit,lux):
    my_date = datetime.datetime.now(pytz.timezone('Europe/Berlin'))
    fmt = '%Y-%m-%d %H:%M'
    conn = psycopg2.connect(benutzername etc)
    print ("Opened database successfully")

    cur = conn.cursor()

    cur.execute("INSERT INTO MESSDATEN (TIMESTAMP,LUFTFEUCHTIGKEITDRIN,TEMPERATURDRIN,LUFTFEUCHTIGKEITAUSEN,TEMPERATURAUSEN,ERDFEUCHTIGKEIT,LUX) \
        VALUES (%s, %s, %s, %s, %s, %s, %s)", (my_date.strftime(fmt) ,luftfeuchtigkeitdrin, temperaturdrin, luftfeuchtigkeitausen, temperaturausen, erdfeuchtigkeit, lux)) 

    conn.commit()
    print ("Records created successfully")
    conn.close()

Code: Alles auswählen

... # SQL aehnlich SELECT timestamp, wert FROM ....
zeitpunkte = []
werte = []
for zeitpunkt, wert in cursor:
      zeitpunkte.append(zeitpunkt)
      werte.append(wert)

Code: Alles auswählen

zeitpunkte, werte = zip(*cursor)

Code: Alles auswählen

PLOTS = {
    'luftfeuchtigkeit': {
        'title': 'Luftfeuchtigkeit',
        'ylabel': 'Luftfeuchtigkeit in %',
        'plots': [('Luftfeuchtigkeit innen'), ('Luftfeuchtigkeit ausen')]
    },
    'temperatur': {
        'title': 'Temperatur',
        'ylabel': 'Temperatur in °C',
        'plots': [("Temperatur innen"), ("Temperatur ausen")]
    },
    'erdfeuchtigkeit': {
        'title': 'Erdfeuchtigkeit',
        'ylabel': 'Erdfeuchtigkeit in %',
        'plots': [('Erdfeuchtigkeit')]
    },
    'lux': {
        'title': 'Lichtitensität',
        'ylabel': 'Lichtitensität in Lux',
        'plots': [('Lichtitensität(lux)')]
    },
}

@app.route('/image/<filename>', methods=['GET', 'POST'])
def data(filename):
    data_one = filename.split('.')[0]
    
    conn = psycopg2.connect()
    print ("Opened database successfully")

    cur = conn.cursor()

    cur.execute("SELECT TIMESTAMP, LUFTFEUCHTIGKEITDRIN, TEMPERATURDRIN, LUFTFEUCHTIGKEITAUSEN, TEMPERATURAUSEN, ERDFEUCHTIGKEIT, LUX  from MESSDATEN")

    rows = cur.fetchall()

    time = []
    wertone = []
    werttwo = []
    rowone = 1
    rowtwo = 2
    plot = PLOTS[data_one]

    if data_one == 'luftfeuchtigkeit':
        rowone = 1
        rowtwo = 3
    elif data_one == 'temperatur' :
        rowone = 2
        rowtwo = 4
    elif data_one == 'erdfeuchtigkeit' :
        rowone = 5
    elif data_one == 'lux' :
        rowone = 6
    else :
        print ("error")

    for row in rows:
        time.append(row[0])
        wertone.append(row[rowone])
        if data_one == 'luftfeuchtigkeit' or data_one == 'temperatur':
            werttwo.append(row[rowtwo])

    fig, ax = plt.subplots()
    if data_one == 'luftfeuchtigkeit' or data_one == 'temperatur':
        for label in plot['plots']:
            ax.plot(time, wertone, werttwo, label=label)
    else:
        for label in plot['plots']:
            ax.plot(time, wertone, label=label ) 
    ax.grid(True)
    fig.legend()
    ax.set_title(plot['title'])
    ax.set_ylabel(plot['ylabel'])
    fig.autofmt_xdate()

    buffer = io.BytesIO()
    plt.savefig(buffer, format = 'png')
    plot_data = buffer.getvalue()

    response = make_response(plot_data)
    response.headers['Content-type'] = 'image/png'
    return response