Closed avlis closed 1 year ago
PS: I can't explain the timestamps on the odbc trace, it looks like it's the first one that fails. But application wise, the flow is: test connection parameters, send a 'select @@version' to make sure it works <- this works try do do some stuff on new connection <- fails to create new connection
https://github.com/mkleehammer/pyodbc/issues/1095#issuecomment-1220793586 '34 comes with a very buggy old unixODBC, try a previous version of pyODBC to see if that's the cause of this too.
This doesn't make sense.
- Python: 3.11.0 (main, Oct 25 2022, 05:00:36) [GCC 10.2.1 20210110] on linux
- pyodbc: pyodbc-4.0.34-cp39-cp39-manylinux_2_24_x86_64.whl
That wheel won't install on Python 3.11:
(venv) gord@vbox-Xubu-20-04-a:~/PycharmProjects/zzz$ python -VV
Python 3.11.0 (main, Oct 24 2022, 19:55:51) [GCC 9.4.0]
(venv) gord@vbox-Xubu-20-04-a:~/PycharmProjects/zzz$ pip install pyodbc-4.0.34-cp39-cp39-manylinux_2_24_x86_64.whl
ERROR: pyodbc-4.0.34-cp39-cp39-manylinux_2_24_x86_64.whl is not a supported wheel on this platform.
In any case
pip install --upgrade --force-reinstall pyodbc --no-binary pyodbc
should install the latest version of pyodbc from source.
not sure how it got there then... just using a standard container based on python:3-slim.
FROM python:3-slim
ARG version="20221105-001"
LABEL version=${version}
ENV VERSION=${version}
ENV LD_LIBRARY_PATH=/opt/instantclient_21_8
WORKDIR /app
COPY pip-packages.txt pip-packages.txt
RUN apt-get update && apt-get -y dist-upgrade && apt-get -y install curl bash nano screen htop iftop tcpdump net-tools gnupg less iputils-ping unzip
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list && echo 'Package: *\nPin: origin packages.microsoft.com\nPin-Priority: 1\n' > /etc/apt/preferences.d/microsoft.pref
RUN cd /opt && curl https://download.oracle.com/otn_software/linux/instantclient/218000/instantclient-basiclite-linux.x64-21.8.0.0.0dbru.zip > /dev/shm/oic.zip && unzip /dev/shm/oic.zip && rm -f /dev/shm/oic.zip
RUN curl https://downloads.mariadb.com/MariaDB/mariadb_repo_setup > /tmp/ms.sh && chmod +x /tmp/ms.sh && /tmp/ms.sh
RUN apt-get update && ACCEPT_EULA=Y apt-get -y install msodbcsql18 mssql-tools18 mariadb-client postgresql-client g++ unixodbc-dev python3-dev libpq-dev libaio1 libmariadb3 libmariadb-dev
RUN pip3 install -r pip-packages.txt
RUN ln -s /opt/mssql-tools18/bin/sqlcmd /usr/local/bin/ && ln -s /opt/instantclient_21_8/sqlplus /usr/local/bin
RUN pip3 cache purge && apt-get -y purge g++ unixodbc-dev python3-dev libpq-dev libmariadb-dev && apt-get -y autoremove && apt-get autoclean && apt-get clean
root@24fffaae71d7:/app# pip list
Package Version
---------------------- -------
cx-Oracle 8.3.0
mariadb 1.1.4
mysql-connector-python 8.0.31
numpy 1.23.4
pandas 1.5.1
pip 22.3
protobuf 3.20.1
psycopg2 2.9.5
pyodbc 4.0.34
python-dateutil 2.8.2
pytz 2022.6
setuptools 65.5.0
six 1.16.0
wheel 0.37.1
root@24fffaae71d7:/app# python3 -VV
Python 3.11.0 (main, Oct 25 2022, 05:00:36) [GCC 10.2.1 20210110]
cat pip-packages.txt
pandas
pyodbc
cx_Oracle
psycopg2
mariadb
mysql-connector-python
trying to force pip to install pyodbc==4.0.32 , fails:
Building wheels for collected packages: pyodbc, cx_Oracle, psycopg2, mariadb
Building wheel for pyodbc (setup.py): started
Building wheel for pyodbc (setup.py): finished with status 'error'
error: subprocess-exited-with-error
× python setup.py bdist_wheel did not run successfully.
│ exit code: 1
╰─> [137 lines of output]
running bdist_wheel
running build
running build_ext
building 'pyodbc' extension
creating build
creating build/temp.linux-x86_64-cpython-311
creating build/temp.linux-x86_64-cpython-311/src
gcc -pthread -Wsign-compare -DNDEBUG -g -fwrapv -O3 -Wall -fPIC -DPYODBC_VERSION=4.0.32 -I/usr/local/include/python3.11 -c src/buffer.cpp -o build/temp.linux-x86_64-cpython-311/src/buffer.o -Wno-write-strings
In file included from src/pyodbc.h:172,
from src/buffer.cpp:12:
src/pyodbccompat.h: In function ‘PyObject* Text_New(Py_ssize_t)’:
src/pyodbccompat.h:75:43: warning: ‘PyObject* PyUnicode_FromUnicode(const Py_UNICODE*, Py_ssize_t)’ is deprecated [-Wdeprecated-declarations]
75 | return PyUnicode_FromUnicode(0, length);
| ^
In file included from /usr/local/include/python3.11/unicodeobject.h:1042,
from /usr/local/include/python3.11/Python.h:51,
from src/pyodbc.h:45,
from src/buffer.cpp:12:
/usr/local/include/python3.11/cpython/unicodeobject.h:600:42: note: declared here
600 | Py_DEPRECATED(3.3) PyAPI_FUNC(PyObject*) PyUnicode_FromUnicode(
| ^~~~~~~~~~~~~~~~~~~~~
src/pyodbccompat.h: In function ‘Py_UNICODE* Text_Buffer(PyObject*)’:
/usr/local/include/python3.11/cpython/unicodeobject.h:698:75: warning: ‘Py_UNICODE* PyUnicode_AS_UNICODE(PyObject*)’ is deprecated [-Wdeprecated-declarations]
698 | # define PyUnicode_AS_UNICODE(op) PyUnicode_AS_UNICODE(_PyObject_CAST(op))
| ^
src/pyodbccompat.h:86:12: note: in expansion of macro ‘PyUnicode_AS_UNICODE’
86 | return PyUnicode_AS_UNICODE(o);
| ^~~~~~~~~~~~~~~~~~~~
/usr/local/include/python3.11/cpython/unicodeobject.h:685:27: note: declared here
685 | static inline Py_UNICODE* PyUnicode_AS_UNICODE(PyObject *op)
| ^~~~~~~~~~~~~~~~~~~~
src/pyodbccompat.h: In function ‘Py_ssize_t Text_Size(PyObject*)’:
/usr/local/include/python3.11/cpython/unicodeobject.h:664:71: warning: ‘Py_ssize_t PyUnicode_GET_SIZE(PyObject*)’ is deprecated [-Wdeprecated-declarations]
664 | # define PyUnicode_GET_SIZE(op) PyUnicode_GET_SIZE(_PyObject_CAST(op))
| ^
src/pyodbccompat.h:126:40: note: in expansion of macro ‘PyUnicode_GET_SIZE’
126 | return (o && PyUnicode_Check(o)) ? PyUnicode_GET_SIZE(o) : 0;
| ^~~~~~~~~~~~~~~~~~
/usr/local/include/python3.11/cpython/unicodeobject.h:652:26: note: declared here
652 | static inline Py_ssize_t PyUnicode_GET_SIZE(PyObject *op)
| ^~~~~~~~~~~~~~~~~~
src/pyodbccompat.h: In function ‘Py_ssize_t TextCopyToUnicode(Py_UNICODE*, PyObject*)’:
/usr/local/include/python3.11/cpython/unicodeobject.h:664:71: warning: ‘Py_ssize_t PyUnicode_GET_SIZE(PyObject*)’ is deprecated [-Wdeprecated-declarations]
664 | # define PyUnicode_GET_SIZE(op) PyUnicode_GET_SIZE(_PyObject_CAST(op))
| ^
src/pyodbccompat.h:146:26: note: in expansion of macro ‘PyUnicode_GET_SIZE’
146 | Py_ssize_t cch = PyUnicode_GET_SIZE(o);
| ^~~~~~~~~~~~~~~~~~
/usr/local/include/python3.11/cpython/unicodeobject.h:652:26: note: declared here
652 | static inline Py_ssize_t PyUnicode_GET_SIZE(PyObject *op)
| ^~~~~~~~~~~~~~~~~~
/usr/local/include/python3.11/cpython/unicodeobject.h:698:75: warning: ‘Py_UNICODE* PyUnicode_AS_UNICODE(PyObject*)’ is deprecated [-Wdeprecated-declarations]
698 | # define PyUnicode_AS_UNICODE(op) PyUnicode_AS_UNICODE(_PyObject_CAST(op))
| ^
src/pyodbccompat.h:147:24: note: in expansion of macro ‘PyUnicode_AS_UNICODE’
147 | memcpy(buffer, PyUnicode_AS_UNICODE(o), cch * sizeof(Py_UNICODE));
| ^~~~~~~~~~~~~~~~~~~~
/usr/local/include/python3.11/cpython/unicodeobject.h:685:27: note: declared here
685 | static inline Py_UNICODE* PyUnicode_AS_UNICODE(PyObject *op)
| ^~~~~~~~~~~~~~~~~~~~
gcc -pthread -Wsign-compare -DNDEBUG -g -fwrapv -O3 -Wall -fPIC -DPYODBC_VERSION=4.0.32 -I/usr/local/include/python3.11 -c src/cnxninfo.cpp -o build/temp.linux-x86_64-cpython-311/src/cnxninfo.o -Wno-write-strings
In file included from src/pyodbc.h:172,
from src/cnxninfo.cpp:7:
src/pyodbccompat.h: In function ‘PyObject* Text_New(Py_ssize_t)’:
src/pyodbccompat.h:75:43: warning: ‘PyObject* PyUnicode_FromUnicode(const Py_UNICODE*, Py_ssize_t)’ is deprecated [-Wdeprecated-declarations]
75 | return PyUnicode_FromUnicode(0, length);
| ^
In file included from /usr/local/include/python3.11/unicodeobject.h:1042,
from /usr/local/include/python3.11/Python.h:51,
from src/pyodbc.h:45,
from src/cnxninfo.cpp:7:
/usr/local/include/python3.11/cpython/unicodeobject.h:600:42: note: declared here
600 | Py_DEPRECATED(3.3) PyAPI_FUNC(PyObject*) PyUnicode_FromUnicode(
| ^~~~~~~~~~~~~~~~~~~~~
src/pyodbccompat.h: In function ‘Py_UNICODE* Text_Buffer(PyObject*)’:
/usr/local/include/python3.11/cpython/unicodeobject.h:698:75: warning: ‘Py_UNICODE* PyUnicode_AS_UNICODE(PyObject*)’ is deprecated [-Wdeprecated-declarations]
698 | # define PyUnicode_AS_UNICODE(op) PyUnicode_AS_UNICODE(_PyObject_CAST(op))
| ^
src/pyodbccompat.h:86:12: note: in expansion of macro ‘PyUnicode_AS_UNICODE’
86 | return PyUnicode_AS_UNICODE(o);
| ^~~~~~~~~~~~~~~~~~~~
/usr/local/include/python3.11/cpython/unicodeobject.h:685:27: note: declared here
685 | static inline Py_UNICODE* PyUnicode_AS_UNICODE(PyObject *op)
| ^~~~~~~~~~~~~~~~~~~~
src/pyodbccompat.h: In function ‘Py_ssize_t Text_Size(PyObject*)’:
/usr/local/include/python3.11/cpython/unicodeobject.h:664:71: warning: ‘Py_ssize_t PyUnicode_GET_SIZE(PyObject*)’ is deprecated [-Wdeprecated-declarations]
664 | # define PyUnicode_GET_SIZE(op) PyUnicode_GET_SIZE(_PyObject_CAST(op))
| ^
src/pyodbccompat.h:126:40: note: in expansion of macro ‘PyUnicode_GET_SIZE’
126 | return (o && PyUnicode_Check(o)) ? PyUnicode_GET_SIZE(o) : 0;
| ^~~~~~~~~~~~~~~~~~
/usr/local/include/python3.11/cpython/unicodeobject.h:652:26: note: declared here
652 | static inline Py_ssize_t PyUnicode_GET_SIZE(PyObject *op)
| ^~~~~~~~~~~~~~~~~~
src/pyodbccompat.h: In function ‘Py_ssize_t TextCopyToUnicode(Py_UNICODE*, PyObject*)’:
/usr/local/include/python3.11/cpython/unicodeobject.h:664:71: warning: ‘Py_ssize_t PyUnicode_GET_SIZE(PyObject*)’ is deprecated [-Wdeprecated-declarations]
664 | # define PyUnicode_GET_SIZE(op) PyUnicode_GET_SIZE(_PyObject_CAST(op))
| ^
src/pyodbccompat.h:146:26: note: in expansion of macro ‘PyUnicode_GET_SIZE’
146 | Py_ssize_t cch = PyUnicode_GET_SIZE(o);
| ^~~~~~~~~~~~~~~~~~
/usr/local/include/python3.11/cpython/unicodeobject.h:652:26: note: declared here
652 | static inline Py_ssize_t PyUnicode_GET_SIZE(PyObject *op)
| ^~~~~~~~~~~~~~~~~~
/usr/local/include/python3.11/cpython/unicodeobject.h:698:75: warning: ‘Py_UNICODE* PyUnicode_AS_UNICODE(PyObject*)’ is deprecated [-Wdeprecated-declarations]
698 | # define PyUnicode_AS_UNICODE(op) PyUnicode_AS_UNICODE(_PyObject_CAST(op))
| ^
src/pyodbccompat.h:147:24: note: in expansion of macro ‘PyUnicode_AS_UNICODE’
147 | memcpy(buffer, PyUnicode_AS_UNICODE(o), cch * sizeof(Py_UNICODE));
| ^~~~~~~~~~~~~~~~~~~~
/usr/local/include/python3.11/cpython/unicodeobject.h:685:27: note: declared here
685 | static inline Py_UNICODE* PyUnicode_AS_UNICODE(PyObject *op)
| ^~~~~~~~~~~~~~~~~~~~
src/cnxninfo.cpp: In function ‘PyObject* GetHash(PyObject*)’:
/usr/local/include/python3.11/cpython/unicodeobject.h:698:75: warning: ‘Py_UNICODE* PyUnicode_AS_UNICODE(PyObject*)’ is deprecated [-Wdeprecated-declarations]
698 | # define PyUnicode_AS_UNICODE(op) PyUnicode_AS_UNICODE(_PyObject_CAST(op))
| ^
src/cnxninfo.cpp:45:39: note: in expansion of macro ‘PyUnicode_AS_UNICODE’
45 | Object bytes(PyUnicode_EncodeUTF8(PyUnicode_AS_UNICODE(p), PyUnicode_GET_SIZE(p), 0));
| ^~~~~~~~~~~~~~~~~~~~
/usr/local/include/python3.11/cpython/unicodeobject.h:685:27: note: declared here
685 | static inline Py_UNICODE* PyUnicode_AS_UNICODE(PyObject *op)
| ^~~~~~~~~~~~~~~~~~~~
/usr/local/include/python3.11/cpython/unicodeobject.h:664:71: warning: ‘Py_ssize_t PyUnicode_GET_SIZE(PyObject*)’ is deprecated [-Wdeprecated-declarations]
664 | # define PyUnicode_GET_SIZE(op) PyUnicode_GET_SIZE(_PyObject_CAST(op))
| ^
src/cnxninfo.cpp:45:64: note: in expansion of macro ‘PyUnicode_GET_SIZE’
45 | Object bytes(PyUnicode_EncodeUTF8(PyUnicode_AS_UNICODE(p), PyUnicode_GET_SIZE(p), 0));
| ^~~~~~~~~~~~~~~~~~
/usr/local/include/python3.11/cpython/unicodeobject.h:652:26: note: declared here
652 | static inline Py_ssize_t PyUnicode_GET_SIZE(PyObject *op)
| ^~~~~~~~~~~~~~~~~~
src/cnxninfo.cpp:45:18: error: ‘PyUnicode_EncodeUTF8’ was not declared in this scope; did you mean ‘PyUnicode_DecodeUTF8’?
45 | Object bytes(PyUnicode_EncodeUTF8(PyUnicode_AS_UNICODE(p), PyUnicode_GET_SIZE(p), 0));
| ^~~~~~~~~~~~~~~~~~~~
| PyUnicode_DecodeUTF8
error: command '/usr/bin/gcc' failed with exit code 1
[end of output]
note: This error originates from a subprocess, and is likely not a problem with pip.
ERROR: Failed building wheel for pyodbc
Running setup.py clean for pyodbc
Step 16/20 : RUN pip3 install --upgrade --force-reinstall pyodbc --no-binary pyodbc
---> Running in 9c4820448bb9
DEPRECATION: --no-binary currently disables reading from the cache of locally built wheels. In the future --no-binary will not influence the wheel cache. pip 23.1 will enforce this behaviour change. A possible replacement is to use the --no-cache-dir option. You can use the flag --use-feature=no-binary-enable-wheel-cache to test the upcoming behaviour. Discussion can be found at https://github.com/pypa/pip/issues/11453
Collecting pyodbc
Using cached pyodbc-4.0.34.tar.gz (271 kB)
Preparing metadata (setup.py): started
Preparing metadata (setup.py): finished with status 'done'
Installing collected packages: pyodbc
Attempting uninstall: pyodbc
Found existing installation: pyodbc 4.0.34
Uninstalling pyodbc-4.0.34:
Successfully uninstalled pyodbc-4.0.34
DEPRECATION: pyodbc is being installed using the legacy 'setup.py install' method, because the '--no-binary' option was enabled for it and this currently disables local wheel building for projects that don't have a 'pyproject.toml' file. pip 23.1 will enforce this behaviour change. A possible replacement is to enable the '--use-pep517' option. Discussion can be found at https://github.com/pypa/pip/issues/11451
Running setup.py install for pyodbc: started
Running setup.py install for pyodbc: finished with status 'done'
Successfully installed pyodbc-4.0.34
and running the pyodbc.connect with each option as a parameter, instead of a single string parameter, will get me a "
trying to install 3.0.32 from source will get a similar error to the one with the binary package.
pyodbc 4.0.34 does not have pre-built wheels for Python 3.11, so plain old pip install pyodbc
builds from source:
(venv) gord@gord-dv7:~/PycharmProjects/pyodbc_issue_1117$ pip install pyodbc
Collecting pyodbc
Using cached pyodbc-4.0.34.tar.gz (271 kB)
Preparing metadata (setup.py) ... done
Building wheels for collected packages: pyodbc
Building wheel for pyodbc (setup.py) ... done
Created wheel for pyodbc: filename=pyodbc-4.0.34-cp311-cp311-linux_x86_64.whl size=367726 sha256=30486db1d15a8e89992ade3978eef75538512e6384191a903a230a4bced411e7
Stored in directory: /home/gord/.cache/pip/wheels/48/92/82/ef590fe3bb1c78aed76aea62dc928516a58915cd16997eee09
Successfully built pyodbc
Installing collected packages: pyodbc
Successfully installed pyodbc-4.0.34
After that, this code works:
import pyodbc
cnxn = pyodbc.connect(
driver="ODBC Driver 18 for SQL Server",
server="192.168.0.199",
user="scott",
password="tiger^5HHH",
database="test",
trustservercertificate="yes",
)
print("First connect succeeded.")
cnxn.close()
cnxn = pyodbc.connect(
driver="ODBC Driver 18 for SQL Server",
server="192.168.0.199",
user="scott",
password="tiger^5HHH",
database="test",
trustservercertificate="yes",
)
print("Second connect succeeded.")
"""console output:
First connect succeeded.
Second connect succeeded.
"""
I am unable to reproduce your issue:
gord@xubu-22-04:~/mydockerbuild$ cat my_app.py
import pyodbc
cnxn = pyodbc.connect(
driver="ODBC Driver 18 for SQL Server",
server="192.168.0.199",
user="scott",
password="tiger^5HHH",
database="test",
trustservercertificate="yes",
)
crsr = cnxn.cursor()
print(crsr.execute("SELECT 'First connect succeeded.'").fetchval())
crsr.close()
cnxn.close()
cnxn = pyodbc.connect(
driver="ODBC Driver 18 for SQL Server",
server="192.168.0.199",
user="scott",
password="tiger^5HHH",
database="test",
trustservercertificate="yes",
)
crsr = cnxn.cursor()
print(crsr.execute("SELECT 'Second connect succeeded.'").fetchval())
crsr.close()
cnxn.close()
gord@xubu-22-04:~/mydockerbuild$ cat my_dockerfile
FROM python:3-slim
WORKDIR /app
COPY my_app.py my_app.py
RUN apt-get update && apt-get -y install curl g++ gnupg unixodbc-dev
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list && echo 'Package: *\nPin: origin packages.microsoft.com\nPin-Priority: 1\n' > /etc/apt/preferences.d/microsoft.pref
RUN apt-get update && ACCEPT_EULA=Y apt-get -y install msodbcsql18 mssql-tools18 mariadb-client postgresql-client g++ unixodbc-dev python3-dev libpq-dev libaio1 libmariadb3 libmariadb-dev
RUN pip install pyodbc
RUN python my_app.py
gord@xubu-22-04:~/mydockerbuild$ sudo docker build ~/mydockerbuild -f my_dockerfile -t issue_1117
Sending build context to Docker daemon 4.096kB
Step 1/8 : FROM python:3-slim
---> 8b6fe05996da
Step 2/8 : WORKDIR /app
---> Using cache
---> 43e8dde06bf5
[…]
Step 8/8 : RUN python my_app.py
---> Running in d02bdde90da8
First connect succeeded.
Second connect succeeded.
Removing intermediate container d02bdde90da8
---> e532feafc751
Successfully built e532feafc751
Successfully tagged issue_1117:latest
Thanks to an amazing job by @keitherskine, new releases build official wheels now. There is an official 4.0.35 wheel available. Please try with that.
Environment
To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:
container based on python:3-slim
Issue
just checking out the new python 3.11, an old application of mine fails to establish the second connection to the same MS SQL server.
works every time if I send the connection parameters on a single string.
previous code:
nc=pyodbc.connect(driver="{ODBC Driver 18 for SQL Server}", server=c["server"], database=c["database"], user=c["user"], password=c["password"],encoding = "UTF-8", trustservercertificate = c["trustservercertificate"] )
now only works if:
nc=pyodbc.connect('DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={0};DATABASE={{{1}}};UID={{{2}}};PWD={{{3}}};ENCODING=UTF-8;TRUSTSERVERCERTIFICATE={4}'.format(c["server"], c["database"], c["user"], c["password"], c["trustservercertificate"] ))
ODBC trace:
Exception error message:
<build-in function connect> returned NULL without setting an exception