PostgreSQL的学习记录

使用Python操作PSQL——psycopg2包

导入psyconpg2包：

import psycopg2 as psql

1.1、建立连接

主要使用如下函数：

psql.connect(
    dsn=None,
    connection_factory=None,
    cursor_factory=None,
    **kwargs,
)

使用该函数建立连接需要的基本连接参数为：

• dbname：数据库名称；

• database：同样是数据库名称，只做关键字参数使用；

• user：用户名验证；

• password：密码验证；

• host：用于连接数据库服务器的主机地址；

• port：连接的端口号，对于PostgreSQL一般默认为5432；

该函数的使用方法有两种：

• 将连接参数指定为字符串，如下：

connect_psql = psql.connect("""dbname = postgres user = postgres password = pword host = localhost""")

• 将连接参数指定为关键字参数，如下：

connect_psql = psql.connect(database = "postgres",
                                user = "postgres",
                                password = "pword",
                                host = "localhost")
#或者
psql_host = {"database":"postgres",
             "user":"postgers",
             "password":"pword",
             "host":"localhost"}
connect_psql = psql.connect(**psql_host)

该函数将返回一个connection对象，该对象常用的基本方法如下：

序号	方法	功能	备注
1	cancel( )	取消当前操作
2	close( )	关闭当前连接	关闭连接后当前连接将无法再使用
3	commit( )	将当前的修改、操作提交到数据库
4	cursor( )	获得一个新游标
5	fileno( )	返回connection连接的文件描述符
6	get_backend_pid( )	获得后端的进程ID
7	get_dsn_parameters( )	获取当前连接的有效连接参数
8	get_parameter_status(parameter)	查询/获取后端服务器的参数
9	reset( )	重置当前的连接到默认状态
10	rollback( )	回滚当前数据库的所有更改

需要注意的是，在连接建立后，执行完操作后，需要及时提交并关闭连接，若操作失败则需要执行相应的回退操作之后也需要关闭连接，大致模板如下：

##模板一
connect_psql = psql.connect(**psql_host)
cursor_psql = connect_psql.cursor()
try:
    cursor_psql.execute(SQL_statement)
    connect_psql.commit()
except:
    connect_psql.rollback()
finally:
    connect_psql.close()

连接对象一般用作上下文管理器，而一个上下文环境则包装了一个事务。当上下文环境成功退出时，则事务被提交；而上下文环境若异常退出，事务将进行回滚。同时，上下文环境的关闭并不会影响连接对象，一个连接对象可用于多个上下文环境（事务）的操作管理。

那么什么是上下文环境的退出？如下所示，完成了一次提交，退出了with语句即退出了上下文环境，结束了一次事务。

##模板二
try:
	with connect_psql:
    	with connect_psql.cursor() as curs:
        	curs.execute(SQL_statement1)
       
	with connect_psql:
    	with connect_psql.cursor() as curs:
        	curs.execute(SQL_satement2)
except Exception as err:
    print(err)
#退出with语句即结束了一个事务，自动完成提交,无需额外写commit()；
#连接对象会继续保持连接，需要close()语句进行关闭；
#连接对象被关闭后，将无法使用

1.2、游标操作

（1）什么是游标？

• 游标提供了一种对从数据表中检索出的数据进行操作的灵活手段；
• 本质上讲，游标实际上是一种能从包含了多条数据记录的结果集中每次提取一条记录的机制;
• 游标总是与一条SQL选择语句相关联的；
• 游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成；
• 当需要对结果集进行处理时，必须声明一个指向该结果集的游标，该游标充当指针的作用，其可以遍历结果集中的每一行数据，但每次只移动指向一行；
• 概括来讲，SQL的游标是一种临时的数据库对象，其即可以存放从数据库当中获取的选择结果的结果集（数据副本），以可以存储数据库中数据行指针的信息；其提供了一种逐行操作表中数据的方法；
• 从种类上来讲，SQL游标分为Transact_SQL游标、API游标以及客户游标；

（2）创建一个游标对象

cursor_psql = connect_psql.cursor(name=None, 
                                  cursor_factory=None, 
                                  scrollable=None, 
                                  withhold=False)

对于同一连接对象所创建的游标对象相互之间是不隔离的，由一个游标对数据所进行的更改都可以由其他游标对象看到。游标对象所具有的方法，如下表所示：

序号	方法与属性	功能描述	备注
1	close()	立即关闭游标，关闭后游标将无法使用	若使用with语句操作游标，退出语句将自动关闭游标
2	closed	***只读属性***，指示游标的状态	True表示游标处于关闭状态，False表示游标处于打开状态
3	name	***只读属性***，指示游标的名字
4	execute(query,vars =None)	执行数据库操作命令，如基本的增、删、改等	query参数：字符串形式的SQL语句； vars参数：当字符串SQL语句中有占位符%s时，通过该参数传递相应的变量
5	executemany(query,vars_list)	对var_list当中的所有参数批量执行query语句操作	可视为execute（）语句的循环版本，但其执行查询所返回的结果将会被丢弃
6	fetchone()	从查询获得的结果集中获取一行结果	返回一个元组，当没有数据时返回None
7	fetchmany([size=cursor.arraysize])	与fetchone（）相对，一次获取多行数据	返回结果为元组列表，没有数据时为空列表
8	fetchall()	一次性获取当前结果集当中的所有数据行	返回结果为元组列表，没有数据时为空列表
9	rownumber	只读属性，提供结果集当中指针所指行的索引号	行索引是基于0起始的；当无法确定索引时返回None
10	query	只读属性，返回发送到数据库服务器的执行的最后一次语句	以字符串的形式返回
11	statusmessage	只读属性，返回执行上一次命令之后从客户端返回的消息
12	copy_from()	从file文件当中读取数据写入到指定的table表中
13	copy_to()	将table表导出到指定的文件file中
14	copy_expert()	执行提交用户自己编写的COPY语句	执行具有PSQL参数的COPY语句

1.3、示例操作

（1）基本的增加导入操作——单次插入、批量插入

简单的插入单条数据

语句：“INSERT INTO table_name(colnames) values(value_of_col)”

##建立连接
connect_psql = psql.connect(**psql_host)
##首先创建一个表格
with connect_psql:
    with connect_psql.cursor() as curs:
        curs.execute("""
        create table my_db(
        id integer not null,
        student text not null,
        school text,
        family_add text,
        primary key (student)
        )""")

##写入一条数据，数据写入到SQL语句中
with connect_psql:
    with connect_psql.cursor() as curs:
        curs.execute("""
        insert into my_db(id,student,school,family_add) \
        values(2012,'zhang','school1','shanghai')
        """)

##或者，数据使用vars传入
with connect_psql:
    with connect_psql.cursor() as curs:
        curs.execute("""
        insert into my_db(id,student,school,family_add) values(%s,%s,%s,%s)""",
                     vars = (2012,'zhang','school1','shanghai'))
connect_psql.close()

插入多条数据

如果有多条数据时，如果仍像上述一般逐条插入将显得代码冗余，可以采用如下两种方式简化：

data = [(2013,'zhang','school1','shanghai'),
       (2015,'wang','school1','shanghai'),
       (2013,'zhao','school2','shandong'),
       (2014,'li','school3','shanxi')]
##方式1：使用循环操作进行写入
connect_psql = psql.connect(**psql_host)
with connect_psql:
    with connect_psql.cursor() as curs:
        for row in data:
            curs.execute("""
            insert into my_db(id,student,school,family_add) values(%s,%s,%s,%s)
            """,vars = row)
##方式2：使用批量操作
with connect_psql:
    with connect_psql.cursor() as curs:
        curs.executemany("""
            insert into my_db(id,student,school,family_add) values(%s,%s,%s,%s)
            """,vars_list = data)

从其他文件当中导入批量数据

主要使用COPY方法，如下所示，从指定文件中获取数据追加到table表中：

copy_from(file, table, sep='\t', null='\\N', size=8192, columns=None)

其中，

• file，表示为要读取的数据文件，其为文件对象，具有read()、readline（）方法；
• table，数据库中指定的要插入数据的表格名；
• sep，在file文件中数据预期采用的分隔符；
• columns，指定列名进行迭代，其长度与类型要与file文件中的要读取的内容相匹配；默认情况下，将使整个表与文件进行匹配；

***需要注意的是file文件中的数据格式要符合table表的定义，且不能存在表头。因为表头也将作为一行数据读入，有表头时将报错。***

#创建一个文件,从data写入
with open("./data_to_sql.txt","w+") as file:
    for row in data:
        file.write(",".join([str(s) for s in d])+"\n")
        
#从文件中读取数据
with connect_psql:
    with connect_psql.cursor() as curs:
        curs.copy_from(file = open("./data_to_sql.txt","r+"),
                      table = "my_db",
                      sep = ",",
                      columns = ("id","student","school","family_add"))

(2)基本的查看操作

如之前的游标方法，有如下几种方法可以查看游标结果集中查询获得的数据：

• curs.fetchone()：一次获取一行数据；
• curs.fetchmany(size)：一次获得指定规模的数据;
• curs.fetchall()：一次获取游标结果集中的所有数据；

如下所示：

##获取数据
with connect_psql:
    with connect_psql.cursor() as curs:
        curs.execute("select * from my_db where id = 2013")
        rows = curs.fetchall()
        for row in rows:
            if not row:
                ##为空
                break
            print(f"id:{row[0]};student:{row[1]};school:{row[2]};family_add:{row[3]}")
            

(3)基本的数据更改操作

with connect_psql:
    with connect_psql.cursor() as curs:
        curs.execute("update my_db set school = "good_school",family_add = "No Home" where = 2013")

(4)基本的数据删除操作

with connect_psql:
    with connect_psql.cursor() as curs:
        curs.execute("delete from my_db where = 2013")