import psycopg2
from dbfread import DBF
def execute_sql(conn, sql):
# 建立游标,用来执行数据库操作
cursor = conn.cursor()
# 执行SQL命令
cursor.execute(sql)
# 提交SQL命令
conn.commit()
# 关闭游标
cursor.close()
if __name__ == '__main__':
dbf_path = r'./data-use/test.dbf'
table = DBF(dbf_path, encoding='utf-8')
table_sql = 'CREATE TABLE test_area(' # 建表SQL语句
for field in table.fields:
# print(field.name) # 字段名称
table_sql += field.name
if ('N' == field.type):
table_sql += ' int,' # N代表数值型int、float or None 这里可能会报错
elif ('C' == field.type):
table_sql += ' varchar(255),' # 字符串类型
table_sql = table_sql[: -1] + ')'
# 连接到一个指定的数据库
conn = psycopg2.connect(database="db_test", user="postgres", password="postgres", host=localhost, port="5432")
# execute_sql(conn, table_sql) # 执行建表语句
insert_sql = "INSERT INTO test_area(" # 插入SQL语句
for field_name in table.field_names:
insert_sql += field_name + ','
insert_sql = insert_sql[: -1] + ') values('
for record in table:
insert_detail_sql = insert_sql
for v in list(record.values()):
if (int == type(v)):
insert_detail_sql += '{},'.format(v)
elif (str == type(v)):
insert_detail_sql += "'" + v + "',"
insert_detail_sql = insert_detail_sql[: -1] + ')'
execute_sql(conn, insert_detail_sql)
conn.close()
二、dbf中的数据转写到xlsx中;
import dbfread as df
import pandas as pd
dbf_filename = r"C:\Users\Lenovo\Desktop\123.dbf" # dbf文件
xls_filename = r"C:\Users\Lenovo\Desktop\123.xlsx" # 输出路径
data = df.DBF(dbf_filename, encoding='utf-8') # dbf编码为utf-8,如果编码错误可能会乱码
# data = df.DBF(dbf_file_name, encoding='GBK') # dbf编码为GBK,如果编码错误可能会乱码
data = pd.DataFrame(iter(data))
data.to_excel(xls_filename, index=False) # 写入表格中
print('----处理完成----')