09-14-2014, 07:36 PM | #1 |
Junior Member
Posts: 2
Karma: 10
Join Date: Sep 2014
Device: Kobo Aura
|
Sql for making a lot of change in Calibre database
Hello,
I know that we can made some change on selected items with a regex but that really take too much time for multiple update. The way for me it's to make multiple change on author, title, publisher, tags as a batch with Sql request. So i search on this forum and other source on Google but i don't find so many solution. I found one good way here: https://www.mobileread.com/forums/sho...&highlight=sql I build a short py script for test: Code:
from __future__ import (unicode_literals, division, absolute_import, print_function) from calibre.library.database2 import LibraryDatabase2 db = LibraryDatabase2('/Users/Bernard/Calibre Library Temp') db.execute('UPDATE authors SET name="Karl Herbert Scheer" WHERE name="KH.SCHEER"') db.execute('UPDATE authors SET name="Alfred Elton van Vogt" WHERE name="Alfred E. Van Vogt"') db.execute('UPDATE authors SET name="Alfred Elton van Vogt" WHERE name="AE Van Vogt"') Code:
iMac-de-xxx:~ Bernard$ calibre-debug -e /Users/Bernard/Desktop/calibre-auto-correct.py Python function terminated unexpectedly: 'LibraryDatabase2' object has no attribute 'execute' Traceback (most recent call last): File "/Applications/calibre.app/Contents/Resources/Python/lib/python2.7/site.py", line 208, in main return run_entry_point() File "/Applications/calibre.app/Contents/Resources/Python/lib/python2.7/site.py", line 114, in run_entry_point return getattr(pmod, func)() File "site-packages/calibre/debug.py", line 269, in main File "site-packages/calibre/debug.py", line 200, in run_script File "/Users/Pinguin/Desktop/calibre-auto-correct.py", line 6, in <module> db.execute('UPDATE authors SET name="Karl Herbert Scheer" WHERE name="KH.SCHEER"') AttributeError: 'LibraryDatabase2' object has no attribute 'execute' iMac-de-xxx:~ Bernard$ Please help me to correct this little py script or give me another working solution to use Sql request with calibre. Thank you. Bernard |
09-14-2014, 10:24 PM | #2 |
creator of calibre
Posts: 43,860
Karma: 22666666
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
You absolutely should not use SQL to make changes to the calibre database.
Do this instead: Code:
from calibre.library import db db = db().new_api books = db.search('authors:"=KH.SCHEER"') db.set_field('authors', {book_id:'Karl Herbert Scheer' for book_id in books}) Last edited by kovidgoyal; 09-14-2014 at 11:11 PM. |
09-14-2014, 11:17 PM | #3 |
creator of calibre
Posts: 43,860
Karma: 22666666
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
And if you wish to directly rename an author:
Code:
author_id = db.get_item_id('authors', 'KH.SCHEER') db.rename_items('authors', {author_id: 'Karl H. Scheer'}) |
09-15-2014, 12:27 PM | #4 |
Junior Member
Posts: 2
Karma: 10
Join Date: Sep 2014
Device: Kobo Aura
|
Wow ! Fast and great reply
Thank you very much. I made a test with the db.rename_items function. Everything work fine For all users perhaps it should be great to have an 'auto-correct function' in a futur release of Calibre. Not for all field but just for author and editor. For sample in my library i have: Alfred E. Van Vogt - Alfred E Van Vogt - AE Van Vogt - A.E. Van Vogt - A. E. van Vogt - Alfred Elton van Vogt for the same author ! J.-C. Lattés - JC Lattès - JCL - JCLattès - Jean-Claude Lattès - Editions Jcl for the same editor ! Think the best way is to have a grid with the destination field as 1st column, the strict string to find in column 2 (I don't think that RegEx is useful for that), and to finish the replacement string in column 3. Ok easy to say but not to do... Perhaps someone can build that kind of extension. Thank you again for the working script. Bernard |
09-15-2014, 12:47 PM | #5 | ||
null operator (he/him)
Posts: 20,574
Karma: 26954694
Join Date: Mar 2012
Location: Sydney Australia
Device: none
|
Quote:
Quote:
BR |
||
09-15-2014, 01:33 PM | #6 |
Grand Sorcerer
Posts: 11,742
Karma: 6997045
Join Date: Jan 2010
Location: Notts, England
Device: Kobo Libra 2
|
Exactly what I was going to say. How is building a plugin or SQL scaffolding better than simply right-clicking on the authors in the tag browser and renaming them to what you want? Copy the "correct" name to the clipboard, use the tag browser search feature to find variants, and fix them.
|
09-15-2014, 01:59 PM | #7 | |
null operator (he/him)
Posts: 20,574
Karma: 26954694
Join Date: Mar 2012
Location: Sydney Australia
Device: none
|
Quote:
Besides lashing some SQL and Python together is 'way cooler' than clicking a button or two BR |
|
12-26-2015, 08:40 AM | #8 | |
Junior Member
Posts: 1
Karma: 10
Join Date: Dec 2015
Device: Booken Muse
|
sql update
Quote:
If anyone is stuck like I was: you can call execute() through db.conn e.g.: Code:
calibre-debug -c "from calibre.library.database2 import LibraryDatabase2; db = LibraryDatabase2('/home/path/to/my/calibre-library/'); db.conn.execute('update books set timestamp=pubdate'); db.conn.commit();" |
|
Tags |
calibre, database, request, script, sql |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Making P.Clark library database | SBT | Upload Help | 6 | 04-04-2013 11:43 AM |
What is the best way to defragment the SQL database? | JSWolf | Calibre | 3 | 05-16-2012 08:49 AM |
iPad BusinessInsider: Marvel iPad App Is Stunning And Making A Lot of Money Too | kjk | Apple Devices | 0 | 06-18-2010 12:02 PM |
Using SQL against Calibre database | jjansen | Calibre | 3 | 03-29-2010 10:14 AM |