How to load data stored in a text file into a PostgreSQL database via Python

Question

I have a text file that contains data in the following format:

char char char char
#1   a    b    c

char char char dateTime
#2   d    e    20-12-2012
#3   g    h    8-12-2013

I have created 2 tables in PostgreSQL: one with the following datatype structure: (varchar, varchar, varchar, varchar) (for the #1 a b c record).

The second table that is supposed to hold the rest of the data and has been designed with the following datatype structure: (varchar,varchar,varchar, dateTime). Now, what I'd like to do is load the data given above into my PostgreSQL DB. However, since I am new to PostgreSQL, I am not sure how to load data stored in a text file into the database. Could anybody please give me suggestions on how I should go about doing this?

You'll need a DB API adapter for PostgreSQL. Pyscopg2 is a popular choice for this. The documentation there should get you going. — Pedro Romano
– Pedro Romano, Commented Apr 29, 2013 at 7:48

Craig Ringer · Accepted Answer · 2013-04-29 11:58:38Z

2

Use the Python csv module or a similar tool to read the file data in as rows of data.

Once you have the data as rows (lists or tuples) in Python, you can create a new connection using a Python DBI driver like psycopg2. Establish a connection to PostgreSQL, prepare a statement and execute it once with each data row.

There's lots more info all over the 'net; search for "psycopg tutorial" to start. Here's one article, though I haven't done anything more than skim read it.

answered Apr 29, 2013 at 11:58

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to load data stored in a text file into a PostgreSQL database via Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related