src/adapter_db_test.py - main - eb/python_learn

src/adapter_db_test.py
59 строк2 KB
EvgeniyBudaev
psycopg2
03 мар 2021, 06:26
03 мар 2021, 06:26611bf18
import psycopg2

conn = psycopg2.connect(dbname="testdb", user="postgres", password="root")
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS superheroes")
cur.execute("DROP TABLE IF EXISTS traffic_light")
conn.commit()
cur.execute("CREATE TABLE superheroes (hero_id serial PRIMARY KEY, hero_name varchar, strength int);")
cur.execute("INSERT INTO superheroes (hero_name, strength) VALUES (%s, %s)", ("Superman", 100))
cur.execute("INSERT INTO superheroes (hero_name, strength) VALUES (%s, %s)", ("Flash", 999))
cur.execute("""
            INSERT INTO superheroes (hero_name, strength) 
            VALUES (%(name)s, %(strength)s)
            """, {'name': 'Green Arrow', 'strength': 80})
conn.commit()

cur.execute("CREATE TABLE traffic_light (light_id serial PRIMARY KEY, light text);")
cur.execute("INSERT INTO traffic_light (light) VALUES (%s)", ("red",))
conn.commit()

cur.execute("SELECT * FROM superheroes")
one_line = cur.fetchone()
print(one_line)
full_fetch = cur.fetchall()
for record in full_fetch:
    print(record) # выведется все кроме первого

full_fetch[0][0] # получить первую колонку из первой записи
conn.commit()
cur.close()
conn.close()


# ===
try:
    with psycopg2.connect(dbname="testdb", user="postgres", password="root") as conn:
        with conn.cursor(cursor_factory=RealDictCursor) as curs:
            execute_values(curs, "INSERT INTO traffic_light (light) VALUES %s", [("green",), ("yellow",)])
            curs.execute("SELECT * FROM traffic_light")
            records = curs.fetchall()
            print(records)
            print(records[0]["light"])


    conn = psycopg2.connect(dbname="testdb", user="postgres", password="root")
    with conn:
        with conn.cursor() as curs:
            curs.execute("""
                        UPDATE superheroes
                        SET stregth = %savepoint 
                        WHERE hero_name = %s
                        """, (90, 'Superman'))

    with conn:
        with conn.cursor() as curs:
            curs.execute("SELECT * FROM superheroes")
            print(curs.fetchall())
finally:
    conn.close()