存储二进制图片以及取出

存储二进制图片及取出

import psycopg2 as pg

conn = pg.connect(database='testbase', user='postgres', password='111111', host='127.0.0.1', port='5432')
cur = conn.cursor()

sql = 'insert into test_save_img(img) values (%s)';

with open('G:\\test\\1.png', 'rb') as f:
    img_bytea = f.read();
    
# 因为python读的二进制与pgsql中的二进制不是一种类型，所以需要psycopg进行类型转换一些
img_bytea = pg.Binary(img_bytea)
# 注意这里一个参数的时候需要加一个逗号，表示这是一个可迭代类型
cur.execute(sql, (img_bytea,))
conn.commit()
conn.close()

conn = pg.connect(database='testbase', user='postgres', password='111111', host='127.0.0.1', port='5432')
cur = conn.cursor()

sql = 'select * from test_save_img where id = 1';
cur.execute(sql)
rows = cur.fetchall()
for row in rows:
    print(row[0])
    with open(r'G:/test/2.png', 'ab') as f:
        f.write(row[1])
    
conn.close()

向Bytea数组中插值

向其中插入一个Bytea类型的数组。

conn = pg.connect(database='testbase', user='postgres', password='111111', host='127.0.0.1', port='5432')
cur = conn.cursor()

sql = 'insert into test_save_img2 values (%s, array[%s])';

with open('G:\\test\\1.png', 'rb') as f:
    img_bytea = f.read();
    
img_bytea = pg.Binary(img_bytea)
cur.execute(sql, (1, img_bytea))
conn.commit()
conn.close()

然后向其中插值：

conn = pg.connect(database='testbase', user='postgres', password='111111', host='127.0.0.1', port='5432')
cur = conn.cursor()

sql = 'update test_save_img2 set imgs = array[%s]||imgs where id=1';

with open('G:\\test\\1.png', 'rb') as f:
    img_bytea = f.read();
    
img_bytea = pg.Binary(img_bytea)
cur.execute(sql, (img_bytea,))
conn.commit()
conn.close()