PgSQL数据库学习笔记（三）

数据库 PgSQL

字数统计: 1.5k阅读时长: 6 min

 2022/10/17 

PostgreSQL命令

由于Pgsql数据库的命令在运行时会自动转换为小写英文字母，故命令语句也可以用小写字母编写。

使用pgAdmin工具创建数据库

右键点击batabases，选择弹出的数据库的菜单。

pgAdmin工具界面语言设成中文

左键点击File，弹出的Preferences。

选择左下角的User language，点击右上角的箭头，选择Chinese(Simplified)，点击Save保存。在弹出的Refresh required窗口中点击Refresh保存即可。

pgAdmin创建SQL脚本

在已经创建好的数据库名称中右键点击，选择CREATE 脚本，即可创建SQL脚本。可以在SQL脚本里编写PgSQL语句。

注意：SQL脚本中尽量不要直接点击运行代码。这样在创建过一次表后，直接点击运行代码时会再次运行建表的语句，会报表格已存在的错。通常是选中需要运行的代码块后再点击执行按钮。数据库只运行选中的代码，这样不会重复执行先前写好的代码，若没有选中任何代码，则默认为选中全部。

PgSQL创建数据库

-- create database 数据库名
-- 	   with
--     关于数据库的说明
create database postgres
    with
    owner=postgres -- 该数据库的拥有者
    encoding='UTF8' -- 数据库中使用的字符集编码
 	lc_collate='Chinese (Simplified)_China.936' -- 在数据库中使用的排序规则顺序
    lc_ctype='Chinese (Simplified)_China.936'-- 在数据库中使用的字符分类
    tablespace=pg_default-- 与数据库相关联的表空间名称
    connection limit=-1 -- 数据库允许多少并发连接，-1表示没有限制
    is_template=false; -- 若为真，则具有CREATEDB特权的用户都可以从 这个数据库克隆。如果为假（默认），则只有超级用户或者该数据库的拥有者 可以克隆它。

PgSQL的CRUD语句

create建表

使用create语句创建表：

-- create table 表名(
	-- 属性名 数据类型 条件约束 
-- )
create table rpt_tutorial( 
	id serial primary key not null,-- 建立自增id
	name text not null,-- 建立文本型属性
	age int not null,-- 建立整数型属性
	datetime date not null-- 建立日期型属性
);

create创建复合类型

使用create语句创建复合类型：

1 2	-- create type 复合类型名 as （类型名数据类型) create type profession as (profession char(50));

创建复合类型后，可实现在表中自定义字段名称，其格式为：属性名复合类型名条件约束

insert插入数据

使用insert语句查询数据：

1 2	insert into rpt_tutorial(name,age,datetime,prof) values('Zhangsan',30,'2022-10-14','(teacher)');

read读数据

使用select语句查询数据：

1
2
3

-- select 属性名 form 表名 where 条件约束
select * from rpt_tutorial;
select name from rpt_tutorial where age>=30;

update更新数据

使用update...set语句更新数据：

1 2	-- update 表名 set 更新的属性名=更新的数值 where 条件约束更新的属性名=原数值 update rpt_tutorial set datetime='2022-10-17' where datetime='2022-10-14';

delete&drop删除数据

使用delete或drop语句删除数据：

-- 删除表中的记录
-- delete from 表名 
delete from rpt_tutorial
-- 删除表
-- delete table 表名 
drop table rpt_tutorial
-- 删除复合类型
drop type profession
-- 重置自增id为1，删除所有数据
truncate table rpt_tutorial restart identity;

使用python进行PgSQL数据库连接

Psycopy是针对python的Postgres数据库的适配模块，安装psycopg2可以整合python和Postgres。可在cmd中输入命令进行安装：

1	pip install psycopg2

导入psycopg2模块，并进行sql操作，其语句格式如下。

conn=psycopg2.connect(database="数据库名",user="数据库用户名",password="数据库密码",host="127.0.0.1",port="5432")#127.0.0.1为默认的本地ip地址，port为数据库默认地址
print("Opened database successfully")
cur=conn.cursor()#创建一个cursor对象
cur.execute('''
sql操作语句
);''')
conn.commit()
cur.close()
conn.close()

使用python将csv文本的数据导入导出至pqsql

方法一：

使用psycopg2模块中的copy语句操作文本，由于csv文件默认以逗号分隔单元格，故导入导出时要注意加上或减去逗号：

#读csv文件
#resourcefilenames为csv文件地址
#targettablename为要导入的数据库表名
with open(resourcefilenames,'r',encoding='utf-8') as f:
    next(f)
    cur.copy_from(f,targettablename,sep=',',)
    
#写文本
#targetfilenames为导出的csv文件名
write_head=True
with open(targetfilenames,'w',encoding='utf-8') as f:
    columns=['id','name','age','datetime']
    if write_head:
        s=''
        for k in columns:
            s+=k+','
        s=s[:-1]
        f.write(s+'\n')
        write_head=False    
    cur.copy_to(f,targettablename,sep=',',columns=columns) 
print("Export csv successfully")

方法二：

运用xlrd、xlwt模块导入导出数据：

#导入数据
import xlrd
workbook = xlrd.open_workbook(filename='C:/文件名')#仅支持xls文件
# 需要先指定sheet工作表
table = workbook.sheet_by_name(sheet_name='sheet表名')
# 获取行数
rows = table.nrows
# 获取列数
cols = table.ncols
# 循环获取每行的数据
for row in range(rows):
    for col in range(cols):
        value = table.cell_value(row, col)
        print('第{}行{}列的数据为：{}'.format(row, col, value))
#导出数据
import xlwt
# 创建一个workbook并设置编码
workbook = xlwt.Workbook(encoding = 'utf-8')
# 添加sheet
worksheet = workbook.add_sheet('text2')
# 写入excel, 参数对应 行, 列, 值
for row in range(rows):
    for col in range(cols):
        value = table.cell_value(row, col)
        worksheet.write(row, col, value)
workbook.save('text2.csv')   
#导出数据方法2
def outdata(data,file,sheetname):
    # work_book=xlrd.open_workbook(filename=file)
    work_book=xlwt.Workbook(encoding='utf-8')
    sheet=work_book.add_sheet(sheetname)
    sheet.write(0,0,'id')
    sheet.write(0,1,'name')
    sheet.write(0,2,'age')
    sheet.write(0,3,'datetime')
    for i in range(len(data)):
        for j in range(len(data[i])):
            sheet.write(i+1,j,'%s' % data[i][j])
    work_book.save(file)              
    print("Data output successfully") 
outdata(rows,targetfilenames,'test')

方法三：

运用csv模块导入导出数据

#导入数据
import csv
from re import A
def readfile():
    f = open('文件名', 'r')
    csv_reader = csv.reader(f)
    a=[]
    for row_data in csv_reader:
        a.append(row_data)
        print(a)
    f.close()         
    return a
#导出数据    
a=readfile()
csvFile3 = open('新文件名','w',encoding='utf-8',newline='')
writer2 = csv.DictWriter(csvFile3,fieldnames=['属性名'])
key=a[0]
for t in range(len(key)):
    writer2 = csv.writer(csvFile3)
    writer2.writerow(a[t])
csvFile3.close()

原文作者：唐抉

原文链接：http://tangmenjue.top/2022/10/17/PgSQL%E6%95%B0%E6%8D%AE%E5%BA%93%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0%EF%BC%88%E4%B8%89%EF%BC%89/

发表日期：October 17th 2022, 4:08:18 pm

更新日期：October 17th 2022, 4:08:18 pm

Next Post

Flask Web开发学习笔记（一）
Previous Post

PgSQL数据库学习笔记（二）

CATALOG

1. PostgreSQL命令