From dbf to PostgreSQL with Python

Some time ago I found an interesting database file suffix I never faced before: the .dbf and saw aroung that it was first introduced in 1983 with dBASE II. This article showcases how we can automatically generate the PostgreSQL table and fill it with data using Python and dbfread.

If you need a FREE PostgreSQL database?
🦀 Check Aiven’s FREE plans! 🦀
️ Need to optimize your SQL query with AI? ️
Check Aiven AI database optimizer! Powered by EverSQL

Download a sample dbf file

We can get a sample .dbf file from Infused with the following in our terminal

wget https://github.com/infused/dbf/raw/master/spec/fixtures/cp1251.dbf

Enter fullscreen mode Exit fullscreen mode

This will store a file named cp1251.dbf in the current folder.

Use dbfread to move the data into PostgreSQL

We need to install simpledf with

pip install dbfread

Enter fullscreen mode Exit fullscreen mode

Then we can write a Python script (named convert_bdf_to_sql.py) that opens the sample.dbf file and creates the PostgreSQL DDL and loads the data into a CSV file we can use to populate the database

from dbfread import DBF
import dataset

db = dataset.connect('postgresql://[USER]:[PWD]@[HOST]:[PORT]/[DBNAME]?sslmode=require')
table = db['people']

for record in DBF('cp1251.dbf', lowernames=True):
    table.insert(record)

Enter fullscreen mode Exit fullscreen mode

In the above script we:

• connect to a PostgreSQL instance using
  • [USER]: the username
  • [PWD]: the password
  • [HOST]: the hostname
  • [PORT]: the port
  • [DBNAME]: the database name
• define a table named people that will be created and populated
• insert into the people table all the records in cp1251.dbf

We can then execute it with:

python convert_bdf_to_sql.py

Enter fullscreen mode Exit fullscreen mode

If we now connect to our PostgreSQL database:

psql postgres://[USER]:[PWD]@[HOST]:[PORT]/[DBNAME]?sslmode=require

Enter fullscreen mode Exit fullscreen mode

we can check the people table being populated with:

select * from people;

Enter fullscreen mode Exit fullscreen mode

We can see the data in the table!

 id | rn |                  name
----+----+----------------------------------------
  1 |  1 | амбулаторно-поликлиническое
  2 |  2 | больничное
  3 |  3 | НИИ
  4 |  4 | образовательное медицинское учреждение
(4 rows)

Enter fullscreen mode Exit fullscreen mode

原文链接：From dbf to PostgreSQL with Python