% MATLAB 2022a and newer
% make_pg_db.m
psycopg2 = py.importlib.import_module('psycopg2');
conn = psycopg2.connect(host="localhost", port=int64(5432), ...
                        user="Al", password="SuperSecret",  ...
                        database="deliveries");
cursor = conn.cursor();

% delete/create
cursor.execute("drop table if exists rates");
cursor.execute("drop table if exists orders");
cursor.execute("create table rates(item varchar primary key, " + ...
                                   "cost_by_weight float);");
create_orders_table = "create table orders (" + ...
                           "origin varchar,"  + ...
                           "item   varchar,"  + ...
                           "weight float,"    + ...
                           "dest   varchar);";
cursor.execute(create_orders_table);

% populate the rates table
rows = { {'tube'   , 0.46}, ...
         {'package', 0.65}, ...
         {'letter' , 0.16}, ...
         {'card'   , 0.05} };
insert = "insert into rates (item,cost_by_weight) values(%s,%s)";
cursor.executemany(insert, rows);

% load orders data from the text file and insert to orders table
tic;
rows = py.load_orders.readfile('orders.txt');
fprintf('Data read time = %.6f seconds\n', toc)

insert = "insert into orders (origin,item,weight,dest) values(%s,%s,%s,%s)";                                                                                          
tic
batch_size = 10000; % insert this many rows at a time
n_rows = length(rows);
for i = 1: batch_size:n_rows
    i_end = min(i+batch_size-1, n_rows);
    rows_subset = rows(i:i_end);
    cursor.executemany(insert, rows_subset)
    fprintf('inserted %d to %d\n', i, i_end);
end
conn.commit();
fprintf('Data insert time = %.6f seconds\n', toc)
conn.close();
