2:25 p.m.
From gretl, if I run
? open dsn=polls user=phl --odbc
Connected to ODBC data source 'polls'
? data dem rep query="select democrat,republican from generic" --odbc
? print dem
The symbol 'dem' is undefined
However, if I precede the data command with "smpl 1 228", gretl imports
all 228 rows. Is there a way to import data from SQL that doesn't
require knowing the number of observations in advance? I'd like just to
point gretl at a database, give it a query like the above, and have it
populate the database with all the records it finds? Is that not
possible? It works like that when importing data from CSV files.
Thanks!
Peter
Dr. Peter H. Lemieux
politicsbythenumbers.org
politicsbythenumbers(a)gmail.com
@PeterHLemieux
3:23 p.m.
On Thu, 19 Jul 2018, Peter H. Lemieux wrote:
From gretl, if I run
? open dsn=polls user=phl --odbc
Connected to ODBC data source 'polls'
? data dem rep query="select democrat,republican from generic" --odbc
? print dem
The symbol 'dem' is undefined
However, if I precede the data command with "smpl 1 228", gretl imports all
228 rows. Is there a way to import data from SQL that doesn't require
knowing the number of observations in advance? I'd like just to point gretl
at a database, give it a query like the above, and have it populate the
database with all the records it finds? Is that not possible? It works like
that when importing data from CSV files.
Thanks!
Peter
Hm, I don't think this is possible at present. The problem is that there
is no way of guessing how many rows the table returned by a SQL query
will have until you execute the query itself.
Would it be possible for to initialise your dataset with a large number
of observations (say, like, "nulldata 100000") and then drop the empty
ones?
-------------------------------------------------------
Riccardo (Jack) Lucchetti
Dipartimento di Scienze Economiche e Sociali (DiSES)
Università Politecnica delle Marche
(formerly known as Università di Ancona)
r.lucchetti(a)univpm.it
http://www2.econ.univpm.it/servizi/hpp/lucchetti
-------------------------------------------------------
7:10 p.m.
On 07/19/2018 11:23 AM, Riccardo (Jack) Lucchetti wrote:
On Thu, 19 Jul 2018, Peter H. Lemieux wrote:
> From gretl, if I run
>
> ? open dsn=polls user=phl --odbc
> Connected to ODBC data source 'polls'
> ? data dem rep query="select democrat,republican from generic" --odbc
> ? print dem
> The symbol 'dem' is undefined
>
> However, if I precede the data command with "smpl 1 228", gretl imports
> all 228 rows. Is there a way to import data from SQL that doesn't require
> knowing the number of observations in advance? I'd like just to point
> gretl at a database, give it a query like the above, and have it populate
> the database with all the records it finds? Is that not possible? It
> works like that when importing data from CSV files.
>
> Thanks!
> Peter
Hm, I don't think this is possible at present. The problem is that there is
no way of guessing how many rows the table returned by a SQL query will have
until you execute the query itself.
Would it be possible for to initialise your dataset with a large number
of observations (say, like, "nulldata 100000") and then drop the empty ones?
I thought about trying that. One somewhat inefficient solution would be to
run the query twice, the first time to obtain the number of rows. Then the
client could build the empty data table and execute the query again to fill
it. Given how much caching goes on in DB transactions that approach might
not be all that slow.
You can wrap the input query in another select to get the number of rows:
select count(*) from (input_query) as foo;
so I think it would be relatively easy to add. Unfortunately I don't
program in C, so I don't have the tools to implement this. I'd activate it
with a command-line option to the data command like --fill.
Still, couldn't the client just keep reading records from the DB and adding
them to the open gretl database until it reaches the end? Does it count the
number of rows in a CSV file before reading it?
Peter
10:25 a.m.
On Thu, 19 Jul 2018, Peter H. Lemieux wrote:
I thought about trying that. One somewhat inefficient solution would
be to
run the query twice, the first time to obtain the number of rows. Then the
client could build the empty data table and execute the query again to fill
it. Given how much caching goes on in DB transactions that approach might
not be all that slow.
You can wrap the input query in another select to get the number of rows:
select count(*) from (input_query) as foo;
so I think it would be relatively easy to add. Unfortunately I don't program
in C, so I don't have the tools to implement this. I'd activate it with a
command-line option to the data command like --fill.
Still, couldn't the client just keep reading records from the DB and adding
them to the open gretl database until it reaches the end? Does it count the
number of rows in a CSV file before reading it?
The way I see it, your solution would be fine if you were going to issue
only one SQL query, but what happens if you execute more than one "data"
statement after the "open" command?
The problem I see with your solution is that in general there's no way to
ensure that data don't spill out at the end, so to speak, because you
could issue more that one query, and the second one could yield more data
than the first one.
And then, there's the whole business of ensuring that the rows match. We
added the facility for ODBC import a long time ago; IMHO, if you could use
csv files, the "join" command would give you a much better tool. One
little-known fact is that, in many cases, gretl handles URLs like local
files. For example, the following works
<hansl>
open http://samplecsvs.s3.amazonaws.com/Sacramentorealestatetransactions.csv
</hansl>
which is sort of neat, but nothing to get excited about. OTOH, this is
really, really cool
<hansl>
nulldata 985
join http://samplecsvs.s3.amazonaws.com/Sacramentorealestatetransactions.csv baths
</hansl>
-------------------------------------------------------
Riccardo (Jack) Lucchetti
Dipartimento di Scienze Economiche e Sociali (DiSES)
Università Politecnica delle Marche
(formerly known as Università di Ancona)
r.lucchetti(a)univpm.it
http://www2.econ.univpm.it/servizi/hpp/lucchetti
-------------------------------------------------------
11:50 a.m.
Am 19.07.2018 um 21:10 schrieb Peter H. Lemieux:
On 07/19/2018 11:23 AM, Riccardo (Jack) Lucchetti wrote:
> On Thu, 19 Jul 2018, Peter H. Lemieux wrote:
>
>>
>> ? open dsn=polls user=phl --odbc
>> Connected to ODBC data source 'polls'
>> ? data dem rep query="select democrat,republican from generic" --odbc
>> ? print dem
>> The symbol 'dem' is undefined
>>
>> However, if I precede the data command with "smpl 1 228", gretl
>> imports all 228 rows.
Hi, can I come back to the beginning here? So
you're saying that the
'data' line essentially failed (no 'dem' created), but the first error
message is after the 'print' command? If so, I'd say it's a little bug
concerning the missing error message.
>
> Hm, I don't think this is possible at present. The problem is that
> there is no way of guessing how many rows the table returned by a SQL
> query will have until you execute the query itself.
>
> Would it be possible for to initialise your dataset with a large number
> of observations (say, like, "nulldata 100000") and then drop the
> empty ones?
I thought about trying that. One somewhat inefficient solution would
be to run the query twice,
But these are two different things. Jack suggests to
create a huge
"dummy" dataset on the gretl side, and shrink it to fit afterwards. The
SQL query would still only be run once. So it looks like a practical
workaround, no?
cheers,
sven
2097
days inactive
2102
days old
4 comments
3 participants
participants (3)
-
Peter H. Lemieux -
Riccardo (Jack) Lucchetti -
Sven Schreiber