Can Excel SQL query get metadata out of calibredb?

[chaley]

Originally Posted by unboggling

Wouldn't it be nice to say to calibre users (particularly power users working with huge datasets) who might want or need it: there is this capabilty to work the SQL thing, and here is how to do it?

Actually, it scares me. I can see the reports now.
1) My database is hosed -- what do I do?
2) What did you do?
3) I was testing an Excel macro that changes the author names in the DB to base64.
4) Oh...

Updating calibre's database from outside of calibre is a bad idea. Unfortunately I don't see any way to prevent it, other than hope that the missing trigger functions would abort the update.

[unboggling]

I hear you and can imagine the potential headaches. I wasn't even thinking about updating calibredb from outside. I was researching if there might be a better way for me to work with the calibredb data only for the purpose of printing or saving a formatted table of metadata, as opposed to the unformatted catalog.csv.

[Starson17]

Originally Posted by unboggling

I was researching if there might be a better way for me to work with the calibredb data only for the purpose of printing or saving a formatted table of metadata, as opposed to the unformatted catalog.csv.

If you only want to extract and save data from metadata.db, the simplest way is to use sqlite3 and a script. In Windows, I could use this batch file to get a list of all book titles sent to test1.txt:

Echo ON
Echo Calibre metadata.db test ...
Echo .output test1.txt                  > sqlscript.scr
Echo SELECT title FROM books;    >> sqlscript.scr
Echo .quit                       >> sqlscript.scr
SQLITE3 METADATA.DB < sqlscript.scr

There are lots of SQL commands for selection, sorting, etc.

[unboggling]

Thanks, Starson. I think I'll go with something like that, at least using SQLite shell.

[Maggie-Me]

Originally Posted by unboggling

Great, thanks. Now I'm off to try a build from the ODBCdriver source code.

Another way to do this... not sure how you would do in Mac but in Windows a lot of times I will have text or csv dumps that I need to query.

1st. I start a new Access database and link to the text(csv) file, you only have to do this once since every time the file is changed it is pointed to since you are linking and not importing (set up a folder so they are in the same place so you can keep track of)

2nd. In Excel set up a data connetion to that Access database, then you can run any query anytime you want on it, as far as Excel is concerned it is a database

[unboggling]

M-M, thanks. I'll look doing something like that.

[garcle]

Another approach may be to use one of the myriad SQLite GUI's available out there.
I use http://www.sqliteexpert.com/

But there are many others available check out:-

http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

[unboggling]

@garcle, woohoo!, gui's for a Mac user, thanks, I didn't know about them. sqliteexpert isn't available for OS X. Your other link helped. I downloaded free SQLiteStudio which seems easy enough for a (adjectives deleted) Mac user such as myself to use to get me started.

@anyone, I have some newbie questions.

1. On OSX 10.6.6 on a 2 year old intel mac, does it matter if I use 32 bit or 64 bit apps? I gather 64 bit is better but really don't understand why.

2. Re SQLite, if I want to create my own DBs, can I do it with the SQLite that's part of the calibre install, or do I need to DL another copy of SQLite, or set up something so the one instance can deal with multiple different DBs simultaneously? What solution would not interfere with calibre?

3. Re my noobness with unix system admin and Mac OS X unix in specific. Can anyone recommend any good books or web sites for me to learn from? (I had many problems trying to install MySQL (and then PostgreSQL) last night until I stopped when I realized I'd better learn what I'm doing first.)

4. Re MySQL or PostgreSQL, I got also got confused about using the server version vs using the non-server version. (I assume I don't need both.) I'm a hobbyist, not an enterprise. Any help for my confusion?

[Starson17]

Originally Posted by unboggling

I downloaded free SQLiteStudio which seems easy enough for a (adjectives deleted) Mac user such as myself to use to get me started.

There are lots of SQLite GUIs out there and most will let you run SQL commands and save them. The script I gave you is how I do it via script/command line

2. Re SQLite, if I want to create my own DBs, can I do it with the SQLite that's part of the calibre install, or do I need to DL another copy of SQLite, or set up something so the one instance can deal with multiple different DBs simultaneously? What solution would not interfere with calibre?

I don't know why you would want to set up a different DB, as that's what Calibre is for, and it provides lots of options for new fields, etc. That said, if you just want access to the data in Calibre's DB, you can use a separate SQLite access program (that's what I do with the script I posted, using sqlite3.exe) or you can issue SQL commands through Calibre's command line tools. There are sample posts on how to do that. You might want to look at some of my posts about using SQL to do searches for duplicate titles.

I'd avoid changing anything in metadata.db. You won't cause problems if you just read it for any data you want.

[unboggling]

Starson, thanks for the clarification. For now I'm going to put this SQL thing on hold until I learn enough system admin on OSX to do complicated installs (for me anyway) like mySQL, and learn enough to build from source (such as the ODBC for SQLite discussed above.) I still want to get that ODBC working for me on OS X, and do pretty-table printing and referencing from Excel.