Crazy's Blog

Posts Tagged ‘bash’

Sqlite and postgres in bash

November 10th, 2009

Recently I needed to query a postgres database and put some of the info in a sqlite database.

At first I wondered how am I going to do that?, but it’s much much easier then you’d think it is.

A small example of a bash script doing just that:

#!/bin/bash
export PGPASSWORD=mypassword
psql="psql -h localhost -U myuser -d mydatabase"
 
sqlite3 sqlitedatabase.db  "CREATE TABLE mytable(Id TEXT, myfield1 TEXT, myfield2 TEXT);"
 
$psql --quiet --no-align --field-separator ' ' -t --c "select id, field1, field2 from mypostgrestable;" | while  read -a Record ;do
  Id=${Record[0]}
  first=${Record[1]}
  second=${Record[2]}
 
  sqlite3 sqlitedatabase.db  "INSERT INTO mytable(Id, myfield1, myfield2) values ('${Id}','${first}','${second}');"
done
 
sqlite3 sqlitedatabase.db  "select * from mytable";

Not much right?
Lets go over it:

First we set our password.
This is done by using the “export PGPASSWORD=mypassword”.
It can also be done in two lines, frist setting the PGPASSWORD and then exporting it.

After this, we create our table.
Note that this creates the database if it does not exist.

Our next, and most important step is to query the postgres database and loop over the result.
This is done by piping(|) the result of the query to the “while read -a Record ;do”. Everything between this and the “done” will be repeated for each record returned by the query.

To read more about using pipes, there is an interesting article you can read here

After we finish the loop, as a check, do a “select *” from the table to see if the inserts worked.