This PR adds following supports:

• Table.delete return True or False. This can handle the case that try to delete not existed row/column.
• add Table.__repr__

Hello there,

I always get following error

Traceback (most recent call last): File "./compile_results.py", line 58, in for i in table_send: File "/usr/local/lib/python2.7/dist-packages/dataset/persistence/util.py", line 73, in next return self.next() File "/usr/local/lib/python2.7/dist-packages/dataset/persistence/util.py", line 67, in next if not self._next_chunk(): File "/usr/local/lib/python2.7/dist-packages/dataset/persistence/util.py", line 58, in _next_chunk chunk = self.result_proxy.fetchmany(self.step) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/result.py", line 997, in fetchmany self.cursor, self.context) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception exc_info File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause reraise(type(exception), exception, tb=exc_tb) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/result.py", line 990, in fetchmany l = self.process_rows(self._fetchmany_impl(size)) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/result.py", line 907, in _fetchmany_impl return self.cursor.fetchmany(size) sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) SQL logic error or missing database

if I try to access dataset number 5000 of my sqlite database.

I am importing two databases into memory with about 60000 rows, which works. I can also access the row 5000 (here id 10000) without error. However, as soon as the sqlite database reaches the for loop at this position, the whole python program crashes with the error shown above. So it seems like the database is working and got the imported data correctly (otherwise I could not access it before the for loop) - but something weird happens then. Any ideas?

Thank you very much for your help and great product :)

# import json from sender into memory database
db_send = dataset.connect('sqlite:///:memory:')
table_send = db_send['record']
with open(sendfile) as send:
    s = json.load(send)
table_send.insert_many(s["results"])
send.close()

# import json from receiver into memory database
db_recv = dataset.connect('sqlite:///:memory:')
table_recv = db_recv['record']
with open(recvfile) as recv:
    r = json.load(recv)
table_recv.insert_many(r["results"])
recv.close()

# init variables for matching counter
count_search=0
count_found=0

#print len(table_send)
#test = table_send.find_one(id=9998)
#print test['id']
#test = table_send.find_one(id=10000)
#print test['id']
#test = table_send.find_one(id=10002)
#print test['id']

# iterate through sender results, set file as sent
# match checksum of sent files to received files
# if matches, include time details of receiver and set received
# update results
for i in table_send:
        print i['id']
        i['file_sent'] = 1
        count_search += 1
        res_recv = table_recv.find_one(filechecksum=i['filechecksum'])
        i['file_sent'] = 1
        if res_recv != None:
                count_found += 1
                #print i['filechecksum'] + ':' + res_recv['filechecksum']
                i['r_time_start'] = res_recv['time_start']
                i['r_time_end'] = res_recv['time_end']
                i['r_time'] = res_recv['time']
                i['file_received'] = 1
        table_send.update(i,['id'])

I'm getting logging messages appearing in my script output. How do I silence them? And why are they even there in the first place?

>>> import dataset
>>> dt = dataset.connect('sqlite:///:memory:')
>>> table = dt['test']
DEBUG:dataset.persistence.database:Creating table: test on Engine(sqlite:///:memory:)
>>> table.insert({"id": "bv56fzi", "name": "This will fail"})
DEBUG:dataset.persistence.table:Creating column: name (<class 'sqlalchemy.types.UnicodeText'>) on 'test'

Updated the input_many and update_many functions so that an iterator rows input will be handled properly, as was mentioned by @pudo in my last pull request (#298). It may be a bit over-complicated, so may require modification.

It is useful to be able to store Numpy arrays in as a large binary object in a database. Here is an implementation that uses Numpy's native binary format to convert an array to a binary string and put it into the database. When reading entries from the database, there is a function that checks if the binary string starts with \x93NUMPY (which is the first 6 bytes of any numpy array) and converts the string back to a numpy array.

If I connect to a database using the with statement like this:

with dataset.connect(path_to_database) as db:
    pass

Does the connection to database close automatically once we are outside the scope of the with statement? Or should we still explicitly close the connection?

This might be a sqlalchemy thing, but when I try to connect to a postgres db with the following (example) url:

import dataset
db_url = "postgres://drsuess:c@[email protected]:5432/lorax"
db = dataset.connect(db_url)

The connection attempt just freezes indefinitely. I tried changing the password and it remedied the situation, but you might want to add a check / warning for this.

On my postgresql database (9.3.2), executing the quickstart examples works when connection schema is not specified:

>>>import dataset
>>>db = dataset.connect()
>>>table = db['person']
>>>print(table.columns)
['id']
>>>table.insert(dict(name='John Doe', age=46))
1

However, when connection schema is specified, the created table is not found when using insert - it seems to be looking instead in the current search_path:

>>>import dataset
>>>db = dataset.connect(schema="myschema")
>>>table = db['person']
>>>print(table.columns)
['id']
>>>table.insert(dict(name='John Doe', age=46))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/dataset/persistence/table.py", line 66, in insert
    self._ensure_columns(row, types=types)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/dataset/persistence/table.py", line 198, in _ensure_columns
    self.create_column(column, _type)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/dataset/persistence/table.py", line 224, in create_column
    Column(name, type)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/alembic/operations.py", line 365, in add_column
    schema=schema
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/alembic/ddl/impl.py", line 127, in add_column
    self._exec(base.AddColumn(table_name, column, schema=schema))
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/alembic/ddl/impl.py", line 76, in _exec
    conn.execute(construct, *multiparams, **params)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1651, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 717, in execute
    return meth(self, multiparams, params)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 67, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 771, in _execute_ddl
    compiled
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 927, in _execute_context
    context)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1076, in _handle_dbapi_exception
    exc_info
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 920, in _execute_context
    context)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 425, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation "person" does not exist
 'ALTER TABLE person ADD COLUMN age INTEGER' {}

After a clean install of OSX Mojave 10.14.2 I can no longer PIP install Dataset into a Anaconda Python3.6.5 environment. The error below lists GCC as a problem during the build. I attempted to install GCC again using Homebrew to no avail. I then wiped and reinstalled Mojave with the same results.

I also attempted to PIP install Dataset into the root system environment with the same results.

I can install other python modules into both the root system and Anaconda environments.

Any direction on getting Dataset working again would be greatly appreciated. Errors below:

Collecting dataset Using cached https://files.pythonhosted.org/packages/ba/76/5b8fe80f487bfa90b8d0094c3a27150fe797b1fbe02dec31b048fd52448d/dataset-1.1.0-py2.py3-none-any.whl Collecting sqlalchemy>=1.1.0 (from dataset) Requirement already satisfied: six>=1.11.0 in /Users/jz/anaconda3/envs/bridge/lib/python3.6/site-packages (from dataset) (1.12.0) Collecting normality>=0.5.1 (from dataset) Using cached https://files.pythonhosted.org/packages/7f/38/9ed937afe0fe95a281180d8820d46eb955bc1b0f656f3b301635608a5c1f/normality-1.0.0-py2.py3-none-any.whl Collecting alembic>=0.6.2 (from dataset) Collecting banal>=0.4.1 (from normality>=0.5.1->dataset) Using cached https://files.pythonhosted.org/packages/27/07/376d107ccf0dd80efdd8e4310b1d7d34a2d54f5389f946d58950c6dd70cc/banal-0.4.2-py2.py3-none-any.whl Collecting chardet (from normality>=0.5.1->dataset) Using cached https://files.pythonhosted.org/packages/bc/a9/01ffebfb562e4274b6487b4bb1ddec7ca55ec7510b22e4c51f14098443b8/chardet-3.0.4-py2.py3-none-any.whl Collecting pyicu>=1.9.3 (from normality>=0.5.1->dataset) Using cached https://files.pythonhosted.org/packages/c2/15/0af20b540c828943b6ffea5677c86e908dcac108813b522adebb75c827c1/PyICU-2.2.tar.gz Collecting Mako (from alembic>=0.6.2->dataset) Requirement already satisfied: python-dateutil in /Users/jz/anaconda3/envs/bridge/lib/python3.6/site-packages (from alembic>=0.6.2->dataset) (2.7.5) Collecting python-editor>=0.3 (from alembic>=0.6.2->dataset) Collecting MarkupSafe>=0.9.2 (from Mako->alembic>=0.6.2->dataset) Using cached https://files.pythonhosted.org/packages/da/fc/2979c425ad23d528d6ac2e1f3efdc28e572fa1e1fbd5a75171cbdd7ddaa5/MarkupSafe-1.1.0-cp36-cp36m-macosx_10_6_intel.whl Building wheels for collected packages: pyicu Running setup.py bdist_wheel for pyicu: started Running setup.py bdist_wheel for pyicu: finished with status 'error' Complete output from command /Users/jz/anaconda3/envs/bridge/bin/python -u -c "import setuptools, tokenize;file='/private/var/folders/8v/wnw63nls49572vj7073gzn4w0000gn/T/pycharm-packaging/pyicu/setup.py';f=getattr(tokenize, 'open', open)(file);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, file, 'exec'))" bdist_wheel -d /private/var/folders/8v/wnw63nls49572vj7073gzn4w0000gn/T/pip-wheel-q_nht7jo --python-tag cp36:

Building PyICU 2.2 for ICU 58.2

Adding CXXFLAGS="-I/Users/jz/anaconda3/include" from /Users/jz/anaconda3/bin/icu-config Adding LDFLAGS="-Wl,-rpath,/Users/jz/anaconda3/lib -L/Users/jz/anaconda3/lib -licui18n -licuuc -licudata" from /Users/jz/anaconda3/bin/icu-config running bdist_wheel running build running build_py creating build creating build/lib.macosx-10.7-x86_64-3.6 copying PyICU.py -> build/lib.macosx-10.7-x86_64-3.6 creating build/lib.macosx-10.7-x86_64-3.6/icu copying icu/init.py -> build/lib.macosx-10.7-x86_64-3.6/icu warning: build_py: byte-compiling is disabled, skipping.

running build_ext building '_icu' extension creating build/temp.macosx-10.7-x86_64-3.6 gcc -Wno-unused-result -Wsign-compare -Wunreachable-code -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -I/Users/jz/anaconda3/envs/bridge/include -arch x86_64 -I/Users/jz/anaconda3/envs/bridge/include -arch x86_64 -I/Users/jz/anaconda3/envs/bridge/include/python3.6m -c _icu.cpp -o build/temp.macosx-10.7-x86_64-3.6/_icu.o -DPYICU_VER="2.2" -I/Users/jz/anaconda3/include warning: include path for stdlibc++ headers not found; pass '-std=libc++' on the command line to use the libc++ standard library instead [-Wstdlibcxx-not-found] In file included from _icu.cpp:27: ./common.h:38:13: error: unknown type name 'decltype' typedef decltype(nullptr) nullptr_t; ^ ./common.h:38:30: error: expected ';' after top level declarator typedef decltype(nullptr) nullptr_t; ^ ; In file included from _icu.cpp:27: In file included from ./common.h:106: In file included from /Users/jz/anaconda3/include/unicode/unistr.h:33: /Users/jz/anaconda3/include/unicode/std_string.h:35:10: fatal error: 'string' file not found #include ^~~~~~~~ 1 warning and 3 errors generated. error: command 'gcc' failed with exit status 1

---

Running setup.py clean for pyicu Failed to build pyicu Installing collected packages: sqlalchemy, banal, chardet, pyicu, normality, MarkupSafe, Mako, python-editor, alembic, dataset Running setup.py install for pyicu: started Running setup.py install for pyicu: finished with status 'error' Complete output from command /Users/jz/anaconda3/envs/bridge/bin/python -u -c "import setuptools, tokenize;file='/private/var/folders/8v/wnw63nls49572vj7073gzn4w0000gn/T/pycharm-packaging/pyicu/setup.py';f=getattr(tokenize, 'open', open)(file);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, file, 'exec'))" install --record /private/var/folders/8v/wnw63nls49572vj7073gzn4w0000gn/T/pip-record-yz3yw6d5/install-record.txt --single-version-externally-managed --compile:

Building PyICU 2.2 for ICU 58.2

Adding CXXFLAGS="-I/Users/jz/anaconda3/include" from /Users/jz/anaconda3/bin/icu-config
Adding LDFLAGS="-Wl,-rpath,/Users/jz/anaconda3/lib -L/Users/jz/anaconda3/lib -licui18n -licuuc -licudata" from /Users/jz/anaconda3/bin/icu-config
running install
running build
running build_py
creating build
creating build/lib.macosx-10.7-x86_64-3.6
copying PyICU.py -> build/lib.macosx-10.7-x86_64-3.6
creating build/lib.macosx-10.7-x86_64-3.6/icu
copying icu/__init__.py -> build/lib.macosx-10.7-x86_64-3.6/icu
warning: build_py: byte-compiling is disabled, skipping.

running build_ext
building '_icu' extension
creating build/temp.macosx-10.7-x86_64-3.6
gcc -Wno-unused-result -Wsign-compare -Wunreachable-code -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -I/Users/jz/anaconda3/envs/bridge/include -arch x86_64 -I/Users/jz/anaconda3/envs/bridge/include -arch x86_64 -I/Users/jz/anaconda3/envs/bridge/include/python3.6m -c _icu.cpp -o build/temp.macosx-10.7-x86_64-3.6/_icu.o -DPYICU_VER="2.2" -I/Users/jz/anaconda3/include
warning: include path for stdlibc++ headers not found; pass '-std=libc++' on the command line to use the libc++ standard library instead [-Wstdlibcxx-not-found]
In file included from _icu.cpp:27:
./common.h:38:13: error: unknown type name 'decltype'
    typedef decltype(nullptr) nullptr_t;
            ^
./common.h:38:30: error: expected ';' after top level declarator
    typedef decltype(nullptr) nullptr_t;
                             ^
                             ;
In file included from _icu.cpp:27:
In file included from ./common.h:106:
In file included from /Users/jz/anaconda3/include/unicode/unistr.h:33:
/Users/jz/anaconda3/include/unicode/std_string.h:35:10: fatal error: 'string' file not found
#include <string>
         ^~~~~~~~
1 warning and 3 errors generated.
error: command 'gcc' failed with exit status 1

----------------------------------------

Failed building wheel for pyicu Command "/Users/jz/anaconda3/envs/bridge/bin/python -u -c "import setuptools, tokenize;file='/private/var/folders/8v/wnw63nls49572vj7073gzn4w0000gn/T/pycharm-packaging/pyicu/setup.py';f=getattr(tokenize, 'open', open)(file);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, file, 'exec'))" install --record /private/var/folders/8v/wnw63nls49572vj7073gzn4w0000gn/T/pip-record-yz3yw6d5/install-record.txt --single-version-externally-managed --compile" failed with error code 1 in /private/var/folders/8v/wnw63nls49572vj7073gzn4w0000gn/T/pycharm-packaging/pyicu/

This PR adds support of with statement for Database class. Basic usage:

with dataset.connect('...') as db:
        db['...'].insert()

If an exception is raised within the scope of with then all changes are rolled back and exception is passed forward. Otherwise all changes are committed upon leaving with scope.

Tests are added as well.

This PR partially fixes the issue https://github.com/pudo/dataset/issues/90.

The actual problem underlying #96.

If you put a datetime into the database, you get a unicode string back. If you put the unicode string into the database, #96 stings you and it crashes.

(Roundtripping is important for "fetch this row from the DB, mutate it and save it back" scenarios.)

>>> import dataset
>>> db = dataset.connect("sqlite:///:memory:")
>>> table = db['table']
>>> import datetime
>>> table.insert({'datecol': datetime.datetime.now()})
1
>>> for row in table: pass
...
>>> row
OrderedDict([(u'id', 1), (u'datecol', u'2014-06-04 12:05:32.821552')])
>>> type(row['datecol'])
<type 'unicode'>
>>> table.insert({'datecol': row['datecol']})
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/venv/local/lib/python2.7/site-packages/dataset/persistence/table.py", line 67, in insert
    res = self.database.executable.execute(self.table.insert(row))
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1682, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 720, in execute
    return meth(self, multiparams, params)
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 817, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 884, in _execute_context
    None, None)
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1108, in _handle_dbapi_exception
    exc_info
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 880, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 572, in _init_compiled
    param.append(processors[key](compiled_params[key]))
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/type_api.py", line 864, in process
    return process_param(value, dialect)
  File "/home/venv/local/lib/python2.7/site-packages/dataset/persistence/util.py", line 87, in process_bind_param
    return (value / 1000 - self.epoch).total_seconds()
sqlalchemy.exc.StatementError: unsupported operand type(s) for /: 'unicode' and 'int' (original cause: TypeError: unsupported operand type(s) for /: 'unicode' and 'int') u'INSERT INTO "table" (datecol) VALUES (?)' []

Importing data into sqlite, the data that I'm reading from source (a dbf file) already has a field called ID. I've tried:

table = db.create_table(table_name, primary_id = 'ID')

but that throws sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) duplicate column name: ID

I also tried lowercase 'id' in case case the field name is case sensitive but still getting the error.

Is this expected behaviour?

Is it possible to increase a value in a table by a certain number without reading last value?

product | quantity
lamp    | 22

I want to directly increase (or decrease) quantity of "lamp" by x

Upserts are a feature here: https://dataset.readthedocs.io/en/latest/

And then a limitation here: https://dataset.readthedocs.io/en/latest/quickstart.html#limitations-of-dataset

Which is it? Please make the documentation consistent.

Code:

import dataset
from decimal import Decimal
from enum import Enum, unique


@unique
class CurrencyEnum(Enum):
    CAD = 'CAD'
    USD = 'USD'


db = dataset.connect('sqlite:///my_database.db')
table = db['money_table']

dirty_money = {'dirty_amount': Decimal('1.23'),
               'dirty_currency': CurrencyEnum.USD}

clean_money = {'clean_amount': float(dirty_money['dirty_amount']),
               'clean_currency': dirty_money['dirty_currency'].value}

table.insert(clean_money)
table.insert(dirty_money)

Clean money can be inserted but not dirty.

/Users/matecsaj/Documents/Projects/nugget_spiders/venv/bin/python /Applications/PyCharm.app/Contents/plugins/python/helpers/pydev/pydevd.py --multiprocess --qt-support=auto --client 127.0.0.1 --port 54496 --file /Users/matecsaj/Library/Application Support/JetBrains/PyCharm2022.2/scratches/scratch.py 
Traceback (most recent call last):
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 210, in raise_
    raise exception
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error binding parameter 0 - probably unsupported type.
[SQL: INSERT INTO money_table (dirty_amount, dirty_currency) VALUES (?, ?)]
[parameters: (Decimal('1.23'), <CurrencyEnum.USD: 'USD'>)]
(Background on this error at: https://sqlalche.me/e/14/rvf5)
Exception during reset or similar
Traceback (most recent call last):
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 757, in _finalize_fairy
    fairy._reset(pool, transaction_was_reset)
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1016, in _reset
    pool._dialect.do_rollback(self)
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 683, in do_rollback
    dbapi_connection.rollback()
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 123145578684416 and this is thread id 4534160832.
Exception closing connection <sqlite3.Connection object at 0x1089e7640>
Traceback (most recent call last):
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 757, in _finalize_fairy
    fairy._reset(pool, transaction_was_reset)
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1016, in _reset
    pool._dialect.do_rollback(self)
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 683, in do_rollback
    dbapi_connection.rollback()
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 123145578684416 and this is thread id 4534160832.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 260, in _close_connection
    self._dialect.do_terminate(connection)
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 689, in do_terminate
    self.do_close(dbapi_connection)
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 692, in do_close
    dbapi_connection.close()
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 123145578684416 and this is thread id 4534160832.

Process finished with exit code 1

Please add support for enums and decimals; otherwise, do type conversions.

Code source: https://dataset.readthedocs.io/en/latest/

import dataset

db = dataset.connect('sqlite:///:memory:')

table = db['sometable']
table.insert(dict(name='John Doe', age=37))
table.insert(dict(name='Jane Doe', age=34, gender='female'))

john = table.find_one(name='John Doe')

Result:

/Users/matecsaj/Documents/Projects/nugget_spiders/venv/bin/python /Applications/PyCharm.app/Contents/plugins/python/helpers/pydev/pydevd.py --multiprocess --qt-support=auto --client 127.0.0.1 --port 53910 --file /Users/matecsaj/Library/Application Support/JetBrains/PyCharm2022.2/scratches/scratch.py 
Traceback (most recent call last):
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such table: sometable

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/dataset/table.py", line 356, in _sync_table
    self.db.op.add_column(self.name, column, self.db.schema)
  File "<string>", line 3, in add_column
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/alembic/operations/ops.py", line 2047, in add_column
    return operations.invoke(op)
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/alembic/operations/base.py", line 399, in invoke
    return fn(self, operation)
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/alembic/operations/toimpl.py", line 154, in add_column
    operations.impl.add_column(table_name, column, schema=schema, **kw)
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/alembic/ddl/impl.py", line 322, in add_column
    self._exec(base.AddColumn(table_name, column, schema=schema))
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/alembic/ddl/impl.py", line 195, in _exec
    return conn.execute(construct, multiparams)
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1380, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1472, in _execute_ddl
    ret = self._execute_context(
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 210, in raise_
    raise exception
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/Users/matecsaj/Documents/Projects/nugget_spiders/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: sometable
[SQL: ALTER TABLE sometable ADD COLUMN name TEXT]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Process finished with exit code 1

When I substitute the following line, it works fine.

db = dataset.connect('sqlite:///mydatabase.db')