search

stefan-jansen / zipline-reloaded

Zipline, a Pythonic Algorithmic Trading Library
https://zipline.ml4trading.io
Apache License 2.0
1.18k stars 216 forks source link

sqlite3.IntegrityError: NOT NULL constraint failed: equity_symbol_mappings.symbol #174

Closed mbecker911 closed 1 year ago

mbecker911 commented 1 year ago

Dear Zipline Maintainers,

Before I tell you about my issue, let me describe my environment:

Environment

* Operating System: Windows 10 Pro * Python Version: Python 3.10 * Python Bitness: 64 Bit * How did you install Zipline: Pip and Conda * Python packages: alabaster 0.7.12 pyhd3eb1b0_0 alembic 1.9.4 pyhd8ed1ab_0 conda-forge anyio 3.5.0 py310haa95532_0 appdirs 1.4.4 pyh9f0ad1d_0 conda-forge argon2-cffi 20.1.0 py310h2bbff1b_1 arrow 1.2.2 pyhd3eb1b0_0 astroid 2.11.7 py310haa95532_0 atomicwrites 1.4.0 py_0 attrs 21.4.0 pyhd3eb1b0_0 autopep8 1.6.0 pyhd3eb1b0_1 babel 2.9.1 pyhd3eb1b0_0 backcall 0.2.0 pyhd3eb1b0_0 bcolz-zipline 1.2.6 py310h9b08ddd_0 conda-forge bcrypt 3.2.0 py310h2bbff1b_1 beautifulsoup4 4.11.1 py310haa95532_0 binaryornot 0.4.4 pyhd3eb1b0_1 black 22.6.0 py310haa95532_0 blas 1.0 mkl bleach 4.1.0 pyhd3eb1b0_0 blosc 1.21.0 h19a0ad4_1 bottleneck 1.3.5 py310h9128911_0 brotli 1.0.9 h2bbff1b_7 brotli-bin 1.0.9 h2bbff1b_7 brotlipy 0.7.0 py310h2bbff1b_1002 bt 0.2.9 pypi_0 pypi bzip2 1.0.8 he774522_0 ca-certificates 2022.12.7 h5b45459_0 conda-forge certifi 2022.12.7 pyhd8ed1ab_0 conda-forge cffi 1.15.1 py310h2bbff1b_0 chardet 4.0.0 py310haa95532_1003 charset-normalizer 2.0.4 pyhd3eb1b0_0 click 8.0.4 py310haa95532_0 cloudpickle 2.0.0 pyhd3eb1b0_0 cmaes 0.9.1 pypi_0 pypi colorama 0.4.5 py310haa95532_0 colorlog 6.7.0 pypi_0 pypi cookiecutter 1.7.3 pyhd3eb1b0_0 cryptography 37.0.1 py310h21b164f_0 cycler 0.11.0 pyhd3eb1b0_0 cython 0.29.30 py310hd77b12b_0 debugpy 1.5.1 py310hd77b12b_0 decorator 5.1.1 pyhd3eb1b0_0 defusedxml 0.7.1 pyhd3eb1b0_0 diff-match-patch 20200713 pyhd3eb1b0_0 dill 0.3.4 pyhd3eb1b0_0 docutils 0.18.1 py310haa95532_3 empyrical 0.2.2 pypi_0 pypi empyrical-reloaded 0.5.9 pypi_0 pypi en-core-web-sm 2.2.0 pypi_0 pypi entrypoints 0.4 py310haa95532_0 exchange-calendars 4.2.5 pypi_0 pypi ffmpeg 4.3.1 ha925a31_0 conda-forge ffn 0.3.6 pypi_0 pypi fftw 3.3.9 h2bbff1b_1 flake8 4.0.1 pyhd3eb1b0_1 flatbuffers 23.1.21 pypi_0 pypi fonttools 4.25.0 pyhd3eb1b0_0 freetype 2.10.4 hd328e21_0 frozendict 2.3.5 py310h8d17308_0 conda-forge gensim 3.8.0 pypi_0 pypi glib 2.69.1 h5dc1a3c_1 greenlet 2.0.2 py310h00ffb61_0 conda-forge gst-plugins-base 1.18.5 h9e645db_0 gstreamer 1.18.5 hd78058f_0 gym 0.26.2 pypi_0 pypi gym-notices 0.0.8 pypi_0 pypi h5py 3.6.0 py310hfc34f40_0 hdbscan 0.8.29 py310h9b08ddd_1 conda-forge hdf5 1.10.6 nompi_he0bbb20_101 conda-forge html5lib 1.1 pyh9f0ad1d_0 conda-forge icc_rt 2019.0.0 h0cc432a_1 icu 58.2 ha925a31_3 idna 3.3 pyhd3eb1b0_0 imagesize 1.4.1 py310haa95532_0 importlib-metadata 4.11.3 py310haa95532_0 importlib_metadata 4.11.3 hd3eb1b0_0 importlib_resources 5.12.0 pyhd8ed1ab_0 conda-forge inflection 0.5.1 py310haa95532_0 intel-openmp 2021.4.0 haa95532_3556 intervaltree 3.1.0 pyhd3eb1b0_0 ipykernel 6.9.1 py310haa95532_0 ipython 7.31.1 py310haa95532_1 ipython_genutils 0.2.0 pyhd3eb1b0_1 ipywidgets 7.6.5 pyhd3eb1b0_1 iso3166 2.1.1 pyhd8ed1ab_0 conda-forge iso4217 1.9.20220401 pyhd8ed1ab_0 conda-forge isort 5.9.3 pyhd3eb1b0_0 jedi 0.18.1 py310haa95532_1 jellyfish 0.9.0 py310h2bbff1b_0 jinja2 3.0.3 pyhd3eb1b0_0 jinja2-time 0.2.0 pyhd3eb1b0_3 joblib 1.2.0 pyhd8ed1ab_0 conda-forge jpeg 9e h2bbff1b_0 json5 0.9.6 pyhd3eb1b0_0 jsonschema 4.4.0 py310haa95532_0 jupyter 1.0.0 py310haa95532_8 jupyter_client 7.3.4 py310haa95532_0 jupyter_console 6.4.4 py310haa95532_0 jupyter_core 4.10.0 py310haa95532_0 jupyter_server 1.23.4 py310haa95532_0 jupyterlab 3.5.3 py310haa95532_0 jupyterlab_pygments 0.1.2 py_0 jupyterlab_server 2.10.3 pyhd3eb1b0_1 jupyterlab_widgets 1.0.0 pyhd3eb1b0_1 keras 2.11.0 pypi_0 pypi keyring 23.4.0 py310haa95532_0 kiwisolver 1.4.2 py310hd77b12b_0 korean_lunar_calendar 0.3.1 pyhd8ed1ab_0 conda-forge lazy-object-proxy 1.6.0 py310h2bbff1b_0 lerc 3.0 hd77b12b_0 libblas 3.9.0 1_h8933c1f_netlib conda-forge libbrotlicommon 1.0.9 h2bbff1b_7 libbrotlidec 1.0.9 h2bbff1b_7 libbrotlienc 1.0.9 h2bbff1b_7 libcblas 3.9.0 5_hd5c7e75_netlib conda-forge libclang 12.0.0 default_h627e005_2 libdeflate 1.8 h2bbff1b_5 libffi 3.4.2 hd77b12b_4 libiconv 1.16 h2bbff1b_2 liblapack 3.9.0 5_hd5c7e75_netlib conda-forge libogg 1.3.5 h2bbff1b_1 libpng 1.6.37 h2a8f88b_0 libsodium 1.0.18 h62dcd97_0 libspatialindex 1.9.3 h6c2663c_0 libta-lib 0.4.0 hcfcfb64_1 conda-forge libtiff 4.4.0 h8a3f274_0 libvorbis 1.3.7 he774522_0 libwebp 1.2.2 h2bbff1b_0 libxml2 2.9.14 h0ad7f3c_0 libxslt 1.1.35 h2bbff1b_0 logbook 1.5.3 py310h8d17308_7 conda-forge lru-dict 1.1.8 py310h8d17308_1 conda-forge lxml 4.9.1 py310h1985fb9_0 lz4-c 1.9.3 h2bbff1b_1 lzo 2.10 he774522_2 m2w64-gcc-libgfortran 5.3.0 6 conda-forge m2w64-gcc-libs 5.3.0 7 conda-forge m2w64-gcc-libs-core 5.3.0 7 conda-forge m2w64-gmp 6.1.0 2 conda-forge m2w64-libwinpthread-git 5.0.0.4634.697f757 2 conda-forge mako 1.2.4 pyhd8ed1ab_0 conda-forge market-prices 0.10.1 pypi_0 pypi markupsafe 2.1.1 py310h2bbff1b_0 matplotlib 3.5.2 py310haa95532_0 matplotlib-base 3.5.2 py310hd77b12b_0 matplotlib-inline 0.1.2 pyhd3eb1b0_2 mccabe 0.6.1 py310haa95532_2 mdptoolbox-hiive 4.0.3.1 pypi_0 pypi mistune 0.8.4 py310h2bbff1b_1000 mkl 2021.4.0 haa95532_640 mkl-service 2.4.0 py310h2bbff1b_0 mkl_fft 1.3.1 py310ha0764ea_0 mkl_random 1.2.2 py310h4ed8f06_0 mock 4.0.3 pyhd3eb1b0_0 mpmath 1.2.1 py310haa95532_0 msys2-conda-epoch 20160418 1 conda-forge multipledispatch 0.6.0 py_0 conda-forge multitasking 0.0.9 pyhd8ed1ab_0 conda-forge munkres 1.1.4 py_0 mypy_extensions 0.4.3 py310haa95532_1 nbclassic 0.5.2 py310haa95532_0 nbclient 0.5.13 py310haa95532_0 nbconvert 6.4.4 py310haa95532_0 nbformat 5.3.0 py310haa95532_0 nest-asyncio 1.5.5 py310haa95532_0 networkx 3.0 pyhd8ed1ab_0 conda-forge nltk 3.7 pypi_0 pypi notebook 6.5.2 py310haa95532_0 notebook-shim 0.2.2 py310haa95532_0 numexpr 2.8.3 py310hb57aa6b_0 numpy 1.23.5 pypi_0 pypi numpydoc 1.4.0 py310haa95532_0 openssl 1.1.1t hcfcfb64_0 conda-forge optuna 3.1.0 pypi_0 pypi packaging 21.3 pyhd3eb1b0_0 pandas 1.4.3 py310hd77b12b_0 pandas-datareader 0.10.0 pyh6c4a22f_0 conda-forge pandocfilters 1.5.0 pyhd3eb1b0_0 paramiko 2.8.1 pyhd3eb1b0_0 parso 0.8.3 pyhd3eb1b0_0 pathspec 0.9.0 py310haa95532_0 patsy 0.5.3 pyhd8ed1ab_0 conda-forge pcre 8.45 hd77b12b_0 pexpect 4.8.0 pyhd3eb1b0_3 pickleshare 0.7.5 pyhd3eb1b0_1003 pillow 9.2.0 py310hdc2b20a_1 pip 23.0 pypi_0 pypi platformdirs 2.4.0 pyhd3eb1b0_0 plotly 5.13.0 pypi_0 pypi pluggy 1.0.0 py310haa95532_1 ply 3.11 py310haa95532_0 poyo 0.5.0 pyhd3eb1b0_0 prometheus_client 0.14.1 py310haa95532_0 prompt-toolkit 3.0.36 py310haa95532_0 prompt_toolkit 3.0.36 hd3eb1b0_0 psutil 5.9.0 py310h2bbff1b_0 ptable 0.9.2 pypi_0 pypi ptyprocess 0.7.0 pyhd3eb1b0_2 pycodestyle 2.8.0 pyhd3eb1b0_0 pycparser 2.21 pyhd3eb1b0_0 pydocstyle 6.1.1 pyhd3eb1b0_0 pyflakes 2.4.0 pyhd3eb1b0_0 pyfolio 0.7.0 pypi_0 pypi pyfolio-reloaded 0.9.5 pypi_0 pypi pygments 2.11.2 pyhd3eb1b0_0 pylint 2.14.5 py310haa95532_0 pyls-spyder 0.4.0 pyhd3eb1b0_0 pyluach 2.0.2 pyhd8ed1ab_0 conda-forge pymarkowitz 1.1.5 pypi_0 pypi pynacl 1.5.0 py310h8cc25b3_0 pyopenssl 22.0.0 pyhd3eb1b0_0 pyparsing 3.0.9 py310haa95532_0 pyprind 2.11.3 pypi_0 pypi pyqt 5.15.7 py310hd77b12b_0 pyqt5-sip 12.11.0 py310hd77b12b_0 pyreadline 2.1 py310haa95532_1 pyrsistent 0.18.0 py310h2bbff1b_0 pysocks 1.7.1 py310haa95532_0 pytables 3.7.0 py310h388bc9b_1 python 3.10.4 hbb2ffb3_0 python-dateutil 2.8.2 pyhd3eb1b0_0 python-fastjsonschema 2.16.2 py310haa95532_0 python-interface 1.6.0 py_0 conda-forge python-lsp-black 1.0.0 pyhd3eb1b0_0 python-lsp-jsonrpc 1.0.0 pyhd3eb1b0_0 python-lsp-server 1.3.3 pyhd3eb1b0_0 python-slugify 5.0.2 pyhd3eb1b0_0 python_abi 3.10 2_cp310 conda-forge pytz 2022.1 py310haa95532_0 pywin32 302 py310h2bbff1b_2 pywin32-ctypes 0.2.0 py310haa95532_1000 pywinpty 2.0.2 py310h5da7b33_0 pyyaml 6.0 py310h2bbff1b_0 pyzmq 23.2.0 py310hd77b12b_0 qdarkstyle 3.0.2 pyhd3eb1b0_0 qstylizer 0.1.10 pyhd3eb1b0_0 qt-main 5.15.2 he8e5bd7_7 qt-webengine 5.15.9 hb9a9bb5_4 qtawesome 1.0.3 pyhd3eb1b0_0 qtconsole 5.2.2 pyhd3eb1b0_0 qtpy 2.0.1 pyhd3eb1b0_0 qtwebkit 5.212 h3ad3cdb_4 regex 2022.8.17 pypi_0 pypi requests 2.28.1 py310haa95532_0 requests-futures 1.0.0 pypi_0 pypi rope 0.22.0 pyhd3eb1b0_0 rtree 0.9.7 py310h2eaa2aa_1 scikit-learn 1.1.3 py310hd77b12b_0 scipy 1.7.3 py310h6d2d95c_2 send2trash 1.8.0 pyhd3eb1b0_1 setuptools 63.4.1 py310haa95532_0 sip 6.6.2 py310hd77b12b_0 six 1.16.0 pyhd3eb1b0_1 sniffio 1.2.0 py310haa95532_1 snowballstemmer 2.2.0 pyhd3eb1b0_0 sortedcontainers 2.4.0 pyhd3eb1b0_0 soupsieve 2.3.1 pyhd3eb1b0_0 sphinx 5.0.2 py310haa95532_0 sphinxcontrib-applehelp 1.0.2 pyhd3eb1b0_0 sphinxcontrib-devhelp 1.0.2 pyhd3eb1b0_0 sphinxcontrib-htmlhelp 2.0.0 pyhd3eb1b0_0 sphinxcontrib-jsmath 1.0.1 pyhd3eb1b0_0 sphinxcontrib-qthelp 1.0.3 pyhd3eb1b0_0 sphinxcontrib-serializinghtml 1.1.5 pyhd3eb1b0_0 spyder 5.2.2 py310haa95532_1 spyder-kernels 2.2.1 py310haa95532_0 sqlalchemy 1.4.46 py310h8d17308_0 conda-forge sqlite 3.39.2 h2bbff1b_0 statsmodels 0.13.5 py310h9b08ddd_2 conda-forge sympy 1.10.1 py310haa95532_0 ta-lib 0.4.19 py310h2873277_4 conda-forge tabulate 0.8.10 pypi_0 pypi tenacity 8.1.0 pypi_0 pypi tensorboard 2.11.2 pypi_0 pypi tensorflow 2.11.0 pypi_0 pypi tensorflow-estimator 2.11.0 pypi_0 pypi tensorflow-intel 2.11.0 pypi_0 pypi terminado 0.17.1 py310haa95532_0 testpath 0.6.0 py310haa95532_0 text-unidecode 1.3 pyhd3eb1b0_0 textdistance 4.2.1 pyhd3eb1b0_0 threadpoolctl 3.1.0 pyh8a188c0_0 conda-forge three-merge 0.1.1 pyhd3eb1b0_0 tinycss 0.4 pyhd3eb1b0_1002 tk 8.6.12 h2bbff1b_0 toml 0.10.2 pyhd3eb1b0_0 tomli 2.0.1 py310haa95532_0 tomlkit 0.11.1 py310haa95532_0 toolz 0.12.0 pyhd8ed1ab_0 conda-forge torchaudio 0.13.1 pypi_0 pypi tornado 6.1 py310h2bbff1b_0 trading-calendars 2.1.1 pyhd3deb0d_0 conda-forge traitlets 5.1.1 pyhd3eb1b0_0 truth-table-generator 1.1.2 pypi_0 pypi typing_extensions 4.3.0 py310haa95532_0 tzdata 2022a hda174b7_0 ucrt 10.0.22621.0 h57928b3_0 conda-forge ujson 5.4.0 py310hd77b12b_0 unidecode 1.2.0 pyhd3eb1b0_0 urllib3 1.26.11 py310haa95532_0 vc 14.2 h21ff451_1 vs2015_runtime 14.32.31332 h1d6e394_9 conda-forge watchdog 2.1.6 py310haa95532_0 wcwidth 0.2.5 pyhd3eb1b0_0 webencodings 0.5.1 py310haa95532_1 websocket-client 0.58.0 py310haa95532_4 wheel 0.37.1 pyhd3eb1b0_0 widgetsnbextension 3.5.2 py310haa95532_0 win_inet_pton 1.1.0 py310haa95532_0 wincertstore 0.2 py310haa95532_2 winpty 0.4.3 4 wordcloud 1.8.2.2 py310he2412df_0 conda-forge wrapt 1.14.1 py310h2bbff1b_0 xz 5.2.5 h8cc25b3_1 yahooquery 2.3.0 pypi_0 pypi yaml 0.2.5 he774522_0 yapf 0.31.0 pyhd3eb1b0_0 yfinance 0.2.12 pyhd8ed1ab_0 conda-forge zeromq 4.3.4 hd77b12b_0 zipline-reloaded 2.0.0.post1 he6999da_0 ml4t zipp 3.8.0 py310haa95532_0 zlib 1.2.12 h8cc25b3_2 zstd 1.5.2 h19a0ad4_0

Now that you know a little about me, let me tell you about the issue I am having:

Description of Issue

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: equity_symbol_mappings.symbol [SQL: INSERT INTO equity_symbol_mappings (id, end_date, share_class_symbol, start_date, company_symbol, symbol, sid) VALUES (?, ?, ?, ?, ?, ?, ?)] [parameters: (0, 0, '', 0, '', None, 0)] (Background on this error at: https://sqlalche.me/e/14/gkpj)

Here is how you can reproduce this issue on your machine:

Reproduction Steps

In "extension.py"

import pandas as pd from zipline.data.bundles import register from zipline.data.bundles.csvdir import csvdir_equities

register( 'equity-bundle', # name we select for the bundle csvdir_equities(

name of the directory as specified above (named after data frequency)

    ['daily'],
    # path to directory containing the location of the directory in which our .csv files exist:
    r'C:\Users\my-folder',
),
calendar_name='NYSE')

On the Anaconda Prompt

zipline ingest -b equity-bundle

I would have expected to find a sql database file including the price data infrmation of my share. ...

What steps have you taken to resolve this already?

After closer investigation of the relevant subpackage "csvdir.py" being involved as described in

https://github.com/quantopian/zipline/blob/master/zipline/data/bundles/csvdir.py

my two guesses are that the daily_bar_writer either has no access rights or receives an empty list without any symbol information of tickers included, e.g. like for Apple "AAPL". I can see the path and symbols being handed over to the function _pricing_iter() are correct but when I look at the Bar chart loading the assets, I see no ticker or SID information at all. I would have expected the "AAPL" symbol and SID = 0 but it looks like this:

Loading custom pricing data: [####################################] 100% Merging daily equity files: [####################################]

I ran Spyder with Admin rights and deselected write selection for the relevant "daily" data folder ...

I tried for ages, reading a hundreds of relevant docus but no success.

...

Sincerely, Marcus

MBounouar commented 1 year ago

Hi, I don't know what's going on, but the best way to be sure would be to run the tests especially the "bundle" tests. Also you seem to be running version 2.0.0 of zipline-reloaded, try to upgrade to the latest release. I doubt it's has to do to with access writes. It seems that the instrument symbols cannot be saved.

mbecker911 commented 1 year ago

@MBounouar Thank you so much for your comment. I created a new virtual environment from the scratch based on Python version 3.8 and installed zipline-reloaded via conda-forge with the command

conda install -c conda-forge zipline-reloaded

In the minimal example csvdir ingestion seems to run through now.

stefan-jansen commented 1 year ago

Closing; please feel free to reopen if the issue persists.

okmijn22 commented 1 year ago

I am having the same issue and was not able to resolve it by starting from scratch and creating a new environment with python 3.8.