SQL

SQL provides database access to import from and export to Csv files.

To use SQL in greycat, you need to add the sql library and use a specific database module, such as postgres, as follows:

@library("sql");
use postgres;

Postgres

Connect to the database with password authentication:

var db = Postgres::open("127.0.0.1", "5432", "mydb", "username", "password");

The export to Csv, and import, require a CsvFormat object. The CsvFormat object must describe the columns of the table. Using the example from the Postgresq manual:

mydb=> select * from weather ;
     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 Hayward       |      37 |      54 |      | 1994-11-29
(2 rows)

@library("sql");
use postgres;
// for CsvFormat
use io;

// ...
    var fmt = CsvFormat {
        separator: ',',
        columns: [ 
            CsvColumnString {name: "city",}, 
            CsvColumnInteger {name: "temp_lo",}, 
            CsvColumnInteger {name: "temp_hi",},
            CsvColumnFloat {name: "prcp", },
            CsvColumnDate {name: "date", }, ]
    };
    db.exportCSV("weather.csv", "weather", fmt);
// ...

The importCSV and exportQueryCSV work in the same way:

@library("sql");
use postgres;
use io;

// ...
    var fmt = CsvFormat {
        separator: ',',
        string_delimiter: '"',
        columns: [ 
            CsvColumnString {name: "city",}, 
        ]
    };
    db.exportQueryCSV("cities.csv", "select city from weather", fmt);
// ...