local installation of sql pack not working?

[gnudiff]

I have a newest auto-install st2 on clean Ubuntu 20 (st2 3.6.0, on Python 3.8.10); and want to install the sql pack.

Unfortunately, it has still listed in requirements an old version of python package (psycopg2), which is not supported in Python 3.8.

So I cloned sql package locally to ~/stackstorm-sql/ and tried to modify requirements.txt.

However, when I do in ~/stackstorm-sql/

st2 pack install file:///.

it still downloads the pack from github, and tries to install the non-working version.

How can I modify the downloaded pack so that it is actually installed?

[arm4b]

You can contribute a fix to the upstream sql pack here:
https://github.com/stackStorm-exchange/stackstorm-sql

[gnudiff]

Thank you. A fix has been submitted already specifically for this in October, and has not yet been integrated:

StackStorm-Exchange/stackstorm-sql#15

That's why I am trying to resolve it locally. But the st2 CLI command doesn't seem to use local version of package.

[arm4b]

Thanks for the pointers. Updated and merged it now StackStorm-Exchange/stackstorm-sql#15

[arm4b]

The new pack version should be available now in the Exchange index.
Please confirm if that worked.

[gnudiff]

Yes, thanks, it works fine now.

[nicoanto]

I installed Stackstorm AIO using Docker.

StackStorm version :

"com.stackstorm.support": "Community",
"com.stackstorm.url": "https://stackstorm.com/#product",
"com.stackstorm.vendor": "StackStorm",
"com.stackstorm.version": "3.8.1",

Docker version :

df1@df1-ThinkStation-P510:$ docker --version
Docker version 27.3.1, build ce12230
df1@df1-ThinkStation-P510:$ docker-compose --version
docker-compose version 1.29.2, build unknown
df1@df1-ThinkStation-P510:~$

All is fine i'm able to install acitve directory pack. But i cannot install sql pack.

The exception :
result:
errors:

• message: Execution failed. See result for details.
  result:
  exit_code: 1
  result: None
  stderr: "st2.actions.python.SetupVirtualEnvironmentAction: DEBUG Setting up virtualenv for pack "sql" (/opt/stackstorm/packs/sql)
  st2.actions.python.SetupVirtualEnvironmentAction: DEBUG Removing virtualenv in "/opt/stackstorm/virtualenvs/sql"
  st2.actions.python.SetupVirtualEnvironmentAction: DEBUG Virtualenv successfully removed.
  st2.actions.python.SetupVirtualEnvironmentAction: DEBUG Creating virtualenv for pack "sql" in "/opt/stackstorm/virtualenvs/sql"
  st2.actions.python.SetupVirtualEnvironmentAction: DEBUG Creating virtualenv in "/opt/stackstorm/virtualenvs/sql" using Python binary "/opt/stackstorm/st2/bin/python"
  st2.actions.python.SetupVirtualEnvironmentAction: DEBUG Running command "/opt/stackstorm/st2/bin/virtualenv -p /opt/stackstorm/st2/bin/python --always-copy --verbose --no-download /opt/stackstorm/virtualenvs/sql" to create virtualenv.
  st2.actions.python.SetupVirtualEnvironmentAction: DEBUG Installing base requirements
  st2.actions.python.SetupVirtualEnvironmentAction: DEBUG Installing requirement six>=1.9.0,<2.0 with command /opt/stackstorm/virtualenvs/sql/bin/pip install six>=1.9.0,<2.0.
  st2.actions.python.SetupVirtualEnvironmentAction: DEBUG Installing pack specific requirements from "/opt/stackstorm/packs/sql/requirements.txt"
  st2.actions.python.SetupVirtualEnvironmentAction: DEBUG Installing requirements from file /opt/stackstorm/packs/sql/requirements.txt with command /opt/stackstorm/virtualenvs/sql/bin/pip install -U -r /opt/stackstorm/packs/sql/requirements.txt.
  Traceback (most recent call last):
  File "/opt/stackstorm/st2/lib/python3.8/site-packages/python_runner/python_action_wrapper.py", line 395, in
  obj.run()
  File "/opt/stackstorm/st2/lib/python3.8/site-packages/python_runner/python_action_wrapper.py", line 214, in run
  output = action.run(**self._parameters)
  File "/opt/stackstorm/packs/packs/actions/pack_mgmt/setup_virtualenv.py", line 93, in run
  setup_pack_virtualenv(
  File "/opt/stackstorm/st2/lib/python3.8/site-packages/st2common/util/virtualenvs.py", line 133, in setup_pack_virtualenv
  install_requirements(
  File "/opt/stackstorm/st2/lib/python3.8/site-packages/st2common/util/virtualenvs.py", line 301, in install_requirements
  raise Exception(
  Exception: Failed to install requirements from "/opt/stackstorm/packs/sql/requirements.txt": Collecting cx_Oracle<=7.3.0
  Using cached cx_Oracle-7.3.0-cp38-cp38-manylinux1_x86_64.whl (759 kB)
  Collecting psycopg2<=2.8
  Using cached psycopg2-2.8.tar.gz (367 kB)
  (stderr: ERROR: Command errored out with exit status 1:
  command: /opt/stackstorm/virtualenvs/sql/bin/python -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-hiwwsg6g/psycopg2_f516a652f1084a678d31c6b5c5f683d8/setup.py'"'"'; file='"'"'/tmp/pip-install-hiwwsg6g/psycopg2_f516a652f1084a678d31c6b5c5f683d8/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(file);code=f.re\d().replace('"'"'
  '"'"', '"'"'
  '"'"');f.close();exec(compile(code, file, '"'"'exec'"'"'))' egg_info --egg-base /tmp/pip-pip-egg-info-5jemev49
  cwd: /tmp/pip-install-hiwwsg6g/psycopg2_f516a652f1084a678d31c6b5c5f683d8/
  Complete output (23 lines):
  running egg_info
  creating /tmp/pip-pip-egg-info-5jemev49/psycopg2.egg-info
  writing /tmp/pip-pip-egg-info-5jemev49/psycopg2.egg-info/PKG-INFO
  writing dependency_links to /tmp/pip-pip-egg-info-5jemev49/psycopg2.egg-info/dependency_links.txt
  writing top-level names to /tmp/pip-pip-egg-info-5jemev49/psycopg2.egg-info/top_level.txt
  writing manifest file '/tmp/pip-pip-egg-info-5jemev49/psycopg2.egg-info/SOURCES.txt'

  Error: pg_config executable not found.

  pg_config is required to build psycopg2 from source. Please add the directory
  containing pg_config to the $PATH or specify the full executable path with the
  option:

  python setup.py build_ext --pg-config /path/to/pg_config build ...
  

  or with the pg_config option in 'setup.cfg'.

  If you prefer to avoid building psycopg2 from source, please install the PyPI
  'psycopg2-binary' package instead.

  For further information please check the 'doc/src/install.rst' file (also at
  http://initd.org/psycopg/docs/install.html).

  ---

ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.
WARNING: You are using pip version 20.3.3; however, version 24.3.1 is available.
You should consider upgrading via the '/opt/stackstorm/virtualenvs/sql/bin/python -m pip install --upgrade pip' command.

I have no idea how to fix that ;( I really need to use a postgre database

[fdrab]

install the psycopg2-binary as suggested by the pip output. that's what I did.

[nicoanto]

Within [st2-docker_st2client_1] container I installed psycopg2-binary using apt first, and i still get the issue, although the lib was correctly installed. I tested it using cmd

root@675ce1984833:/home/stanley# python3 -c "import psycopg2; print(psycopg2.version)"
2.8.4 (dt dec pq3 ext lo64)
root@675ce1984833:/home/stanley#

I also test real connection to a postgre DB with a script always from [st2-docker_st2client_1] container

import psycopg2
def test_connection():
try:
# Remplace les valeurs ci-dessous par les informations de connexion de ta base PostgreSQL
conn = psycopg2.connect(
dbname="easydaasdatabase",
user="xxxx",
password="xxxx",
host="192.168.1.xx"
)
print("Connexion réussie à la base de données !")
print("psycopg2 version :",psycopg2.version)
conn.close()
etc.

And it worked.

Then i test your suggestion, i install psycopg2-binary using pip

root@675ce1984833:/home/stanley# pip3 install psycopg2-binary
Collecting psycopg2-binary
Downloading psycopg2_binary-2.9.10-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (3.0 MB)
|████████████████████████████████| 3.0 MB 2.6 MB/s
Installing collected packages: psycopg2-binary
Successfully installed psycopg2-binary-2.9.10

The version is more recent. cmd and script above still work, but i still get the same error ;( Installation process of sql pack still require "Using cached psycopg2-2.8.tar.gz (367 kB) due to "/opt/stackstorm/packs/sql/requirements.txt" IS it a part of the pb ?

I am not an expert in docker and python i just wanted to make a POC with stackstorm and its sql pack for a project.

BR

[fdrab]

Yes. Stackstorm packs have their own virtual environments into which they install the required libraries. I've cloned the SQL pack for use in a K8S cluster and I've updated the requirements.txt to contain:
sqlalchemy
psycopg2-binary <=2.8.4
pymssql<3.0
oracledb
fdb
pymysql

The above doesn't work straight out of the gate and minor modifications to the action code is required, because sqlalchemy underwent significant changes in 2.0.0 release. After minor modifications to the code I'm now able to use the pack as before on my test env that's installed via manual component installation onto a RHEL8 server.

[nicoanto]

Any chance to publish these modifications quickly to the repo to get a full functionnal sql pack which will work within docker AIO image ?

[fdrab]

I'm not a good programmer and not everyone will want to install psycopg2-binary (people may have other options).
The only changes I've made are:
1, in generic_query:

with self.db_connection(kwargs_dict) as conn:
            # Execute the query
            query_result = conn.execute(query)
            #print(query_result.mappings().all())
            keys = query_result.keys()
            # We need to execute these commands while connection is still open.
            return_result = {'affected_rows': query_result.rowcount}
            if query_result.returns_rows:
                return_result = []
                all_results = query_result.fetchall()
                for row in all_results:
                    # Rows are returned as tuples with keys.
                    # Convert that to a dictionary for return
                    return_result.append(self.row_to_dict(keys, row))

2, in lib.base_action.py:

def row_to_dict(self, keys, row):
        """When SQLAlchemy returns information from a query the rows are
        tuples and have some data types that need to be converted before
        being returned.
        returns: dictionary of values
        """
        return_dict = {}
        for column in keys:
            value = getattr(row, column)

            if isinstance(value, datetime.date):
                return_dict[column] = value.isoformat()
            elif isinstance(value, decimal.Decimal):
                return_dict[column] = float(value)
            else:
                return_dict[column] = value

        return return_dict

3, in update / insert .py remove the "autoload=True" as it's deprecated.