postgresql基本使用_postgres fetchmany-优快云博客

文章目录

glance

import psycopg2


class Psql:
    def __init__(self, dbname, user, password, host='localhost', port=5432):
        dsn = f"dbname={dbname} user={user} password={password} host={host} port={port}"
        self.conn = psycopg2.connect(dsn)
        self.cursor = self.conn.cursor()

    def __del__(self):
        self.cursor.close()
        self.conn.close()

    def select(self, sql_string):
        self.cursor.execute(sql_string)
        return self.cursor.fetchall()

    def run_sql(self, sql_string):
        self.cursor.execute(sql_string)
        self.conn.commit()


if __name__ == '__main__':
    psql = Psql('testdb', 'postgres', 'postgres')
    sql_str = """
    drop table if exists testing;
    create table testing(
    id serial primary key,
    name varchar(255),
    age int
    )
    """
    psql.run_sql(sql_str)
    psql.run_sql("insert into testing(name,age)values('xiaoming',23)")
    psql.run_sql("insert into testing(name,age)values('xiaohong',21)")
    res = psql.select("select * from testing")
    print(res)

数据库操作

su - postgres

psql

\l  列出数据库
\c  连接到数据库
\d  列出表信息

CREATE DATABASE "testdb" WITH ENCODING = 'UTF8';

DROP DATABASE testdb;

表格操作

创建表

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低温度
    temp_hi         int,           -- 最高温度
    prcp            real,          -- 湿度
    date            date
);

CREATE TABLE cities (
    name            varchar(80),
    location        point
);

删除表

DROP TABLE tablename;

插入数据

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

你还可以使用COPY从文本文件中装载大量数据。这种方式通常更快，因为COPY命令就是为这类应用优化的，只是比 INSERT少一些灵活性。比如：

COPY weather FROM '/home/user/weather.txt';

查询

SELECT DISTINCT city
    FROM weather
    ORDER BY city;

连接查询

SELECT *
    FROM weather w, cities c
    WHERE w.city = c.name;

子查询：

SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

分组查询

SELECT city, max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'            -- (1)
    GROUP BY city
    HAVING max(temp_lo) < 40;
    
理解聚集和SQL的WHERE以及HAVING子句之间的关系对我们非常重要。WHERE和HAVING的基本区别如下：WHERE在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）， 而HAVING在分组和聚集之后选取分组行。因此，WHERE子句不能包含聚集函数； 因为试图用聚集函数判断哪些行应输入给聚集运算是没有意义的。相反，HAVING子句总是包含聚集函数（严格说来，你可以写不使用聚集的HAVING子句， 但这样做很少有用。同样的条件用在WHERE阶段会更有效）。

更新

UPDATE weather
    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
    WHERE date > '1994-11-28';

删除

DELETE FROM weather WHERE city = 'Hayward';

python驱动 psycopg2

基本

pip install psycopg2-binary

import psycopg2
dsn = "dbname=test user=postgres password=secret host=localhost port=5432"
conn = psycopg2.connect(dsn)

conn.close()

conn = psycopg2.connect(DSN)

with conn:
    with conn.cursor() as curs:
        curs.execute(SQL1)

with conn:
    with conn.cursor() as curs:
        curs.execute(SQL2)

conn.close()

cursor

fetchone()
fetchall()
fetchmany()
for i in cursor.executee(SQLSTR)：

any 优于 in

You can use a Python list as the argument of the IN operator using the PostgreSQL ANY operator.

ids = [10, 20, 30]
cur.execute("SELECT * FROM data WHERE id = ANY(%s);", (ids,))

Furthermore ANY can also work with empty lists, whereas IN () is a SQL syntax error.

踩坑指南

serial类型自增

drop TABLE if EXISTS xxx;
create table xxx(
    id serial PRIMARY KEY,
    name varchar(255)
);

强删 database

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='fsfs' AND pid<>pg_backend_pid();
drop DATABASE fsfs;