10

I am wondering if anyone knows a way to generate a connection to a SQLite database in python from a StringIO object.

I have a compressed SQLite3 database file and I would like to decompress it using the gzip library and then connect to it without first making a temp file.

I've looked into the slqite3 library source, but it looks like filename gets passed all the way through into the C code. Are there any other SQLite3 connection libraries that you could use a file ID for? Or is there some why I can trick the builtin sqlite3 library into thinking that my StringIO (or some other object type) is an actual file?

horriblyUnpythonic
  • 853
  • 2
  • 14
  • 34
  • 1
    Uncompress it to a RAM disk perhaps? – Jon Clements Nov 19 '13 at 23:21
  • What's the use case for this? Load some existing data already in sqlite3 format, do some work with it, and discard any changes after? – Jon Clements Nov 19 '13 at 23:42
  • I only need to get a few columns from a compressed database object. I will be working with it in python objects after that. – horriblyUnpythonic Nov 20 '13 at 00:02
  • Short of a tempfile then, what about pickling the sqlite3 data, and then loading that to work with? If it's gzipped, then presumably it's not from a constantly changing data source, so a once off pickling could be feasible... (and could be reasonably small enough to not require compressing) – Jon Clements Nov 20 '13 at 00:03
  • I'm not sure I follow. I don't write and compress the sqlite file, I'm getting it as is from another application. – horriblyUnpythonic Nov 20 '13 at 00:06
  • Umm... so something else is using sqlite3, then gzipping it and you get that? Umm... – Jon Clements Nov 20 '13 at 00:06
  • Might be a very hackish way around it - what OS is it running on? – Jon Clements Nov 20 '13 at 00:43
  • I'm not Windows 7 right now, but it's a reasonable assumption that it could be used on another version of Windows. I can't imagine it will need to work on OSX or Linux. – horriblyUnpythonic Nov 20 '13 at 00:53

1 Answers1

4

The Python sqlite3 module cannot open a database from a file number, and even so, using StringIO will not give you a file number (since it does not open a file, it just emulates the Python file object).

You can use the :memory: special file name to avoid writing a file to disk, then later write it to disk once you are done with it. This will also make sure the file is optimized for size, and you can opt not to write e.g. indexes if size is really a big issue.

Krumelur
  • 31,081
  • 7
  • 77
  • 119