From dbf to SQL (and PostgreSQL) with Python

- 2 minutes read - 328 words

Some time ago I found an interesting database file suffix I never faced before: the .dbf and saw around 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.

 

👉 Want a FREE PostgreSQL database?👈
🦀 Check Aiven’s FREE plans! 🦀

⚡️ Want to optimize your SQL query with AI? ⚡️
Check Aiven SQL Query Optimizer!

⚡️ Want a Fully PostgreSQL optimized Database? ⚡️
Check Aiven AI Database Optimizer!
Powered by EverSQL, it provides you index and SQL rewrite recommendations to take your database performance to the next level

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

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

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)

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

If we now connect to our PostgreSQL database:

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

we can check the people table being populated with:

select * from people;

We can see the data in the table!

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