[sqlite] Using sqlite3.exe as a subprocess
How to detect beginning and end of output from a SQL Statement piped to a subprocess: Initialize the subprocess with .header off .separator .mode list And then delimit your queries - SELECT ' START'; - - SELECT ' END'; Discarding everything not between the expected START and END lines -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Roger Binns Gesendet: Samstag, 16. J?nner 2016 19:36 An: SQLite mailing list Betreff: Re: [sqlite] Using sqlite3.exe as a subprocess -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 15/01/16 13:16, Matthew Allen wrote: > Yeah in hindsight it wasn't the best. I was trying to do: > > while still running: p.communicate etc > > Poll is not the right method to call. It especially is the wrong method in your scenario since you want to dynamically read and write from the subprocess. You'll want stdin/out/err all to be subprocess.PIPE. You'll possibly get lucky with the SQLite shell, but it is a *very* good idea to do the reading of their stdout and writing to their stdin in different threads. The reason is that many of these tools have an loop that looks like this: repeat: - write prompt to stdout - read a command from stdin - write output to stdout, errors to stderr If the output is bigger than the pipe buffer size, then the "write output" step blocks until there is space in the pipe, and will do so before reading the next command. Remember that you don't know when the output is done - in theory you could try to detect the prompt and hope that something similar is not in the data, but that is brittle. If you look at the implementation of the communicate method, you'll see it addresses this issue by using multiple threads (~one per pipe of interest). Since you are using Python 2, another issue you need to be aware of is that the subprocess module is buggy when your python code is multi-threaded. This issue affects non-Windows systems (eg Linux and Mac) and you can end up with the wrong child process being accessed! It isn't theoretical - happened to me two weeks ago and was a pain to realise what had happened and debug. The good news is Google have a fix: https://github.com/google/python-subprocess32 But finally I'd recommend you just avoid all this subprocess stuff. SQLite will quite happily take commands from a script. Consequently you can create a script file like the following and have SQLite execute it (command line parameter): .bail on .open c:\path\to\your\db .output c:\...\tmp\dump.sql .dump .exit You can use the tempfile module to get a temporary filename. The script approach avoids many text vs binary, quoting, encoding, testing and other issues. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v2 iEYEARECAAYFAlaajaQACgkQmOOfHg372QQ68QCeJuZzafMJMAju3F7twPbyToAM XOcAnic84HeEtyTIg/u0r7gbRHt/x/+o =Xyid -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Using sqlite3.exe as a subprocess
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 15/01/16 13:16, Matthew Allen wrote: > Yeah in hindsight it wasn't the best. I was trying to do: > > while still running: p.communicate etc > > Poll is not the right method to call. It especially is the wrong method in your scenario since you want to dynamically read and write from the subprocess. You'll want stdin/out/err all to be subprocess.PIPE. You'll possibly get lucky with the SQLite shell, but it is a *very* good idea to do the reading of their stdout and writing to their stdin in different threads. The reason is that many of these tools have an loop that looks like this: repeat: - write prompt to stdout - read a command from stdin - write output to stdout, errors to stderr If the output is bigger than the pipe buffer size, then the "write output" step blocks until there is space in the pipe, and will do so before reading the next command. Remember that you don't know when the output is done - in theory you could try to detect the prompt and hope that something similar is not in the data, but that is brittle. If you look at the implementation of the communicate method, you'll see it addresses this issue by using multiple threads (~one per pipe of interest). Since you are using Python 2, another issue you need to be aware of is that the subprocess module is buggy when your python code is multi-threaded. This issue affects non-Windows systems (eg Linux and Mac) and you can end up with the wrong child process being accessed! It isn't theoretical - happened to me two weeks ago and was a pain to realise what had happened and debug. The good news is Google have a fix: https://github.com/google/python-subprocess32 But finally I'd recommend you just avoid all this subprocess stuff. SQLite will quite happily take commands from a script. Consequently you can create a script file like the following and have SQLite execute it (command line parameter): .bail on .open c:\path\to\your\db .output c:\...\tmp\dump.sql .dump .exit You can use the tempfile module to get a temporary filename. The script approach avoids many text vs binary, quoting, encoding, testing and other issues. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v2 iEYEARECAAYFAlaajaQACgkQmOOfHg372QQ68QCeJuZzafMJMAju3F7twPbyToAM XOcAnic84HeEtyTIg/u0r7gbRHt/x/+o =Xyid -END PGP SIGNATURE-
[sqlite] Using sqlite3.exe as a subprocess
On 16 January 2016 at 07:16, Roger Binns wrote: > > > while p.poll() == None: resp = p.communicate() print len(resp[0]), > > resp[0] > > That code doesn't make sense. communicate waits until the process > terminates. The SQLite shell won't terminate unless it gets a quit > command, or EOF on stdin. > Yeah in hindsight it wasn't the best. I was trying to do: while still running: p.communicate etc Poll is not the right method to call. > > The problem I'm trying to solve is: When my application that uses > > an sqlite3 database gets the "database disk image is malformed" I > > need to be able to give the user a "repair" option which dumps the > > datrabase to an .sql file and reimport it all. I'm assuming the > > best way is to do that via the shell rather than try and copy all > > the dump code into my own application. > > Good news - here is a shell in Python I already made for you: > > https://rogerbinns.github.io/apsw/shell.html#shell-class > https://github.com/rogerbinns/apsw/blob/master/tools/shell.py Well if I get stuck then I'll have a look at that. But after I sent that message I had a closer look at the available arguments to sqlite3.exe and one of them stood out: -interactive Once I passed that to the command line as well as the database path it started working like a normal process with the stdin and stdout pipes. So I have what I need in terms of being able to control the sqlite3.exe from my application. Regards Matt
[sqlite] Using sqlite3.exe as a subprocess
It seems that sqlite3.exe (console) doesn't work as a subprocess with pipes. I've tried it with both C++ code calling the CreateProcessW win32 API and with python and both resulted in the same behaviour. Which is the sub-process doesn't return anything when I try and read it's output (just hangs). I'm expecting the signon and prompt messages to be printed via stdout and readable by the parent process. Both C++ + python work fine with other binaries (cmd.exe for instance). So I very much doubt it's my code or environment (Win7). Here is my python code: import os import sys import subprocess if 0: p = subprocess.Popen(["C:\\Windows\\system32\\cmd.exe"], stdout=subprocess.PIPE) else: p = subprocess.Popen(["sqlite3.exe", "Database.sqlite"], stdout=subprocess.PIPE) if p is None: print "Error creating process." else: while p.poll() == None: resp = p.communicate() print len(resp[0]), resp[0] I expect there is something funny going on with sqlite3.exe's stdout/stdin. But I'm not immediately clear what that is looking at it's code. Why doesn't it work like other console processes? The problem I'm trying to solve is: When my application that uses an sqlite3 database gets the "database disk image is malformed" I need to be able to give the user a "repair" option which dumps the datrabase to an .sql file and reimport it all. I'm assuming the best way is to do that via the shell rather than try and copy all the dump code into my own application. Based on http://froebe.net/blog/2015/05/27/error-sqlite-database-is-malformed-solved/ Regards Matthew
[sqlite] Using sqlite3.exe as a subprocess
On Fri, Jan 15, 2016 at 4:53 AM, Matthew Allen wrote: > It seems that sqlite3.exe (console) doesn't work as a subprocess with > pipes. > [...] I expect there is something funny going on with sqlite3.exe's > stdout/stdin. Sorry to highjack your thread Matthew, but I have what I consider a related use case. I'd like to embed the SQLite3 shell into another program, both a console program and a gui one, and because I'd like it to access in-memory databases, this cannot be done via forking and pipes. Basically I'd like the shell to have a "Virtual Console Interface" (VCI), to be able to reuse all the shell's goodness, in client apps, w/o having to hack and duplicate the shell's code. With some way to access in-memory databases in the same process as well (a special form of attach or an API?). I realize the shell is not meant and designed to be embedded right now, only the library is, but I'd really like it to be, basically. My own 2016 wishful-thinking feature request :). --DD
[sqlite] Using sqlite3.exe as a subprocess
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 14/01/16 19:53, Matthew Allen wrote: > It seems that sqlite3.exe (console) doesn't work as a subprocess > with pipes. There is a bit of a problem with using apps via pipes. Generally when stdout is a terminal, output will be line buffered (ie you get each line from printf as \n is encountered). However when output is not a terminal then other buffering kicks in. For example it may be in blocks of 4kb, so you'll only see something every time that much has been generated. The Windows standard library is even a bit stranger when not connected to a terminal. For the first 512 bytes of output it will send them immediately, and then switch to block buffers. There are solutions available to try and "trick" the apps to believing they are outputting to a terminal, when it is in fact a pipe. However you won't need them (but shoutout to Expect - a populariser of TCL). > while p.poll() == None: resp = p.communicate() print len(resp[0]), > resp[0] That code doesn't make sense. communicate waits until the process terminates. The SQLite shell won't terminate unless it gets a quit command, or EOF on stdin. > The problem I'm trying to solve is: When my application that uses > an sqlite3 database gets the "database disk image is malformed" I > need to be able to give the user a "repair" option which dumps the > datrabase to an .sql file and reimport it all. I'm assuming the > best way is to do that via the shell rather than try and copy all > the dump code into my own application. Good news - here is a shell in Python I already made for you: https://rogerbinns.github.io/apsw/shell.html#shell-class https://github.com/rogerbinns/apsw/blob/master/tools/shell.py It does require APSW for the database access, as the standard sqlite3 module lacks various functionality. https://rogerbinns.github.io/apsw/pysqlite.html You can add your own repair command based on the existing dump command. This shell aborts on error. The way the standard SQLite shell handles errors (IIRC) is to scan a table forwards (rowid order), and then on encountering the error scans backwards. This is a best effort, but doesn't mean you won't lose lots of data! However I'd recommend you use the backup api and make periodic copies of the database that way. You can then offer going back to a previous snapshot. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v2 iEYEARECAAYFAlaZU68ACgkQmOOfHg372QTvegCgpF/pck6KCjdOqDKhxl5XEyuA cFYAoMdJwpDo5Pwg2uRr/RbNYmEhtdz1 =AR0i -END PGP SIGNATURE-
[sqlite] Using sqlite3.exe as a subprocess
On Jan 14, 2016, at 8:53 PM, Matthew Allen wrote: > >p = subprocess.Popen(["sqlite3.exe", "Database.sqlite"], > stdout=subprocess.PIPE) It looks like you?re trying to use both stdin and stdout, but you really only need stdout here, since sqlite3.exe will accept SQL or sqlite3 shell commands on its command line after the database argument. You don?t need to feed it the commands over stdin. In fact, you don?t even need to use stdout, if you?re willing to leave it all to the command shell: cmd.exe /c sqlite3 Database.sqlite .dump | sqlite3 NewDatabase.sqlite Then replace Database.sqlite with NewDatabase.sqlite, optionally moving the former to a backup location first.
[sqlite] Using sqlite3.exe as a subprocess
On 1/14/16, Matthew Allen wrote: > It seems that sqlite3.exe (console) doesn't work as a subprocess with > pipes. > Yeah it does. The test suite does this, in the shellN.test test scripts (N=1..5, ex: https://www.sqlite.org/src/artifact/ce5e744870387164) Those these are written in TCL, not in Python, and TCL does go through a lot of gyrations to make sure pipes work in a civilized manner. So I'm not saying that this is easy to pull off. But it does work. -- D. Richard Hipp drh at sqlite.org
[sqlite] Using sqlite3.exe as a subprocess
Good day,
Assuming you don't want to alter the code of the shell tool to take a
named pipe (this isn't that difficult to do, unfortunately due to the
business logic I can't go into, it was not allowed):
Have you tried to create a command prompt shell, begin the sqlite
shell tool in that and direct IO to the shell? There is an occasion
(the reasoning for which I will not go into) that we do this in c#.
Yours should be able to pull the same (or similar) trick in c++.
(You should get the gist from this)
System.Diagnostics.Process pIOSql ;
pIOSql = new System.Diagnostics.Process();
pIOSql.StartInfo.CreateNoWindow = true;
pIOSql.StartInfo.UseShellExecute = false;
pIOSql.StartInfo.FileName = PathToDbDirectory + "sqlite3.exe";
pIOSql.StartInfo.Arguments = "\""+PathToDbDirectory + "my.db\"";
pIOSql.StartInfo.RedirectStandardError = true;
pIOSql.StartInfo.RedirectStandardInput = true;
pIOSql.StartInfo.RedirectStandardOutput = true;
pIOSql.Start();
pIOSql.StandardInput.WriteLine("select count(1) from
someTable;\n");
}
.
pIOSql.StandardOutput.DiscardBufferedData();
StreamWriter sCmd = pIOSql.StandardInput;
String sqlcmd = Command;
sCmd.WriteLine(sqlcmd);
etc.
One has to do a bit of work to handle timing. If you aren't worried
(at all) about security then you could even create a temp file, and
stick your queries into it, so you can redirect your output to another
file and funnel everything through .read
Be careful about empty set results!
regards,
Adam DeVita
On Fri, Jan 15, 2016 at 8:32 AM, Dominique Devienne
wrote:
> On Fri, Jan 15, 2016 at 4:53 AM, Matthew Allen wrote:
>
>> It seems that sqlite3.exe (console) doesn't work as a subprocess with
>> pipes.
>> [...] I expect there is something funny going on with sqlite3.exe's
>> stdout/stdin.
>
>
> Sorry to highjack your thread Matthew, but I have what I consider a related
> use case.
>
> I'd like to embed the SQLite3 shell into another program, both a console
> program and a gui one,
> and because I'd like it to access in-memory databases, this cannot be done
> via forking and pipes.
>
> Basically I'd like the shell to have a "Virtual Console Interface" (VCI),
> to be able to reuse all the shell's
> goodness, in client apps, w/o having to hack and duplicate the shell's
> code. With some way to access
> in-memory databases in the same process as well (a special form of attach
> or an API?).
>
> I realize the shell is not meant and designed to be embedded right now,
> only the library is,
> but I'd really like it to be, basically. My own 2016 wishful-thinking
> feature request :). --DD
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
