1、准备数据
2、配置好postgre
3、python写入
import psycopg2 as ps
import os
import glob
def getallpngs(file_dir):
file_lists=[]
for i,j,k in os.walk(file_dir):
file_list=glob.glob(os.path.join(i,"*.png"))
file_lists=file_lists+file_list
return file_lists
def getconn():
return ps.connect(database=database,user=user,password=password,host=host,port=port)
def create_table(tablename,sql):
conn = getconn()
cur = conn.cursor()
try:
cur.execute(sql)
conn.commit()
cur.close()
conn.close()
print("{}数据库创建成功!!!".format(tablename))
except Exception as e:
print("{}数据库表已存在!!!".format(tablename))
def insert_data(tablename,data):
conn = getconn()
cur = conn.cursor()
key_list = []
value_list=[]
sql = '''insert into {}(%s) values(%s);'''.format(tablename)
for k,v in data.items():
if k!='id' and k!='tablename':
key_list.append(k)
value_list.append('%%(%s)s' % k)
sql = sql % (','.join(key_list),','.join(value_list))
try:
cur.execute(sql,data)
conn.commit()
except:
conn.rollback()
cur.close()
conn.close()
def query(sql):
conn= getconn()
cursor = conn.cursor()
cursor.execute(sql)
class_list = cursor.fetchall()
cursor.close()
conn.close()
return class_list
if __name__ == "__main__":
tablename="img_test"
sql = "create table {} (name text NOT NULL, content bytea NOT NULL);".format(tablename)
create_table("table_tdt",sql)
image_dir = './data'
file_lists=getallpngs(image_dir)
for fileone in file_lists:
with open(fileone,'rb') as reader:
buffer = reader.read()
data={}
data['name']=fileone
data['content']=ps.Binary(buffer)
insert_data(tablename,data)
4、数据库中是这样的