Sql for making a lot of change in Calibre database

[Pinguin44]

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:

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"')

When i call this script on terminal, i get this:

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$

Perhaps i miss something somewhere !

Please help me to correct this little py script or give me another working solution to use Sql request with calibre.

Thank you.

Bernard

[kovidgoyal]

You absolutely should not use SQL to make changes to the calibre database.

Do this instead:

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})

Using SQL to change authors will, for instance, mean that the filenames of the actual ebook files will not be changed to match the new author names. The values of author_sort in the authors table will not be changed and so on. Generally speaking, calibre's database is not a wrapper around sqlite. It has far more features than sqlite, so using SQL directly will cause all sorts of problems.

[kovidgoyal]

And if you wish to directly rename an author:

author_id = db.get_item_id('authors', 'KH.SCHEER')
db.rename_items('authors', {author_id: 'Karl H. Scheer'})

which will automatically take care of renaming files and doing all other housekeeping for you.

[Pinguin44]

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

[BetterRed]

Originally Posted by Pinguin44

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 !

The Manage Authors feature and Quality Check PI might be of assistance in straightening out disjoint author names after which it should be relatively easy to use the now 'established' name. There's a Tweak for how initials are to be dealt with.

Originally Posted by Pinguin44

Ok easy to say but not to do... Perhaps someone can build that kind of extension.

Most extensions/plug-ins are built by the first person who needed it

BR

[chaley]

Originally Posted by BetterRed

The Manage Authors feature and Quality Check PI might be of assistance in straightening out disjoint author names after which it should be relatively easy to use the now 'established' name. There's a Tweak for how initials are to be dealt with.

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.

[BetterRed]

Originally Posted by chaley

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.

But if you don't know about Manage Authors, QC plugins and Tweaking Initials then... It's not as if they're front and centre. But that's true of Photoshop, Word, Firefox... even my console emulator has more settings than at which you can poke a stick

Besides lashing some SQL and Python together is 'way cooler' than clicking a button or two

BR

[tieum]

Originally Posted by Pinguin44

AttributeError: 'LibraryDatabase2' object has no attribute 'execute'

Hi, I encountered the same issue for a sql update today: after a massive import on my new calibre-server, I wanted to set the timestamp to the published date

If anyone is stuck like I was: you can call execute() through db.conn

e.g.:

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();"

Happy holidays!