Problem and background
I've created a CLI-based application in Python that uses SQLite to store user data. It is installed for one user at a time, and there is no communication with the outside world (except for app updates) and all data is stored locally on the user's computer.
This is tested using Pytest. On my local environment, all tests are passing. However, they're failing on GitHub actions. The problem appears to be with the RETURNING keyword in an SQL query.
Attempts to fix
Upon Googling, I found that a possible fix is to change the OS version (https://stackoverflow.com/a/66467899/10907280). A different place also suggested different SQLite versions (https://sqlite.org/forum/info/a4dde39b614ec0b2). I've tried changing the runner environment in many ways, but it doesn't work.
- I've tried different versions of Ubuntu in the GitHub Actions thing.
- I've tried using the
container:setting, and tried Debian, also Ubuntu 21.04. - Tried different versions of Python.
- I've tried manually building Python, with and without each of
libsqlite3-devandsqlite3apt-get packages, and both using directly make and through pyenv.
Environment info
From using the shell command python -c "import sqlite3; print(sqlite3.version)", both the online environment and my local machine have the same version (2.6.0).
My local system is WSL 2, Ubuntu 21.04. Everything updated to the latest stable release, Python 3.10.4 via pyenv.
Code and errors
Old version of action YAML (this fails):
name: build and release
on: [push]
jobs:
test:
name: pytest
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v2
- uses: actions/setup-python@v2
with:
python-version: '3.10'
- run: pip install -r requirements.txt pytest
- uses: cclauss/[email protected]
- run: pytest
Latest version of the action YAML (this fails):
name: Test, build and release
# whenever a branch or commit is pushed
on: [push]
jobs:
# use pytest
test:
# used to ensure testing is done right
env:
DEVELOPMENT: '1'
runs-on: ubuntu-latest
# to avoid using old sqlite version
container:
image: debian:latest
options: --user root
steps:
# check out repo
- uses: actions/checkout@v2
# prevent from asking user for input
- run: export DEBIAN_FRONTEND=noninteractive
# install recommended tools for building Python
- run: apt -q update
- run: apt -q install make build-essential libssl-dev zlib1g-dev libbz2-dev libreadline-dev libsqlite3-dev wget curl llvm libncursesw5-dev xz-utils tk-dev libxml2-dev libxmlsec1-dev libffi-dev liblzma-dev git sqlite3 -y
- run: apt -q upgrade -y
# install pyenv
- run: curl -L https://github.com/pyenv/pyenv-installer/raw/master/bin/pyenv-installer | bash
- run: exec $SHELL
- run: ~/.pyenv/bin/pyenv update
# install and set up required Python
- run: ~/.pyenv/bin/pyenv install 3.10.2
- run: ~/.pyenv/bin/pyenv virtualenv 3.10.2 npbc
- run: ~/.pyenv/bin/pyenv local 3.10.2/envs/npbc
# print version info (debugging)
- run: ~/.pyenv/shims/python -V
- run: ~/.pyenv/shims/python -c "import sqlite3; print(sqlite3.version)"
# install pip packages
- run: ~/.pyenv/shims/pip install -r requirements.txt pytest
# run test
- run: ~/.pyenv/shims/pytest -vv
Full error output from GitHub Actions.
~/.pyenv/shims/pytest -vv
shell: sh -e {0}
env:
DEVELOPMENT: 1
============================= test session starts ==============================
platform linux -- Python 3.10.2, pytest-7.1.2, pluggy-1.0.0 -- /github/home/.pyenv/versions/3.10.2/envs/npbc/bin/python3.10
cachedir: .pytest_cache
rootdir: /__w/npbc/npbc
collecting ... collected 21 items
test_core.py::test_get_number_of_each_weekday PASSED [ 4%]
test_core.py::test_validate_undelivered_string PASSED [ 9%]
test_core.py::test_undelivered_string_parsing PASSED [ 14%]
test_core.py::test_calculating_cost_of_one_paper PASSED [ 19%]
test_core.py::test_validate_month_and_year PASSED [ 23%]
test_db.py::test_get_papers PASSED [ 28%]
test_db.py::test_get_undelivered_strings PASSED [ 33%]
test_db.py::test_delete_paper PASSED [ 38%]
test_db.py::test_add_paper FAILED [ 42%]
test_db.py::test_edit_paper PASSED [ 47%]
test_db.py::test_delete_string PASSED [ 52%]
test_db.py::test_add_string PASSED [ 57%]
test_db.py::test_save_results FAILED [ 61%]
test_regex.py::test_regex_number PASSED [ 66%]
test_regex.py::test_regex_range PASSED [ 71%]
test_regex.py::test_regex_CSVs PASSED [ 76%]
test_regex.py::test_regex_days PASSED [ 80%]
test_regex.py::test_regex_n_days PASSED [ 85%]
test_regex.py::test_regex_all_text PASSED [ 90%]
test_regex.py::test_delivery_regex PASSED [ 95%]
test_regex.py::test_regex_hyphen PASSED [100%]
=================================== FAILURES ===================================
________________________________ test_add_paper ________________________________
def test_add_paper():
setup_db(DATABASE_PATH, SCHEMA_PATH, TEST_SQL)
known_data = [
(1, 'paper1', 0, 0, 0),
(1, 'paper1', 1, 1, 6.4),
(1, 'paper1', 2, 0, 0),
(1, 'paper1', 3, 0, 0),
(1, 'paper1', 4, 0, 0),
(1, 'paper1', 5, 1, 7.9),
(1, 'paper1', 6, 1, 4),
(2, 'paper2', 0, 0, 0),
(2, 'paper2', 1, 0, 0),
(2, 'paper2', 2, 0, 0),
(2, 'paper2', 3, 0, 0),
(2, 'paper2', 4, 1, 3.4),
(2, 'paper2', 5, 0, 0),
(2, 'paper2', 6, 1, 8.4),
(3, 'paper3', 0, 1, 2.4),
(3, 'paper3', 1, 1, 4.6),
(3, 'paper3', 2, 0, 0),
(3, 'paper3', 3, 0, 0),
(3, 'paper3', 4, 1, 3.4),
(3, 'paper3', 5, 1, 4.6),
(3, 'paper3', 6, 1, 6),
(4, 'paper4', 0, 1, 4),
(4, 'paper4', 1, 0, 0),
(4, 'paper4', 2, 1, 2.6),
(4, 'paper4', 3, 0, 0),
(4, 'paper4', 4, 0, 0),
(4, 'paper4', 5, 1, 1),
(4, 'paper4', 6, 1, 7)
]
> npbc_core.add_new_paper(
'paper4',
[True, False, True, False, False, True, True],
[4, 0, 2.6, 0, 0, 1, 7]
)
test_db.py:150:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
name = 'paper4', days_delivered = [True, False, True, False, False, True, ...]
days_cost = [4, 0, 2.6, 0, 0, 1, ...]
def add_new_paper(name: str, days_delivered: list[bool], days_cost: list[float]) -> None:
"""add a new paper
- do not allow if the paper already exists"""
global DATABASE_PATH
with connect(DATABASE_PATH) as connection:
# check if the paper already exists
if connection.execute(
"SELECT EXISTS (SELECT 1 FROM papers WHERE name = ?);",
(name,)).fetchone()[0]:
raise npbc_exceptions.PaperAlreadyExists(f"Paper \"{name}\" already exists.")
# insert the paper
> paper_id = connection.execute(
"INSERT INTO papers (name) VALUES (?) RETURNING paper_id;",
(name,)
).fetchone()[0]
E sqlite3.OperationalError: near "RETURNING": syntax error
npbc_core.py:410: OperationalError
______________________________ test_save_results _______________________________
def test_save_results():
setup_db(DATABASE_PATH, SCHEMA_PATH, TEST_SQL)
known_data = [
(1, 1, 1, 2020, '04/01/2022 01:05:42 AM', '2020-01-01', 105.0),
(1, 1, 1, 2020, '04/01/2022 01:05:42 AM', '2020-01-02', 105.0),
(2, 2, 1, 2020, '04/01/2022 01:05:42 AM', '2020-01-03', 51.0),
(2, 2, 1, 2020, '04/01/2022 01:05:42 AM', '2020-01-01', 51.0),
(2, 2, 1, 2020, '04/01/2022 01:05:42 AM', '2020-01-05', 51.0)
]
> npbc_core.save_results(
{1: 105, 2: 51, 3: 647},
{
1: set([date(month=1, day=1, year=2020), date(month=1, day=2, year=2020)]),
2: set([date(month=1, day=1, year=2020), date(month=1, day=5, year=2020), date(month=1, day=3, year=2020)]),
3: set()
},
1,
2020,
datetime(year=2022, month=1, day=4, hour=1, minute=5, second=42)
)
test_db.py:291:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
npbc_core.py:339: in save_results
log_ids = {
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
.0 = <dict_keyiterator object at 0x7f135e5b5350>
log_ids = {
> paper_id: connection.execute(
"""
INSERT INTO logs (paper_id, month, year, timestamp)
VALUES (?, ?, ?, ?)
RETURNING log_id;
""",
(paper_id, month, year, timestamp)
).fetchone()[0]
for paper_id in costs.keys()
}
E sqlite3.OperationalError: near "RETURNING": syntax error
npbc_core.py:340: OperationalError
=========================== short test summary info ============================
FAILED test_db.py::test_add_paper - sqlite3.OperationalError: near "RETURNING...
FAILED test_db.py::test_save_results - sqlite3.OperationalError: near "RETURN...
========================= 2 failed, 19 passed in 0.68s =========================
Error: Process completed with exit code 1.
GitHub links
The full codebase is available. At this point, I've completed most of the changes I need to make to the actual code, and push it to GitHub. https://github.com/eccentricOrange/npbc/tree/5c529dacbef0f9a1f8915a49dcca47834204aa09
In this branch, I'm trying to fix the online Pytest: https://github.com/eccentricOrange/npbc/tree/ci-cd-errors
UPDATE: This has now been merged and deleted. Pull request: https://github.com/eccentricOrange/npbc/pull/24
All this is part of an upgrade, tracked in this issue: https://github.com/eccentricOrange/npbc/issues/20
CodePudding user response：
The solution was to manually build SQLite, as from this chain on Reddit: https://www.reddit.com/r/learnprogramming/comments/ulcxr0/comment/i7vryw0/?context=3
This is the final YAML, in the "original" runner instead of a container. Setting the LD_LIBRARY_PATH variable is required to make sure that the linker uses the correct SQLite.
name: Test
on: [push]
jobs:
# test with pytest
test:
name: pytest
runs-on: ubuntu-latest
# used to ensure testing directories are used, not user directories
env:
DEVELOPMENT: '1'
steps:
- uses: actions/checkout@v2
# build SQLite from source, because I need 3.35<=
- run: |
wget https://sqlite.org/2022/sqlite-autoconf-3380500.tar.gz
tar -xvf sqlite-autoconf-3380500.tar.gz
- run: |
./configure
make
sudo make install
export PATH="/usr/local/lib:$PATH"
working-directory: sqlite-autoconf-3380500
# run pytest
- uses: actions/setup-python@v2
with:
python-version: '3.10'
- run: pip install -r requirements.txt pytest
- run: pytest
env:
LD_LIBRARY_PATH: /usr/local/lib