pypyodbc / pypyodbc Goto Github PK
View Code? Open in Web Editor NEWpypyodbc is a pure Python cross platform ODBC interface module (pyodbc compatible as of 2017)
License: MIT License
pypyodbc is a pure Python cross platform ODBC interface module (pyodbc compatible as of 2017)
License: MIT License
I've noticed that there are cases where Boolean values are not handled consistently. Many ODBC drivers present these as an integer data type with a value of 0 or 1. Not all ODBC drivers hold to this convention. I have recently encountered one that returns an ASCII encoded 'T' or 'F' character representing the Boolean data. This caused the lambda on pypyodbc.py:651 to always return false.
I've resolved the issue by overloading the output converter to handle
def new_sql_bit_decoder(raw_bytes: bytes):
if raw_bytes in (b'F', b'0', b'FALSE', b'false', b'f'):
return False
return True
I'm not recommending this as a long term solution, but it at least provides the expected results.
I make a request to select sorted data from a dbf file
When trying to write data to another dbf file using Microsoft Access dBASE Driver, I get pypyodbc.Error: ('HY104', '[HY104] [Microsoft][ODBC dBASE Driver]Invalid precision value ')
I suppose that it is necessary to specify the cp866 encoding when connecting to the file.
Example
sql_str = R"INSERT INTO {0} VALUES ({1})".format(self.name, params)
cursor = self.connection.cursor()
cursor.executemany(sql_str, self.orders)
cursor.close()
How can I fix this error
Hello all,
When I try to fetchall the result from a select request, some rows, with null values, shows value from previous line.
Context:
Example:
import pypyodbc
DB_URI="DRIVER={HFSQL};Server Name=some.server;Server Port=4900; Database=some_database;UID=admin;PWD=some_pass"
conn = pypyodbc.connect(DB_URI)
cursor = conn.cursor()
result = cursor.execute("SELECT CODE from CWS_BASE WHERE id in ('66783')")
result.fetchall()
result = cursor.execute("SELECT CODE from CWS_BASE WHERE id in ('120140', '66783')")
result.fetchall()
Output:
[('',)]
[('BLOCAGE',), ('BLOCAGE',)]
Is it linked to the driver or is it linked to the parsing feature of pypyodbc ?
This is the code being used :
import pickle
cnxn_pypyodbc = pypyodbc.connect('DRIVER={SQL Server};Server=localhost;Database=master;Port=1433;UID=sa;PWD=Admin@1234')
cursor_pypyodbc = cnxn_pypyodbc.cursor()
######## Create table to store binary files
sql = """
Use [msdb];
IF OBJECT_ID('[dbo].[pkl_data]', 'U') IS NOT NULL
DROP TABLE [dbo].[pkl_data];
CREATE TABLE [dbo].[pkl_data]
(
[Id] INT NOT NULL PRIMARY KEY, -- Primary Key column
[library] VARCHAR(MAX),
[pkl] VARBINARY(MAX) NOT NULL
-- Specify more columns here
);
"""
cursor_pypyodbc.execute(sql)
######## Insert binary files
sql = """
insert into [msdb].[dbo].[pkl_data] (Id,library, pkl)
VALUES (?, ?,convert(varbinary(max), ?));
"""
import pickle
for i in range(11,20):
params = (i,"pypyodbc",pickle.dumps([1,2,3,4]))
cursor_pypyodbc.execute(sql, params)
cursor_pypyodbc.commit()
#### Read the files inserted
import pandas as pd
df = pd.read_sql_query("SELECT * FROM [msdb].[dbo].[pkl_data]" ,cnxn_pypyodbc)
df
#### Using pickle.loads to convert it into the original python object
[pickle.loads(i) for i in df[df['library']=='pypyodbc']['pkl']]
This errors out by providing the below error :
I see that the binary value of the object is truncated while getting stored on SQL Server database while using pypyodbc
Let's see what needs to be done to finally get rid of the pip errors.
Thanks @nickolay for your work in this comment.
1.3.x
python setup.py sdist
If the artifact(s) on test PyPI are OK...
the newer python installation no longer include the distutils package in its standard library. this may be the reason that 'python -m pip install pypyodbc' no longer works... it always complains with setuptools>=40 but i have already had setuptools-70 installed. hopefully its installation script can be updated soon.
In ODBC Driver (17 and 18) for SQL Server, it will report the size of VARCHAR(MAX) and NVARCHAR(MAX) as 0, causing an optimized bind_data route to be taken and having only the first amount of data to be read.
The old SQL Server driver would have just reported it as LONG_VARCHAR with a size of max 32 signed integer, fetching more data.
My quick and dirty proposal would be to modify line 1753 of the _CreateColBuf(self): function from:
if col_size >= 1024:
dynamic_length = True
to
if col_size >= 1024 or (col_size == 0 and col_sql_data_type in (SQL_VARCHAR, SQL_WVARCHAR)):
dynamic_length = True
Checking if the value in self.description[1] is of type string is another idea since any text column reported as size 0 in an ODBC driver sounds weird to me.
If there is anything I can do to help let me know.
Side note: WVARCHAR is also effected.
VARBINARY is not effected since the "variable length" Boolean for the type is set to True in the SQL_data_type_dict
reminder for myself:
take a look if code at:
https://github.com/jiangwen365/pypyodbc/compare/sqlbindcol
and unmerged pulls at https://github.com/jiangwen365/pypyodbc/pulls?q=is%3Apr+is%3Aunmerged
to see if there is something useful to merge.
I'm trying to switch from pyodbc to pypyodbc and it seems that pypyodbc has problems when trying to insert empty strings as parameters.
Here is an example of the sql query and the parameters:
INSERT INTO [My_MSSQLDB].[dbo].[Table1]
([ItemId]
,[Val1]
,[Val2]
,[Text]
,[OrderId])
VALUES (?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?)
The parameters as a list:
['123', 2, 1, 'Text1', 10034, '124', 7, 2, '', 10034, '125', 0, 1, 'Text3', 10034]
I'm testing pypyodbc with pypy 5.10.1 (python 3.5.3 equivalent) to connect to Progress Openedge database, but when i do
cx = pyodbc.connect("DSN=myConnection")
I got
pypyodbc.ProgrammingError: ('', 'SQL_INVALID_HANDLE')
The same program with standard python (3.5.x or 3.8.x) and pyodbc works well.
What can I check?
Segmentation fault (core dumped)
while using ThreadPoolExecutor it does not allow to execute the code on multiple thread where as it execute smoothly on single thread
I encountered the following issue when trying to connect to an odbc source (filemaker) on a Mac:
'pypyodbc.OdbcNoLibrary: 'ODBC Library is not found. Is LD_LIBRARY_PATH set?'
Macos 13.3 (ventura), libiodbc
installed with homebrew (brew install libiodbc
).
Currently it seems there are some features of pypyodbc that do not return the results SQLAlchemy is expecting, specifically when used in conjuncture with Pandas.
At the moment it appears that pypyodbc is not populating the .description attribute of results returned from SQLAlchemy's .execute() function properly. As a result when using pandas.read_sql_query() passing a preconfigured SQLAlchemy connection that is using pypyodbc, the column names are not what they are in the database.
pypyodbc converts column names in cursor's description
to lowercase by default for some reason.
In the previously released versions (up to 1.3.5) you had to do this to opt out of this behavior:
import pypyodbc
pypyodbc.lowercase = False
In the git version (with the changes from jiangwen365/pypyodbc#81 ) the above code has no effect, instead you have to override this behavior each time a cursor is being constructed:
cursor = cnxn.cursor(lowercase=False)
cursor.execute(query)
header = [column[0] for column in cursor.description]
This is bad because one doesn't always get access to the cursor API (e.g. pandas' read_sql mentioned in the stackoverflow link above) and because there's no reason to break backwards compatibility in this case (especially as the breakage is subtle and silent).
At the very least this warrants a major release and a note in the release notes.
Hi Sir,
โ You said that "Fix a bug on the pypyodbc library linked to HFSQL database. When a line contains an empty value (''), the value of the previous line is used." and we can do it with very high quality and fast. You can check our strong portfolio to make sure.
๐ฉ Would you like me to send you the portfolio?
Thanks,
On Apple Silicon homebew prefix is no longer /usr/local
but /opt/homebrew
this causes pypyodbc to fail:
> python -m pypyodbc
Traceback (most recent call last):
File "/Users/stephane/.pyenv/versions/3.10.2/lib/python3.10/site-packages/pypyodbc.py", line 428, in <module>
ODBC_API = ctypes.cdll.LoadLibrary('libodbc.so')
File "/Users/stephane/.pyenv/versions/3.10.2/lib/python3.10/ctypes/__init__.py", line 452, in LoadLibrary
return self._dlltype(name)
File "/Users/stephane/.pyenv/versions/3.10.2/lib/python3.10/ctypes/__init__.py", line 374, in __init__
self._handle = _dlopen(self._name, mode)
OSError: dlopen(libodbc.so, 0x0006): tried: 'libodbc.so' (no such file), '/usr/local/lib/libodbc.so' (no such file), '/usr/lib/libodbc.so' (no such file), '/Users/stephane/go/src/cdf/libodbc.so' (no such file), '/usr/local/lib/libodbc.so' (no such file), '/usr/lib/libodbc.so' (no such file)
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/Users/stephane/.pyenv/versions/3.10.2/lib/python3.10/runpy.py", line 196, in _run_module_as_main
return _run_code(code, main_globals, None,
File "/Users/stephane/.pyenv/versions/3.10.2/lib/python3.10/runpy.py", line 86, in _run_code
exec(code, run_globals)
File "/Users/stephane/.pyenv/versions/3.10.2/lib/python3.10/site-packages/pypyodbc.py", line 440, in <module>
raise OdbcNoLibrary('ODBC Library is not found. Is LD_LIBRARY_PATH set?')
__main__.OdbcNoLibrary: 'ODBC Library is not found. Is LD_LIBRARY_PATH set?'
Unfortunately LD_LIBRARY_PATH
and other equivalent are no longer supported on macOS due to security concerns.
We use macOS 12, with homebrew (which installs some libraries used by python such as deadline and unixodbc), and python 3.10.2 installed trough pyenv.
We tried to add LDFLAGS="-L$(brew --prefix)/lib
when calling pyenv to build python with no success.
In our database, our TimeField is recorded as HH:MM
. The tm_cvt
function assumes that if the field isn't null/empty then it has a format of HH:MM:SS.XXXXXX
. This is causing a value error as the slice on the string fails.
File "E:\projects\test\venv\lib\site-packages\pypyodbc.py", line 1842, in fetchall row = self.fetchone()
File "E:\projects\test\venv\lib\site-packages\pypyodbc.py", line 1896, in fetchone value_list.append(buf_cvt_func(alloc_buffer.value))
File "E:\projects\test\venv\lib\site-packages\pypyodbc.py", line 600, in tm_cvt else: return datetime.time(int(x[0:2]),int(x[3:5]),int(x[6:8]),int(x[9:].ljust(6,'0')))
ValueError: invalid literal for int() with base 10: ''
For comparison, pyodbc correctly decodes the value as datetime.time(0, 0).
pypyodbc : 1.3.6
python : 3.10.0
It seems that the present code is compatible up to Python2. Do you have any plan for adding support for Python3?
Hi,
Python = 3.8.10
pypyodbc = 1.3.6
Database = MS SQL Server 2019
Column type > [Message] nvarchar NULL
I have column Message (type is above) and sometimes i receive error like below when fetching data:
File "/home/ober/dev/venv/venv_email2jeeves/lib/python3.8/site-packages/pypyodbc.py", line 1920, in fetchone
raw_value = ''.join(raw_data_parts)
TypeError: sequence item 0: expected str instance, bytes found
example values of that column:
Error occurred only on row 3 and only when i use pypyodbc. When i switch back to pyodbc row is returned normally.
Comparing this project latest release version v1.3.6 (https://github.com/pypyodbc/pypyodbc/releases/tag/v1.3.6)
and PyPi latest version v1.3.6 (https://pypi.org/project/pypyodbc/#files) show up several differences (ignoring whitespaces).
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.