Re: octave and postgres examples

[Olaf Till]

On Thu, Jun 20, 2013 at 11:24:33AM +0100, richard wrote:
> On Wed, Jun 19, 2013 at 04:36:10PM -0400, Thomas Grzybowski wrote:
> > Hi,
> > 
> > I have been trying to connect octave to my postgres database using
> > the 'database' Package - obtained from Octave-Forge - SourceForge,
> > and written and maintained by Olaf Till.
> > 
> > I am able to create a connection object ("conn"), as so:
> > 
> > conn = pq_connect(setdbopts("host", "localhost","dbname", "tg",
> > "user", "tg", "password", ""));
> > 
> > I know some SQL, but I am unfamiliar with the octave side of things;
> > I am trying to use the command "pq_exec_params", but the
> > documentation is a little sparse on the syntax.
> > 
> > I could really use some examples of reading and writing tables to
> > and from octave arrays.  If anyone could post some example code that
> > would be very much appreciated.
> > 
> A work in progress hence it is kinda rough ... 

Richard,

you should not construct strings from numbers in order to insert
them. This will lose accuracy in floats, and also it is a pain. There
are two better ways, 1. placeholders, and 2. the copy command.

I'll outline this while commenting on the below. First, some messy
things remain (if you indeed want numbers as column names), but you
can shorten them, too. These are the column specifications for the
float4 columns in 'create table':

octave:1> colspec = cstrcat (sprintf ("E%i float4, ", 1:49), " E50 float4")
colspec = E1 float4, E2 float4, E3 float4, E4 float4, E5 float4, E6 float4, E7 
float4, E8 float4, E9 float4, E10 float4, E11 float4, E12 float4, E13 float4, 
E14 float4, E15 float4, E16 float4, E17 float4, E18 float4, E19 float4, E20 
float4, E21 float4, E22 float4, E23 float4, E24 float4, E25 float4, E26 float4, 
E27 float4, E28 float4, E29 float4, E30 float4, E31 float4, E32 float4, E33 
float4, E34 float4, E35 float4, E36 float4, E37 float4, E38 float4, E39 float4, 
E40 float4, E41 float4, E42 float4, E43 float4, E44 float4, E45 float4, E46 
float4, E47 float4, E48 float4, E49 float4,  E50 float4

and the column specifications for the float4 columns for the 'insert':

octave:2> valspec = cstrcat (sprintf ("E%i, ", 1:49), "E50")
valspec = E1, E2, E3, E4, E5, E6, E7, E8, E9, E10, E11, E12, E13, E14, E15, 
E16, E17, E18, E19, E20, E21, E22, E23, E24, E25, E26, E27, E28, E29, E30, E31, 
E32, E33, E34, E35, E36, E37, E38, E39, E40, E41, E42, E43, E44, E45, E46, E47, 
E48, E49, E50

and the placeholder string (this includes "Augustus", so 51 elements):

octave:2> placeholders = cstrcat (sprintf ("$%i, ", 1:50), "$51")
placeholders = $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, 
$15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, 
$31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, 
$47, $48, $49, $50, $51

after this inevitable mess, things are much easier if you don't
construct strings from numbers, see below:

You can leave this, but
> ##############constants and initial conditions ############################
> # Set up the E matrix, initialise with ones

this is a mistake (makes a 50x50 matrix),
> E= ones(50); 

rather use:

E = ones (1, 50);

> E(1) = 0.03 ; # (rl) Interest on loan =0.05
> E(2) = 0.01 ; # (rd) Interest on Deposit =0.01
> E(3) = 0.1 ;  # (Ow) Omega
> E(4) = 0.16;  # sigma (0.27) *sigma up, inflation up*
> E(5) = 3 ;    # (v) Output accelerator
> E(6) = 300 ;  # (N) Population available to the Economy
> E(7) = 0.03;  # (g) GDP growth rate - *redundant value* 
> E(8) = 300;   # (L) Population employed in the economy *redundant value* 
> E(9) = 900;   # (K) Wealth available to the economy *redundant value*
> E(10)= 1 ;    # (II) Employment ratio "redundant value" = 1
> E(11)= 300;   # (K/v) Current real Capital *redundant value*
> E(12)= 0.01; # (Alpha) rate of change of productivity from Capital and 
> Labour: alpha up, capital up, inflation slightly dn 
> E(13)= 0.0048;  # (Beta) rate of change of Population available to the 
> Economy 
> E(14)= 0.009;  #0.01;  # (Delta) A bias toward negative growth
> E(15)= 0.038461538; # (Torw=1/26) rate of expenditure of Wages
> E(16)= 1.0;   # (Torp) time lag for price changes
> E(17)= 1.0;   # (Torb) time lag for bank
> E(18)= 0.0593; # (PIr) profit rate *redundant value* 
> E(19)= 1.0;   # (Minsky) Speculation occurring within the system *redundant 
> value* 
> E(20)= 1.0;   # (Winf) wage inflation 
> E(21)= 135.0;  # (Tauc) decay time for capital invested productively 
> E(22) =0.95;E(23)=0;E(24)=2;E(25)=-0.04; # constants for wage inflation 
> Phillips function Ph(II,x,y,s,m)
> E(26)=0.04;E(27)=0.04;E(28)=2;E(29)=0;   # constants for Investment Phillips 
> function Inv(PIr,x1,y1,s1,m1)
> E(30)=0.03;E(31)=10;E(32)=100;E(33)=3;   # constants for Firms timelag 
> Phillips function Taurl(PIr,x2,y2,s2,m2)
> E(34)=0.03;E(35)=2;E(36)=-50;E(37)=0.5;  # constants for Bank timelag 
> Phillips function Taulc(PIr,x3,y3,s3,m3)
> E(38)=0.15;E(39)=0.33;E(40)=5;E(41)=0;   # constants for wage timelag 
> Phillips function Winf( ,x4,y4,s4,m4)
> E(42)=15.65;                             # government spending rate on Army 
> E(43)=0.04;                              # government tax revenue pu of GDP 
> (Y)
> E(44)=0.010;                             # interest on government debt
> E(45)=5.60;                              # government stimulus - Emperor 
> spending pa
> E(46)= 6.0;                              # Emperors non-tax income eg Egypt
> E(47)= 1;                                # Inflation divisor for government 
> spending
> E(48)= 0;                              # Government discretionary spending 
> inc welfare

Drop all the following
> ########################################################################
> E00="Augustus";
> E01=E(01); E02=E(02); E03=E(03); E04=E(04); E05=E(05);
> E06=E(06); E07=E(07); E08=E(08); E09=E(09); E10=E(10);
> E11=E(11); E12=E(12); E13=E(13); E14=E(14); E15=E(15);
> E16=E(16); E17=E(17); E18=E(18); E19=E(19); E20=E(20);
> E21=E(21); E22=E(22); E23=E(23); E24=E(24); E25=E(25);
> E26=E(26); E27=E(27); E28=E(28); E29=E(29); E30=E(30);
> E31=E(31); E32=E(32); E33=E(33); E34=E(34); E35=E(35);
> E36=E(36); E37=E(37); E38=E(38); E39=E(39); E40=E(40);
> E41=E(41); E42=E(42); E43=E(43); E44=E(44); E45=E(45);
> E46=E(46); E47=E(47); E48=E(48); E49=E(49); E50=E(50);
until here.

> #####################################################
>  pkg load database   # the autoload for database is unset usually
>   conn = pq_connect (setdbopts ("host", "localhost", "dbname", "rome", 
> "user", "user", "password", "secret"));
> pq_exec_params (conn, "select * from Ematrix01;"); #
>  S = pq_exec_params (conn, "select * from Ematrix01")
> ########################################################
> # num2str(cell2mat(S.data(1,1)));      # strings, numbers and matricies 
>   try,  pq_exec_params (conn, "drop table ematrix01;"); catch
>   end_try_catch

Instead of the following,
> pq_exec_params (conn, "create table ematrix01 (E00 varchar PRIMARY KEY,\
> E01 float4,E02 float4, E03 float4, E04 float4, E05 float4,\
> E06 float4,E07 float4, E08 float4, E09 float4, E10 float4,\
> E11 float4,E12 float4, E13 float4, E14 float4, E15 float4,\
> E16 float4,E17 float4, E18 float4, E19 float4, E20 float4,\
> E21 float4,E22 float4, E23 float4, E24 float4, E25 float4,\
> E26 float4,E27 float4, E28 float4, E29 float4, E30 float4,\
> E31 float4,E32 float4, E33 float4, E34 float4, E35 float4,\
> E36 float4,E37 float4, E38 float4, E39 float4, E40 float4,\
> E41 float4,E42 float4, E43 float4, E44 float4, E45 float4,\
> E46 float4,E47 float4, E48 float4, E49 float4, E50 float4\
> );");
you can use:

pq_exec_params (conn, sprintf ("create table ematrix01 (E00 varchar PRIMARY 
KEY, %s);", colspec))

But the chief point is:

drop all the following, rather use:

pq_exec_params (conn, sprintf ("insert into ematrix01 (E00, %s) values (%s)", 
valspec, placeholders), cat (2, "Augustus", num2cell (E)))

thats all.

You could use 'copy from' instead, making the long placeholder string
unnecessary:

pq_exec_params (conn, sprintf ("copy ematrix01 (E00, %s) from stdin with 
binary;", valspec), setdbopts ("copy_in_from_variable", true, "copy_in_data", 
cat (2, "Augustus", num2cell (E)), "copy_in_types", cat (2, {"varchar"}, repmat 
({"float4"}, 1, 50))))

Olaf

> X = "insert into Ematrix01 (E00";                #) values";
> #X = "insert into Ematrix01 (E01, E02, E03, E04, E05) values";
> XX = ", E01, E02, E03, E04, E05, E06, E07, E08, E09, E10";
> X = cstrcat(X, XX);
> XX = ", E11, E12, E13, E14, E15, E16, E17, E18, E19, E20";
> X = cstrcat(X, XX);
> XX = ", E21, E22, E23, E24, E25, E26, E27, E28, E29, E30";
> X = cstrcat(X, XX);
> XX = ", E31, E32, E33, E34, E35, E36, E37, E38, E39, E40";
> X = cstrcat(X, XX);
> XX = ", E41, E42, E43, E44, E45, E46, E47, E48, E49, E50";
> X = cstrcat(X, XX);
> XX=") values";
> X = cstrcat(X, XX);
> 
> Y = ["(","'" E00"'",];                          #);"];
> j=0; while (j< 42) 
>  a1= num2str(E(j+1));
>  a2= num2str(E(j+2));
>  a3= num2str(E(j+3));
>  a4= num2str(E(j+4));
>  a5= num2str(E(j+5));
>  a6= num2str(E(j+6));
>  a7= num2str(E(j+7));
>  a8= num2str(E(j+8));
>  a9= num2str(E(j+9));
>  a10= num2str(E(j+10));
> YY = ["," a1,", ", a2,", " a3, ", ", a4, ", ", a5, ", ", a6,", ", a7,", " a8, 
> ", ", a9, ", ", a10, ];
> Y = cstrcat(Y, YY);
> j=j+10;
> endwhile
> YY= [");"];
> Y = cstrcat(Y, YY);
> Z = cstrcat(X, Y);
> pq_exec_params (conn, Z);
> 
> HTH
> 
> Richard A Lough
> _______________________________________________
> Help-octave mailing list
> address@hidden
> https://mailman.cae.wisc.edu/listinfo/help-octave

-- 
public key id EAFE0591, e.g. on x-hkp://pool.sks-keyservers.net

signature.asc
Description: Digital signature