mirror of
https://github.com/simonw/sqlite-utils.git
synced 2026-07-26 02:44:33 +02:00
1d584f0c60Documents all breaking changes in 4.0 for both the Python library and CLI: - db["name"] only returns tables, not views - Default float type changed to REAL - convert() no longer skips False values - Table schemas use double quotes - Upsert uses modern SQLite syntax - Type detection now default for CSV/TSV import - sqlite-utils tui moved to plugin - Python 3.10+ required
134 lines
4.4 KiB
ReStructuredText
134 lines
4.4 KiB
ReStructuredText
.. _upgrading:
|
|
|
|
=====================
|
|
Upgrading to 4.0
|
|
=====================
|
|
|
|
sqlite-utils 4.0 includes several breaking changes. This page describes what has changed and how to update your code.
|
|
|
|
Python library changes
|
|
======================
|
|
|
|
db["name"] only returns tables
|
|
------------------------------
|
|
|
|
In previous versions, ``db["table_or_view_name"]`` would return either a :ref:`Table <reference_db_table>` or :ref:`View <reference_db_view>` object depending on what existed in the database.
|
|
|
|
In 4.0, this syntax **only returns Table objects**. Attempting to use it with a view name will raise a ``sqlite_utils.db.NoTable`` exception.
|
|
|
|
**Before (3.x):**
|
|
|
|
.. code-block:: python
|
|
|
|
# This could return either a Table or View
|
|
obj = db["my_view"]
|
|
obj.drop()
|
|
|
|
**After (4.0):**
|
|
|
|
.. code-block:: python
|
|
|
|
# Use db.view() explicitly for views
|
|
view = db.view("my_view")
|
|
view.drop()
|
|
|
|
# db["name"] now only works with tables
|
|
table = db["my_table"]
|
|
|
|
This change improves type safety since views lack methods like ``.insert()`` that are available on tables.
|
|
|
|
db.table() raises NoTable for views
|
|
-----------------------------------
|
|
|
|
The ``db.table(name)`` method now raises ``sqlite_utils.db.NoTable`` if the name refers to a view. Use ``db.view(name)`` instead.
|
|
|
|
Default floating point type is REAL
|
|
-----------------------------------
|
|
|
|
When inserting data with auto-detected column types, floating point values now create columns with type ``REAL`` instead of ``FLOAT``. ``REAL`` is the correct SQLite affinity for floating point values.
|
|
|
|
This affects the schema of newly created tables but does not change how data is stored or queried.
|
|
|
|
convert() no longer skips False values
|
|
--------------------------------------
|
|
|
|
The ``table.convert()`` method previously skipped rows where the column value evaluated to ``False`` (including ``0``, empty strings, and ``None``). This behavior has been removed.
|
|
|
|
**Before (3.x):**
|
|
|
|
.. code-block:: python
|
|
|
|
# Rows with falsey values were skipped by default
|
|
# --skip-false was needed to process all rows
|
|
table.convert("column", lambda x: x.upper(), skip_false=False)
|
|
|
|
**After (4.0):**
|
|
|
|
.. code-block:: python
|
|
|
|
# All rows are now processed, including those with falsey values
|
|
table.convert("column", lambda x: x.upper() if x else x)
|
|
|
|
Table schemas use double quotes
|
|
-------------------------------
|
|
|
|
Tables created by sqlite-utils now use ``"double-quotes"`` for table and column names in the schema instead of ``[square-braces]``. Both are valid SQL, but double quotes are the SQL standard.
|
|
|
|
This only affects how the schema is written. Existing tables are not modified.
|
|
|
|
Upsert uses modern SQLite syntax
|
|
--------------------------------
|
|
|
|
Upsert operations now use SQLite's ``INSERT ... ON CONFLICT SET`` syntax on SQLite versions 3.24.0 and later. The previous implementation used ``INSERT OR IGNORE`` followed by ``UPDATE``.
|
|
|
|
To use the old behavior, pass ``use_old_upsert=True`` to the ``Database()`` constructor:
|
|
|
|
.. code-block:: python
|
|
|
|
db = Database("my.db", use_old_upsert=True)
|
|
|
|
CLI changes
|
|
===========
|
|
|
|
Type detection is now the default
|
|
---------------------------------
|
|
|
|
When importing CSV or TSV data with the ``insert`` or ``upsert`` commands, sqlite-utils now automatically detects column types. Previously all columns were treated as ``TEXT`` unless ``--detect-types`` was passed.
|
|
|
|
**Before (3.x):**
|
|
|
|
.. code-block:: bash
|
|
|
|
# Types were detected only with --detect-types
|
|
sqlite-utils insert data.db mytable data.csv --csv --detect-types
|
|
|
|
**After (4.0):**
|
|
|
|
.. code-block:: bash
|
|
|
|
# Types are detected by default
|
|
sqlite-utils insert data.db mytable data.csv --csv
|
|
|
|
# Use --no-detect-types to treat all columns as TEXT
|
|
sqlite-utils insert data.db mytable data.csv --csv --no-detect-types
|
|
|
|
The ``SQLITE_UTILS_DETECT_TYPES`` environment variable has been removed.
|
|
|
|
convert --skip-false removed
|
|
----------------------------
|
|
|
|
The ``--skip-false`` option for ``sqlite-utils convert`` has been removed. All rows are now processed regardless of whether the column value is falsey.
|
|
|
|
sqlite-utils tui is now a plugin
|
|
--------------------------------
|
|
|
|
The ``sqlite-utils tui`` command has been moved to a separate plugin. Install it with:
|
|
|
|
.. code-block:: bash
|
|
|
|
sqlite-utils install sqlite-utils-tui
|
|
|
|
Python version requirements
|
|
===========================
|
|
|
|
sqlite-utils 4.0 requires Python 3.10 or higher. Python 3.8 and 3.9 are no longer supported.
|