1 安裝 psycopg2

pip install psycopg2

2、連接數(shù)據(jù)庫

每條完整的sql執(zhí)行步驟如下；

建立連接獲得 connect 對象

獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作，非線程安全，多個應(yīng)用會在同一個連接種創(chuàng)建多個光標；

書寫sql語句

調(diào)用execute()方法執(zhí)行sql

抓取數(shù)據(jù)（可選操作）

提交事物

關(guān)閉連接

# -*- coding: utf-8 -*- import psycopg2 # 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象 cursor = conn.cursor() # sql語句 sql = "SELECT VERSION()" # 執(zhí)行語句 cursor.execute(sql) # 獲取單條數(shù)據(jù). data = cursor.fetchone() # 打印 print("database version : %s " % data) # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 conn.close()

輸出結(jié)果打印出數(shù)據(jù)庫版本說明連接數(shù)據(jù)庫成功：

database version : PostgreSQL 11.3, compiled by Visual C++ build 1914, 64-bit

三 創(chuàng)建表

創(chuàng)建學(xué)生表主要有字段id?唯一標識，字段?num?代表學(xué)號，字段?name?代表學(xué)生姓名；詳細的建表默認規(guī)則轉(zhuǎn)換見附錄

# -*- coding: utf-8 -*- import psycopg2 # 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作 cursor = conn.cursor() # sql語句 建表 sql = """CREATE TABLE student ( id serial4 PRIMARY KEY, num int4, name varchar(25));""" # 執(zhí)行語句 cursor.execute(sql) print("student table created successfully") # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 conn.close()

四 插入操作

4.1 插入數(shù)據(jù)一

知識追尋者提供的第一種防止sql注入的插入數(shù)據(jù)方式（具有占位符的預(yù)編譯sql），重要程度不言而喻；美中不足是字符串類型必須帶上單引號；

# -*- coding: utf-8 -*- import psycopg2 # 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作 cursor = conn.cursor() # sql語句 建表 sql ="INSERT INTO student (num, name) \VALUES (%s, '%s')" % \(100, 'zszxz') # 執(zhí)行語句 cursor.execute(sql) print("successfully") # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 conn.close()

4.2 插入數(shù)據(jù)二（參數(shù)分離）

知識追尋者認為下面參數(shù)與sql語句分離插入的姿勢更簡便帥氣，也是防止sql注入問題；強烈推薦；

# -*- coding: utf-8 -*- import psycopg2 # 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作 cursor = conn.cursor() # sql語句 建表 sql ="""INSERT INTO student (num, name) VALUES (%s, %s)""" params = (101, 'zszxz') # 執(zhí)行語句 cursor.execute(sql,params) print("successfully") # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 conn.close()

4.3 插入數(shù)據(jù)三（字典）

第三種姿勢也就是是支持字典映射關(guān)系插入，使用字典方式的插入數(shù)據(jù)是根據(jù)字典的key進行匹配占位符，強烈推薦；

# -*- coding: utf-8 -*- import psycopg2 # 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作 cursor = conn.cursor() # sql語句 建表 sql ="""INSERT INTO student (num, name) VALUES (%(num)s, %(name)s)""" params = {'num':102, 'name':'zszxz'} # 執(zhí)行語句 cursor.execute(sql,params) print("successfully") # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 conn.close()

五 查詢操作

5.1 查詢一條數(shù)據(jù)

使用fetchone()方法可以抓取一條數(shù)據(jù), 返回的是元組；

# -*- coding: utf-8 -*- import psycopg2 # 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作 cursor = conn.cursor() # sql語句 建表 sql ="""SELECT * FROM student;""" # 執(zhí)行語句 cursor.execute(sql) # 抓取 row = cursor.fetchone() print(row) # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 cursor.close() conn.close()

輸出結(jié)果：

(1, 100, 'zszxz')

5.2 查詢多條數(shù)據(jù)

使用fetchmany([size=cursor.arraysize])方法可以抓取多條數(shù)據(jù)；

此方法可以多次使用，直到數(shù)據(jù)庫中沒有數(shù)據(jù)，此時會返回空列表；

如果不傳參數(shù)，會限制查詢條數(shù)，一般就是返回第一條；

# -*- coding: utf-8 -*- import psycopg2 # 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作 cursor = conn.cursor() # sql語句 建表 sql ="""SELECT * FROM student;""" # 執(zhí)行語句 cursor.execute(sql) # 抓取 #row = cursor.fetchone() rows = cursor.fetchmany(2) print(rows) # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 cursor.close() conn.close()

輸出結(jié)果：

[(1, 100, 'zszxz'), (2, 101, 'zszxz')]

5.3 查詢?nèi)繑?shù)據(jù)

使用?fetchall()?方法會抓取所有數(shù)據(jù)；

# -*- coding: utf-8 -*- import psycopg2 # 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作 cursor = conn.cursor() # sql語句 建表 sql ="""SELECT * FROM student;""" # 執(zhí)行語句 cursor.execute(sql) # 抓取 rows = cursor.fetchall() print(rows) # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 cursor.close() conn.close()

輸出結(jié)果：

[(1, 100, 'zszxz'), (2, 101, 'zszxz'), (3, 102, 'zszxz')]

5.4 按條件查詢

帶參查詢讀者應(yīng)該謹記sql 與 參數(shù) 分離

參數(shù)的末尾必須加上逗號

如果知道返回的數(shù)據(jù)就一條使用fetchone()方法，如果無特殊要求，否則建議使用fetchall()方法

# -*- coding: utf-8 -*- import psycopg2 # 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作 cursor = conn.cursor() # sql語句 建表 sql ="""SELECT * FROM student where id = %s;""" params = (1,) # 執(zhí)行語句 cursor.execute(sql,params) # 抓取 rows = cursor.fetchall() print(rows) # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 cursor.close() conn.close()

輸出結(jié)果：

[(1, 100, 'zszxz')]

六 更新操作

更新操作跟之前的查詢，插入類似，參數(shù)對應(yīng)的文章分清楚即可。

# -*- coding: utf-8 -*- import psycopg2 # 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作 cursor = conn.cursor() # sql語句 建表 sql ="""update student set name = %s where id = %s """ params = ('知識追尋者',3,) # 執(zhí)行語句 cursor.execute(sql,params) # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 cursor.close() conn.close()

七 刪除操作

刪除操作很簡單，看如下代碼，與之前的代碼流程沒什么區(qū)別；

# -*- coding: utf-8 -*- import psycopg2 # 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作 cursor = conn.cursor() # sql語句 建表 sql ="""delete from student where id = %s """ params = (3,) # 執(zhí)行語句 cursor.execute(sql,params) # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 cursor.close() conn.close()

八 異常處理

處理 sql 的異常非常重要，知識追尋者這邊使用psycopg2的?Error?進行異常捕獲，能捕獲到sql執(zhí)行時期的所有異常；下面代碼中表test是庫中不存的表，執(zhí)行sql后會報異常，經(jīng)過異常捕獲后非常美觀，不影響程序運行；

# -*- coding: utf-8 -*- import psycopg2 # 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作 cursor = conn.cursor() # sql語句 建表 sql ="""select * from test""" params = (3,) try:# 執(zhí)行語句cursor.execute(sql,params) except psycopg2.Error as e:print(e) # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 cursor.close() conn.close()

執(zhí)行結(jié)果

錯誤: 關(guān)系 "test" 不存在 LINE 1: select * from test

九 打印sql

使用cursor.query?可以查看執(zhí)行的sql語句，方便排查；

# -*- coding: utf-8 -*- import psycopg2 # 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作 cursor = conn.cursor() # sql語句 建表 sql ="""select * from student""" try:# 執(zhí)行語句cursor.execute(sql,)que = cursor.queryprint(que) except psycopg2.Error as e:print(e) # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 cursor.close() conn.close()

執(zhí)行結(jié)果：

b'select * from student'

十 獲取總條數(shù)

使用cursor.rowcount 可以獲得表中所有行總數(shù)；

# 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作 cursor = conn.cursor() # sql語句 建表 sql ="""select * from student""" # 執(zhí)行語句 cursor.execute(sql) count = cursor.rowcount print(count) # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 conn.close()

輸出

2

十一顯示行號

使用cursor.rownumber 可以顯示當(dāng)前查詢sql獲得數(shù)據(jù)的行號，每抓取一次光標的索引就會加1；

# 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作 cursor = conn.cursor() # sql語句 建表 sql ="""select * from student """ # 執(zhí)行語句 cursor.execute(sql) row_1 = cursor.fetchone() print(cursor.rownumber) row_2 = cursor.fetchone() print(cursor.rownumber) # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 conn.close()

輸出結(jié)果：

1 2

十二 顯示執(zhí)行參數(shù)

使用?mogrify(operation[,?parameters]) 能夠顯示執(zhí)行語句的參數(shù)綁定結(jié)果，返回的是字符串形式；

# 獲得連接 conn = psycopg2.connect(database="python", user="postgres", password="123456", host="127.0.0.1", port="5432") # 獲得游標對象，一個游標對象可以對數(shù)據(jù)庫進行執(zhí)行操作 cursor = conn.cursor() # sql語句 建表 sql ="""INSERT INTO student (num, name) VALUES (%s, %s)""" params = (102, '知識追尋者') # 執(zhí)行語句 result = cursor.mogrify(sql,params) print(result.decode('UTF-8')) cursor.execute(sql,params) # 事物提交 conn.commit() # 關(guān)閉數(shù)據(jù)庫連接 conn.close()

執(zhí)行結(jié)果：

INSERT INTO student (num, name) VALUES (102, '知識追尋者')

十三 附錄

支持默認的類型轉(zhuǎn)換如下,如果想要使用強制類型轉(zhuǎn)換，詳細的可以參照pgsql官網(wǎng)手冊；

PythonPostgreSQL

None	NULL
bool	bool
float	real，double
int，long	smallint，integer，bigint
Decimal	numeric
str，unicode	varchar，text
buffer，memoryview，bytearray，bytes，Buffer protocol	bytea
date	date
time	time,timetz
datetime	timestamp,timestamptz
timedelta	interval
list	ARRAY
tuple,namedtuple	Composite typesIN?syntax
dict	hstore
Range	range
UUID	uuid
Anything	json
ipaddress	inet

?

?

?

總結(jié)

以上是生活随笔為你收集整理的使用python操作postgresql 查询的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。