psycopg连接postgresql数据库

安装psycopg：

pip install psycopg[binary]

连接数据库，创建表，插入数据：

import psycopg
conninfo = "postgres://postgres:root@localhost:5432/oeasydb"
with psycopg.connect(conninfo) as conn:
    print("connect!")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS test(
            id serial PRIMARY KEY,
            num integer,
            data text);
    """)
    print("Table test is created!")
    conn.execute(
        "INSERT INTO test(num, data) VALUES (%s, %s)",
        (100, "abc")
    )
    print("Data is inserted!")
    conn.commit()

查询：

import psycopg
conninfo = "postgres://postgres:root@localhost:5432/oeasydb"
with psycopg.connect(conninfo) as conn:
    print("connect!")
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM test;")
        for record in cur.fetchall():
            print(record)

一句话查询：

import psycopg

conninfo = "postgres://postgres:root@localhost:5432/oeasydb"
print(psycopg.connect(conninfo).execute("SELECT * FROM test").fetchall())

传参：

import psycopg
conninfo = "postgres://postgres:root@localhost:5432/oeasydb"
with psycopg.connect(conninfo) as conn:
    with conn.cursor() as cur:
        t = ("2","abc")
        sql = "SELECT * FROM test WHERE id=%s AND data=%s;"
        print(sql)
        cur.execute(sql, t)
        for record in cur.fetchall():
            print(record)
        cur.close()
        conn.close()

import psycopg
conninfo = "postgres://postgres:root@localhost:5432/oeasydb"
with psycopg.connect(conninfo) as conn:
    sql = "INSERT INTO test(num, data) VALUES (%(num)s, %(data)s);"
    print(sql)
    d = {
        'num': 100,
        'data': 'oeasy'
    }
    conn.execute(sql, d)
    conn.commit()