search

bvanelli / actualpy

Python API implementation for Actual server - reference https://actualbudget.org/
24 stars 5 forks source link

create_budget raises sqlite3.OperationalError: near "DROP": syntax error #81

Open MariuszBielecki288728 opened 1 day ago

MariuszBielecki288728 commented 1 day ago

Checks

Reproducible example

test_actual.py

@pytest.fixture
def actual():
    with Actual(base_url="http://localhost:6669", password="test", bootstrap=True) as actual:
        actual.create_budget("TestBudget")
        actual.upload_budget()
        # This yield is my idea to setup pytest with actualpy, but I actually not sure if it will work
        yield actual 
        actual.delete_budget() 

def test_actual_connector(actual):
    pass

but would probably reproduce on my env by just:

with Actual(base_url="http://localhost:6669", password="test", bootstrap=True) as actual:
      actual.create_budget("TestBudget")

docker-compose.yml

services:
  actual_server:
    image: docker.io/actualbudget/actual-server:latest
    ports:
      - '6669:5006'
    volumes:
      - ./actual-data:/data
    restart: unless-stopped
docker-compose up -d
pytest .

Log output

@pytest.fixture
    def actual():
        with Actual(base_url="http://localhost:6669", password="test", bootstrap=True) as actual:
>           actual.create_budget("Test Budget")

tests/conftest.py:15: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
venv/lib/python3.12/site-packages/actual/__init__.py:192: in create_budget
    self.run_migrations(migration_files[1:])
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <actual.Actual object at 0x7f90648404d0>
migration_files = ['migrations/1548957970627_remove-db-version.sql', 'migrations/1550601598648_payees.sql', 'migrations/1555786194328_re...rations/1561751833510_indexes.sql', 'migrations/1567699552727_budget.sql', 'migrations/1582384163573_cleared.sql', ...]

    def run_migrations(self, migration_files: list[str]):
        """Runs the migration files, skipping the ones that have already been run. The files can be retrieved from
        .data_file_index() method. This first file is the base database, and the following files are migrations.
        Migrations can also be .js files. In this case, we have to extract and execute queries from the standard JS."""
        conn = sqlite3.connect(self._data_dir / "db.sqlite")
        for file in migration_files:
            if not file.startswith("migrations"):
                continue  # in case db.sqlite file gets passed as one of the migrations files
            file_id = file.split("_")[0].split("/")[1]
            if conn.execute(f"SELECT id FROM __migrations__ WHERE id = '{file_id}';").fetchall():
                continue  # skip migration as it was already ran
            migration = self.data_file(file)  # retrieves file from actual server
            sql_statements = migration.decode()
            if file.endswith(".js"):
                # there is one migration which is Javascript. All entries inside db.execQuery(`...`) must be executed
                exec_entries = js_migration_statements(sql_statements)
                sql_statements = "\n".join(exec_entries)
>           conn.executescript(sql_statements)
E           sqlite3.OperationalError: near "DROP": syntax error

venv/lib/python3.12/site-packages/actual/__init__.py:156: OperationalError

============================================================== warnings summary 
tests/test_actual_connector.py::test_actual_connector
  /home/mbielecki/personal_projects/actual-discord-bot/venv/lib/python3.12/site-packages/actual/__init__.py:168: UserWarning: Creating budgets via actualpy is not recommended due to custom code migrations.
    warnings.warn("Creating budgets via actualpy is not recommended due to custom code migrations.")

tests/test_actual_connector.py::test_actual_connector
  /home/mbielecki/personal_projects/actual-discord-bot/venv/lib/python3.12/site-packages/actual/migrations.py:39: UserWarning: Migration query from migrations cannot be executed due to custom code, it will be skipped. Query:

  INSERT INTO ${table} (id, month, category, amount, carryover) VALUES (?, ?, ?, ?, ?)

    warnings.warn(

tests/test_actual_connector.py::test_actual_connector
  /home/mbielecki/personal_projects/actual-discord-bot/venv/lib/python3.12/site-packages/actual/migrations.py:39: UserWarning: Migration query from migrations cannot be executed due to custom code, it will be skipped. Query:

  INSERT INTO zero_budget_months (id, buffered) VALUES (?, ?)

    warnings.warn(

tests/test_actual_connector.py::test_actual_connector
  /home/mbielecki/personal_projects/actual-discord-bot/venv/lib/python3.12/site-packages/actual/migrations.py:39: UserWarning: Migration query from migrations cannot be executed due to custom code, it will be skipped. Query:

  INSERT INTO notes (id, note) VALUES (?, ?)

    warnings.warn(

-- Docs: https://docs.pytest.org/en/stable/how-to/capture-warnings.html

Issue description

Hello,

I'm trying to use pytest to automatically test my code that is using actual.py. I thought about having Actual server bootstrapped as part of test setup. However I'm encountering an error in create_budget() function.

Using pytest . --showlocals prints some more useful information, includiing rendered SQL query. (that does not actually look wrong to me)

conn       = <sqlite3.Connection object at 0x7fd4b89d2a70>
exec_entries = ['\n'
 'CREATE TABLE zero_budget_months\n'
 '  (id TEXT PRIMARY KEY,\n'
 '   buffered INTEGER DEFAULT 0);\n'
 '\n'
 'CREATE TABLE zero_budgets\n'
 '  (id TEXT PRIMARY KEY,\n'
 '   month INTEGER,\n'
 '   category TEXT,\n'
 '   amount INTEGER DEFAULT 0,\n'
 '   carryover INTEGER DEFAULT 0);\n'
 '\n'
 'CREATE TABLE reflect_budgets\n'
 '  (id TEXT PRIMARY KEY,\n'
 '   month INTEGER,\n'
 '   category TEXT,\n'
 '   amount INTEGER DEFAULT 0,\n'
 '   carryover INTEGER DEFAULT 0);\n'
 '\n'
 'CREATE TABLE notes\n'
 '  (id TEXT PRIMARY KEY,\n'
 '   note TEXT);\n'
 '\n'
 'CREATE TABLE kvcache (key TEXT PRIMARY KEY, value TEXT);\n'
 'CREATE TABLE kvcache_key (id INTEGER PRIMARY KEY, key REAL);\n'
 ';',
 '\n    DROP TABLE spreadsheet_cells;\n    ANALYZE;\n    VACUUM;\n  ;']
file       = 'migrations/1686139660866_remove_account_type.sql'
file_id    = '1686139660866'
migration  = b'BEGIN TRANSACTION;\n\nALTER TABLE accounts DROP COLUMN type;\n\nCOMMIT;\n'
migration_files = ['migrations/1548957970627_remove-db-version.sql',
 'migrations/1550601598648_payees.sql',
 'migrations/1555786194328_remove_category_group_unique.sql',
 'migrations/1561751833510_indexes.sql',
 'migrations/1567699552727_budget.sql',
 'migrations/1582384163573_cleared.sql',
 'migrations/1597756566448_rules.sql',
 'migrations/1608652596043_parent_field.sql',
 'migrations/1608652596044_trans_views.sql',
 'migrations/1612625548236_optimize.sql',
 'migrations/1614782639336_trans_views2.sql',
 'migrations/1615745967948_meta.sql',
 'migrations/1616167010796_accounts_order.sql',
 'migrations/1618975177358_schedules.sql',
 'migrations/1632571489012_remove_cache.js',
 'migrations/1679728867040_rules_conditions.sql',
 'migrations/1681115033845_add_schedule_name.sql',
 'migrations/1682974838138_remove_payee_rules.sql',
 'migrations/1685007876842_add_category_hidden.sql',
 'migrations/1686139660866_remove_account_type.sql',
 'migrations/1688749527273_transaction_filters.sql',
 'migrations/1688841238000_add_account_type.sql',
 'migrations/1691233396000_add_schedule_next_date_tombstone.sql',
 'migrations/1694438752000_add_goal_targets.sql',
 'migrations/1697046240000_add_reconciled.sql',
 'migrations/1704572023730_add_account_sync_source.sql',
 'migrations/1704572023731_add_missing_goCardless_sync_source.sql',
 'migrations/1707267033000_reports.sql',
 'migrations/1712784523000_unhide_input_group.sql',
 'migrations/1716359441000_include_current.sql',
 'migrations/1720310586000_link_transfer_schedules.sql',
 'migrations/1720664867241_add_payee_favorite.sql',
 'migrations/1720665000000_goal_context.sql',
 'migrations/1722717601000_reports_move_selected_categories.js',
 'migrations/1722804019000_create_dashboard_table.js']
self       = <actual.Actual object at 0x7fd4b898c230>
sql_statements = 'BEGIN TRANSACTION;\n\nALTER TABLE accounts DROP COLUMN type;\n\nCOMMIT;\n'

Expected behavior

There is no error during create_budget function

Installed versions

Python 3.12.6 actualpy 0.5.1 Actual server - latest docker tag - https://hub.docker.com/layers/actualbudget/actual-server/latest/images/sha256-4a0c0b71d3eb6c9b68c6922c97af4e5dd258c5a433fed30ef147cba8511a4914?context=explore

other packages in my environment:

aiohappyeyeballs   2.4.2       Happy Eyeballs for asyncio
aiohttp            3.10.8      Async http client/server framework (asyncio)
aiosignal          1.3.1       aiosignal: a list of registered asynchronous callbacks
annotated-types    0.7.0       Reusable constraint types to use with typing.Annotated
anyio              3.7.1       High level compatibility layer for multiple asynchronous event loop implementations
attrs              24.2.0      Classes Without Boilerplate
babel              2.16.0      Internationalization utilities
certifi            2024.8.30   Python package for providing Mozilla's CA Bundle.
cffi               1.17.1      Foreign Function Interface for Python calling C code.
charset-normalizer 3.3.2       The Real First Universal Charset Detector. Open, modern and actively maintained alternative to Chardet.
cogwatch           3.3.1       Automatic hot-reloading for your discord.py (or other supported libaries) command files.
cryptography       43.0.1      cryptography is a package which provides cryptographic recipes and primitives to Python developers.
discord            2.3.2       A mirror package for discord.py. Please install that instead.
discord-py         2.4.0       A Python wrapper for the Discord API
environ-config     24.1.0      Boilerplate-free configuration with env variables.
frozenlist         1.4.1       A list-like structure which implements collections.abc.MutableSequence
greenlet           3.1.1       Lightweight in-process concurrent programming
idna               3.10        Internationalized Domain Names in Applications (IDNA)
multidict          6.1.0       multidict implementation
mypy               1.11.2      Optional static typing for Python
mypy-extensions    1.0.0       Type system extensions for programs checked with the mypy type checker.
proto-plus         1.24.0      Beautiful, Pythonic protocol buffers.
protobuf           5.28.2      
pycparser          2.22        C parser in Python
pydantic           2.9.2       Data validation using Python type hints
pydantic-core      2.23.4      Core functionality for Pydantic validation and serialization
python-dateutil    2.9.0.post0 Extensions to the standard Python datetime module
requests           2.32.3      Python HTTP for Humans.
six                1.16.0      Python 2 and 3 compatibility utilities
sniffio            1.3.1       Sniff out which async library your code is running under
sqlalchemy         2.0.35      Database Abstraction Library
sqlmodel           0.0.22      SQLModel, SQL databases in Python, designed for simplicity, compatibility, and robustness.
typing-extensions  4.12.2      Backported and Experimental Type Hints for Python 3.8+
urllib3            2.2.3       HTTP library with thread-safe connection pooling, file post, and more.
watchfiles         0.15.0      Simple, modern and high performance file watching and code reload in python.
yarl               1.13.1      Yet another URL library
bvanelli commented 21 hours ago

Hello, are you running tests on windows?

MariuszBielecki288728 commented 18 hours ago

@bvanelli, no, it is Ubuntu 20.04.6 LTS