ibmdb / python-ibmdbsa Goto Github PK
View Code? Open in Web Editor NEWAutomatically exported from code.google.com/p/ibm-db.ibm-db-sa
License: Apache License 2.0
Automatically exported from code.google.com/p/ibm-db.ibm-db-sa
License: Apache License 2.0
Hi,
I want to insert data with default Boolean values into the table using AS400 dialect and the sql alchemy ( using iseries driver on OS Ubuntu 16.04) . While inserting data into the table, I got following error
sqlalchemy.exc.DataError: (pyodbc.DataError) ('22018', '[22018] [IBM][System i Access ODBC Driver]Error in assignment
Hi,
I want to insert data with default Boolean values into the table using AS400 dialect and the sql alchemy ( using iseries driver on OS Ubuntu 16.04) . While inserting data into the table, I got following error
sqlalchemy.exc.DataError: (pyodbc.DataError) ('22018', '[22018] [IBM][System i Access ODBC Driver]Error in assignment
Code snippet:
from sqlalchemy import *
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.pool import StaticPool
URL = "ibm_db_sa+pyodbc400://{user}:{password}@{host}:{port}/{database}"
engine = create_engine(URL, poolclass=StaticPool, echo=True)
meta = MetaData(bind=engine, schema="test")
db_session = sessionmaker(bind=engine)
model = declarative_base(metadata=meta)
json_data = {
"title": "Sample Konfabulator Widget",
"name": "main_window",
"width": 500,
"height": 500
}
class Client(model):
tablename = 'client'
id = Column("id", Integer, nullable=False, primary_key=True)
name = Column("name", String(128), nullable=True)
age = Column("age", Integer)
is_active = Column("is_active", Boolean, default=True)
is_deleted = Column("is_deleted", Boolean, default=False)
client_address = Column("client_address", Text)
def add_client(client_details):
session = db_session()
client = Client(**client_details)
session.add(client)
session.commit()
return True
def get_client():
session = db_session()
client = session.query(Client).filter(Client.id == "1").first()
print("Client_id =", client.id)
print("Client_name =", client.name)
session.close()
return True
client_data = {"name": "text", "age": 30, "client_address": json.dumps(json_data)}
add_client(client_data)
get_client()
Add_client() and get_client() methods gives the same error.
Note: Same code working on the windows machine with windows iseries driver.
Please help
Currently some errors arise when trying to to use this dialect for Informix DB. Is there plans to support informix db as well under this dialect or will a new one be created?
Traceback (most recent call last):
File "alchemy_test.py", line 3, in <module>
import ibm_db_sa.ibm_db_sa
File "/QOpenSys/pkgs/lib/python3.6/site-packages/ibm_db_sa/init.py", line 22, in <module>
from . import ibm_db, pyodbc, base, zxjdbc
File "/QOpenSys/pkgs/lib/python3.6/site-packages/ibm_db_sa/ibm_db.py", line 20, in <module>
from .base import DB2ExecutionContext, DB2Dialect
File "/QOpenSys/pkgs/lib/python3.6/site-packages/ibm_db_sa/base.py", line 30, in <module>
from . import reflection as ibm_reflection
File "/QOpenSys/pkgs/lib/python3.6/site-packages/ibm_db_sa/reflection.py", line 26, in <module>
from future.utils import iteritems
ModuleNotFoundError: No module named 'future'
Looks like
a5bb019#diff-b3a9e1dd4976b655040c5eebb69309d1L24 added a dependency on the future package, but it wasn't added as a dependency to setup.py.
Having SQLAlchemy use CASTs, causes IBM_DB_SA layer to remove them on the call to:
ibm_db_sa/base.py:351: sql_ori = compiler.SQLCompiler.visit_select(self, select, **kwargs)
, where SQLCompiler is sqlalchemy.sql.compiler.SQLCompiler.
While 'select' input parameter is made of proper SQL query (incl. CASTs), the 'sql_ori' doesn't have it anymore (both in SELECT and WHERE clauses).
Example:
select is "SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1"
sql_ori is "SELECT 'test plain returns' AS anon_1 FROM SYSIBM.SYSDUMMY1"
Hi,
I cannot use your module since I get an main error. I took this is from your documentation
from sqlalchemy import create_engine
e = create_engine("db2+ibm_db://user:pass@host[:port]/database")
but get this error
Traceback (most recent call last):
File "ibm_db2_test3.py", line 2, in <module>
e = create_engine("db2+ibm_db://user:pass@host:1111/database")
File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/__init__.py", line 386, in create_engine
return strategy.create(*args, **kwargs)
File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line 75, in create
dbapi = dialect_cls.dbapi(**dbapi_args)
File "/usr/lib/python2.7/dist-packages/ibm_db_sa/ibm_db.py", line 58, in dbapi
import ibm_db_dbi as module
ImportError: No module named ibm_db_dbi
I can also use another engine (or same kind of alias)
from sqlalchemy import create_engine
import ibm_db_sa.ibm_db
e = create_engine('ibm_db_sa://user:pass@host:1111/database')
with the same error. The system has the follwing properties:
Searching for "No module named ibm_db_dbi" via google shows some guys with the same problems but no real solution. Do you think that the Debian package is some kind of broken?
Given that there have been 6 commits since the 8/31/2016 release, is there a reason that the package hasn't been uploaded to PyPI? I tend to install via pip
but that doesn't get me the current state of the package.
Hello. I have a python application and I am using sqlalchemy to manage my db2 database. I can run most sql command successfully like:
from sqlalchemy import create_engine
engine = create_engine("db2+ibm_db://userid:passwd@hostname:port/database;SECURITY=SSL;")
stmt = text("ALTER TABLE TEST.A DROP CONSTRAINT SQL191023141500200")
engine.execute(stmt)
However, when I run a rename table, it silently fails. (doesn't throw an exception)
stmt = text("RENAME TABLE TEST.TAB1 TO TAB2")
If I run the same command via other means, it works fine. Any suggestions on what the issue might be?
From @GoogleCodeExporter on March 16, 2015 6:50
At time of writing, there is no field mapping in ibm_db_sa for the DB2 DECFLOAT
type, so they just come back as strings. decimal.Decimal is the appropriate
python-side type to map them to, as discussed -
https://groups.google.com/d/msg/ibm_db/mjjocQ-rHEw/OWv3h3zLZI4J
Original issue reported on code.google.com by [email protected]
on 14 Mar 2014 at 2:53
Copied from original issue: ibmdb/python-ibmdb#148
I have the following table defined:
class FileStore(_Base):
__tablename__ = 'file_storage'
uuid = Column('uuid', String(48), primary_key=True)
filename = Column('filename', String(255))
filecontent = Column('filecontent', BLOB(1048576), nullable=False)
last_update = Column('last_update', DateTime)
def __init__(self, attrs):
self.uuid = attrs['uuid']
self.filename = attrs['filename']
self.filecontent = attrs['filecontent']
self.last_update = attrs['last_update']
print('filecontent length: {}'.format(len(self.filecontent)))
Before calling session.add(record) to insert new row to DB2, the print statement show that the file (read in as binary) has 4102461 bytes. The record is inserted without error. However, the query to retrieve the same record results only 46 bytes for filecontent.
Please advice.
After creating the engine, when instantiating the Inspector:
reflection.Inspector.from_engine(engine)
...it fails with error:
[IBM][CLI driver][DB2] SQL0206N "CURRENT" non è valido nel contesto in cui viene utilizzato. SQLSTATE=42703 SQLCODE=-206
The query that fails, issued by the inspector to get the isolation level, is:
SELECT CURRENT ISOLATION FROM sysibm.sysdummy1
After some research I found that DB2 for z/OS doesn't have that register.
reflection.py class gives an error as
name 'unicode' is not defined
my connection string is ibm_db_sa+pyodbc400://u000**:es*[email protected]../T10050bp
I am using as400Dialect_pyodbc class. Does pyodbc.py , reflection.py files has support to python3.5 ?
ibm_db_sa does not support isolation levels. When DB2 is used as ceilometer backend, this lack of support of isolation levels causes failures in REST API /v2/events:
2015-09-14 10:51:05.879 542 ERROR wsme.api [req-32584490-e803-4851-87fa-84ccb99595d8 - - - - -] Server-side error: "". Detail:
Traceback (most recent call last):
File "/usr/lib/python2.7/site-packages/wsmeext/pecan.py", line 84, in callfunction
result = f(self, _args, *_kwargs)
File "/usr/lib/python2.7/site-packages/ceilometer/api/controllers/v2/events.py", line 258, in get_all
limit)]
File "/usr/lib/python2.7/site-packages/ceilometer/event/storage/impl_sqlalchemy.py", line 220, in get_events
execution_options={'isolation_level': self.isolation_level})
File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 882, in connection
execution_options=execution_options)
File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 887, in _connection_for_bind
engine, execution_options)
File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 337, in _connection_for_bind
conn = conn.execution_options(**execution_options)
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 285, in execution_options
self.dialect.set_connection_execution_options(c, opt)
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line 403, in set_connection_execution_options
self._set_connection_isolation(connection, opts['isolation_level'])
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line 412, in _set_connection_isolation
self.set_isolation_level(connection.connection, level)
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/interfaces.py", line 716, in set_isolation_level
raise NotImplementedError()
ibm_db_sa should support isolation levels similar to other db's.
Given a python Decimal(54000000000000), when you put it into a Decimal column in SQLAlchemy that maps to a DB2 column, it adds extra data on it. Version of ibm_db_sa is 0.3.3. This has been seen on Mac, Windows and Linux.
from pprint import pprint
from decimal import Decimal
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
from sqlalchemy.types import DECIMAL
from sqlalchemy.types import Integer
import ibm_db_sa
Base = declarative_base()
class TestTable(Base):
__tablename__ = 'TEST_TABLE'
__table_args__ = {'schema' : 'ALCF_TEST'}
AUTO_GENERATED_ID = Column(Integer, primary_key=True)
DECIMAL_26_6 = Column(DECIMAL)
def get_all_sa(session):
tts = session.query(TestTable).all()
for tt in tts:
dct = {
'DECIMAL_26_6':tt.DECIMAL_26_6,
}
pprint(dct)
def sa_test(username, password, host, port, database):
dsn = '%s:%s@%s:%s/%s' % (username, password, host, port, database)
engine = create_engine('ibm_db_sa://%s' % dsn)#, echo=True)
conn = engine.connect()
session = scoped_session(sessionmaker(bind=engine))
tt = TestTable()
n = Decimal(54000000000000)
tt.DECIMAL_26_6 = n
session.add(tt)
session.commit()
get_all_sa(session)
conn.close()
if __name__ == "__main__":
print(ibm_db_sa.__version__)
username = ''
password = ''
host = ''
port = 50001
database = ""
sa_test(username, password, host, port, database)
When you select the data inserted you will get the following: Decimal('54000000000000.010000'), but you should get exactly Decimal(54000000000000).
Here is the SCHEMA for the table.
-- DROP TABLE ALCF_TEST.TEST_TABLE;
CREATE SCHEMA ALCF_TEST;
CREATE TABLE ALCF_TEST.TEST_TABLE (
AUTO_GENERATED_ID INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
DECIMAL_26_6 DECIMAL(26, 6),
);
Using version
ibm-db | 3.0.1
ibm-db-sa | 0.3.3
I have a Db2 table with a BOOLEAN
column
Boolean column in table ddl
CREATE TABLE "SCHEMA."TABLE" (
...
"VALUE_B" BOOLEAN ,
...
I get the following warning every time I read the table:
python3.7/site-packages/ibm_db_sa/reflection.py:255: SAWarning: Did not recognize type 'BOOLEAN' of column 'VALUE_B'
and the column gets assigned sqlalchemy.types.NULLTYPE
by default in bm_db_sa/reflection/DB2Reflector/get_columns
Is it possible to add support for BOOLEAN
columns so that they are not returned as nulltype?
I am trying to initialize a connection to Db2 Warehouse on Cloud by setting SQLALCHEMY_DATABASE_URI to one of the provided credential parts. They look like this:
{
"hostname": "db2w-host.us-south.db2w.cloud.ibm.com",
"password": "passwd",
"https_url": "https://db2w-host.us-south.db2w.cloud.ibm.com",
"port": 50001,
"ssldsn": "DATABASE=BLUDB;HOSTNAME=db2w-host.us-south.db2w.cloud.ibm.com;PORT=50001;PROTOCOL=TCPIP;UID=bluadmin;PWD=passwd;Security=SSL;",
"host": "db2w-host.us-south.db2w.cloud.ibm.com",
"uri": "db2://bluadmin:[email protected]:50001/BLUDB?ssl=true;",
"db": "BLUDB",
"username": "bluadmin",
"ssljdbcurl": "jdbc:db2://db2w-host.us-south.db2w.cloud.ibm.com:50001/BLUDB:sslConnection=true;"
}
I tried ssldsn and uri, but both result in errors. What is the correct connection URI? Could you
python-ibmdbsa/ibm_db_sa/ibm_db_sa/base.py
Lines 696 to 697 in 8bca217
These two lines raise AttributeError
when connecting with pyodbc ('db2+pyodbc://<dsn_name>'
). I can make a PR to fix this, but I wanted to check if anyone knows of a way to retrieve these attributes when using pyodbc, or if it would be best to just leave them None
or ''
.
From @hemlatabhatt on August 14, 2018 6:20
From @GoogleCodeExporter on March 16, 2015 6:49
I implemented a project using SQLAlchemy because I've read that there is DB2
support available for it.
However after I've implemented a lot of code testing it successfully on SQLite
and MySQL (while waiting for credentials for corporate DB2). When I've finally
connected to DB2 I've figured out that it is not supported by ibm_db_sa code.
Luckily the ibm_db driver (the binary part) seemed to work, but the system
tables had different layout. I needed also some SQL fix to support dedicated
DB2 syntax extension of creation of the tables in the specific internal entity
called DATABASE (sic!).
I've found the AS400Reflector that seemed more similar to the one I needed, but
firstly it was not working for me after all (different system tables) and
secondly - it was not integrated with the rest of the ibm_db_sa system, so it
couldn't be used without some sort of patching the ibm_db_sa code anyway. (see
the issue no 127 created for this -
https://code.google.com/p/ibm-db/issues/detail?id=127 )
But it could be used as very good starting point.
There are few changes I've done into the original code to make it running in my
project. Although it seems to work, I'm not sure if all of them are implemented
in correct way and at least one for sure is ugly (CREATE TABLE IN DATABASE is
partly an ugly hack). :)
I wanted IBM support team to validate this code and if any good thing can be
extracted of it - to include OS390 (z/OS) support in the global ibm_db_sa
official distribution. To be able to do so I needed an open issue, and so I've
created this one.
This applies to:
ibm_db_sa-0.3.0-py2.7.egg - that needed the update
and
ibm_db-2.0.2-py2.7-win32.egg - that was working fine
Original issue reported on code.google.com by [email protected]
on 2 Aug 2013 at 3:51
Copied from original issue: ibmdb/python-ibmdb#131
Copied from original issue: ibmdb/python-ibmdb-django#29
Using the official docker form IBM for DB2 10.5.5:
https://hub.docker.com/r/ibmcom/db2express-c/
docker run -it -p 50000:50000 -e DB2INST1_PASSWORD=mypass -e LICENSE=accept ibmcom/db2express-c:latest bash
# create db
su - db2inst1
db2start
db2sampl
python requirements:
sqlalchemy==1.0.17
ibm-db==2.0.5.1
ibm_db_sa==0.3.2
(btw. tried this with the newest versions also - same result)
OS: Ubuntu 14.04 64bit
Doing:
from sqlalchemy.ext.automap import automap_base
from sqlalchemy import create_engine
Base = automap_base()
engine = create_engine("db2://db2inst1:[email protected]:50000/SAMPLE")
Base.prepare(engine, reflect=True)
raises:
Traceback (most recent call last):
File "db2_automap.py", line 8, in <module>
Base.prepare(engine, reflect=True)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/automap.py", line 777, in prepare
generate_relationship)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/automap.py", line 899, in _relationships_for_fks
nullable = False not in set([fk.parent.nullable for fk in fks])
AttributeError: 'NoneType' object has no attribute 'nullable'
seems that some fk-s have incorrect parent and column attributes
this issue was reported to SQLAlchemy-s team, but this place is more appropriate for it
sqlalchemy 12 has the capability to add comment to a column definition
so if I create a table with comments describing the field
some_table = schema.Table( 'some_table', metadata,
Column('some_field' , Date, nullable=False, index= True,unique=False,
comment = 'some comments about my column')
)
columns = sqlalchemy.engine.reflection.Inspector.from_engine(my_engine).get_columns('some_table','schema_name')
This columns dict returned by the Inspector will have a comment key with the column/field comments.
something like this will add this capability...
file reflection.py from ibm_db_sa
sys_columns = Table("COLUMNS", ischema,
Column("TABSCHEMA", CoerceUnicode, key="tabschema"),
Column("TABNAME", CoerceUnicode, key="tabname"),
Column("COLNAME", CoerceUnicode, key="colname"),
Column("COLNO", sa_types.Integer, key="colno"),
Column("TYPENAME", CoerceUnicode, key="typename"),
Column("LENGTH", sa_types.Integer, key="length"),
Column("SCALE", sa_types.Integer, key="scale"),
Column("DEFAULT", CoerceUnicode, key="defaultval"),
Column("NULLS", CoerceUnicode, key="nullable"),
Column("IDENTITY", CoerceUnicode, key="identity"),
Column("GENERATED", CoerceUnicode, key="generated"),
Column("REMARKS", CoerceUnicode, key="remmarks"),
schema="SYSCAT")
on the function
def get_columns(self, connection, table_name, schema=None, **kw):
query = sql.select([syscols.c.colname, syscols.c.typename,
syscols.c.defaultval, syscols.c.nullable,
syscols.c.length, syscols.c.scale,
syscols.c.identity, syscols.c.generated,
syscols.c.remmarks],
sql.and_(
syscols.c.tabschema == current_schema,
syscols.c.tabname == table_name
),
order_by=[syscols.c.colno]
)
class DB2Reflector(BaseReflector):
'type': coltype,
'nullable': r[3] == 'Y',
'default': r[2] or None,
'comment' : r[8] or None,
'autoincrement': (r[6] == 'Y') and (r[7] != ' '),
})
From @hemlatabhatt on August 14, 2018 6:20
From @GoogleCodeExporter on March 16, 2015 6:49
I think the connect / pconnect methods in ibm_db_dbi should accept **kwargs
intead of a dictionary. DBAPI is vague about this, but from looking at other
libraries, ibm_db_dbi seems to be the only one that expects a dictionary for
the 6th parameter. Psycopg2, for example, has 2 extra keyword arguments plus
**kwargs, while pymssql just uses a bunch of keyword arguments.
By changing to **kwargs, the code can be simplified and also be more compatible
with how SQLAlchemy's create_connect_args() works, e.g. we can have the more
natural:
return ((dsn, conn_args['username'],'','',''), {'xxx': 'yyy'})
instead of:
return ((dsn, conn_args['username'],'','',''), {'conn_options': {'xxx': 'yyy'}})
Original issue reported on code.google.com by [email protected]
on 2 May 2012 at 1:43
Attachments:
Copied from original issue: ibmdb/python-ibmdb#107
Copied from original issue: ibmdb/python-ibmdb-django#28
From @GoogleCodeExporter on March 16, 2015 6:49
Hi,
currently there is no explicit Dialect for choosing an AS400 Remote Database.
As we have already the AS400 Reflector it's in my opinion not an hard task to
add this new Dialect.
This little snippet is enough in my environment to get things working...
from ibm_db_sa.ibm_db import DB2Dialect_ibm_db
from ibm_db_sa.reflection import AS400Reflector
class AS400Dialect(DB2Dialect_ibm_db):
_reflector_cls = AS400Reflector
setup.py:
'sqlalchemy.dialects': ['db2as400=ukh.db:AS400Dialect',],
DSN:
db2as400://...
Can you add this explict Dialect to the core package?
Original issue reported on code.google.com by [email protected]
on 22 Mar 2013 at 3:24
Copied from original issue: ibmdb/python-ibmdb#127
if I create a query
s_with_table_name = select([table_with_custom_columns]).
where(table_with_custom_columns.c.some_custom_column == "IBM")
mylog.info( "result of the compilation specifying table name \n\n%s\n" % s_with_table_name)
my_str_db2 = s_with_table_name.compile(
dialect=ibm_db_sa.dialect(),
compile_kwargs={"literal_binds": True})
Now the driver error out on this, other sa drivers produce a query with the literals
SELECT table_with_custom_columns.some_custom_column, table_with_custom_columns.id, table_with_custom_columns.some_id
FROM table_with_custom_columns
WHERE table_with_custom_columns.some_custom_column = 'IBM'
vs
SELECT table_with_custom_columns.some_custom_column, table_with_custom_columns.id, table_with_custom_columns.some_id
FROM table_with_custom_columns
WHERE table_with_custom_columns.some_custom_column = :some_custom_column_1
to fix this
file base.py from ibm_db_sa driver
change function get_select_precolumns signature
from
def get_select_precolumns(self, select):
to
def get_select_precolumns(self, select,**kwargs):
as sqlalchemy compiler looks for a function with this signature when we compile with
compile_kwargs={"literal_binds": True}
it doesn't brake the code if compile_kwargs is not specify on the compilation
my_str_db2 = s_with_table_name.compile(
dialect=ibm_db_sa.dialect())
I could not find documentation for ssl url and trust store location in url(format). Can you please add it?
@zzzeek fyi
Running this query with sqlalchemy:
SELECT live.videos.place_id AS place_id
FROM live.videos
WHERE date(live.videos.create_dt) = ? AND live.videos.place_id <= ? ORDER BY live.videos.place_id DESC FETCH FIRST 10 ROWS ONLY
So slow it times out. This does, however, run in one second or so on a SQL browser. I feel like this may be a driver issue but i'm not sure how to go about debugging it.
Hi - I'm working on an application that has a backing db2 on cloud DB with a remote disaster recovery node, using ibm_db_sa to connect to it. The connection string we used is ibm_db_sa://dummy:[email protected]:50001/BLUDB;clientRerouteAlternateServerName=dashdb-txn-flex-yp-wdc04-13.services.dal.bluemix.net;clientRerouteAlternatePortNumber=50001;enableSeamlessACR=true;;SECURITY=SSL; . Today, we tested the failove, and got repeated failures: "ibm_db_dbi::OperationalError: [IBM][CLI Driver] CLI0106E Connection is closed. SQLSTATE=08003 SQLCODE=-99999 [SQL: 'SELECT 1 FROM SYSIBM.SYSDUMMY1'] (Background on this error at: http://sqlalche.me/e/e3q8)". Is ACR supported with ibm_db_sa? If not, is there a workaround?
File "/home/siddhesh/Envs/agentenv/lib/python3.5/site-packages/ibm_db_sa/base.py", line 750, in get_unique_constraints
return self._reflector.get_unique_constraints(
AttributeError: 'AS400Reflector' object has no attribute 'get_unique_constraints'
From @GoogleCodeExporter on March 16, 2015 6:50
What steps will reproduce the problem?
1. Issue a create_engine() using "db+pyodbc400" dialect.
What is the expected output? What do you see instead?
Expecting return of engine using pyodbc, but fails on Python 3.
error in base.py:
File "C:\Python34\lib\site-packages\ibm_db_sa\base.py", line 35, in <listcomp>
SA_Version = [long(ver_token) for ver_token in SA_Version.split('.')[0:2]]
NameError: name 'long' is not defined
shell returned 1
What version of the product are you using? On what operating system?
Windows 7, Python 3.4 (32bit), SQLAlchemy 0.9.4, ibm_db_sa 0.3.1
Please provide any additional information below.
It appears that it has not been ported for Python 3, are there plans to support
Python 3?
Original issue reported on code.google.com by [email protected]
on 11 Jun 2014 at 9:11
Copied from original issue: ibmdb/python-ibmdb#154
I was trying to use table reflection on AS400 tables with this package and received an AttributeError.
Steps to reproduce:
engine = create_engine('db2+zxjdbc400://user:pass@as400;prompt=false')
quote_reflected = Table('MYTABLE', metadata, autoload=True, autoload_with=engine, schema='COMPANY')
create_engine('db2+pyodbc:///?odbc_connect={}'.format(connection_string))
fails with the following stack trace:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/mnt/c/Users/a683040/Projects/uccx/.venv/lib/python3.6/site-packages/sqlalchemy/engine/__init__.py", line 488, in create_engine
return strategy.create(*args, **kwargs)
File "/mnt/c/Users/a683040/Projects/uccx/.venv/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 98, in create
(cargs, cparams) = dialect.create_connect_args(u)
File "/mnt/c/Users/a683040/Projects/uccx/.venv/lib/python3.6/site-packages/ibm_db_sa/pyodbc.py", line 51, in create_connect_args
connectors = [urllib.unquote_plus(keys.pop('odbc_connect'))]
AttributeError: module 'urllib' has no attribute 'unquote_plus'
This is because unquote_plus has been moved to urllib.parse.
[Environment]
Database Server: DB2 on AS400 with DB2 Connect installed to allow TCP/IP connections (connection tested using ibm_db and the default port number)
Application Server: Linux server running Python 3 + Flask application, using ibm_db, ibm_db_sa and SQLAlchemy.
[Problem]
Trying to run a simple query to test that the connection from the application server to the test server is working when using SQLAlchemy + ibm_db_sa adapter:
# Tried using two different adapter names
engine = sqlalchemy.create_engine('ibm_db_sa://user:secret@database_server.host.com:446/database1')
engine = sqlalchemy.create_engine("db2+ibm_db://user:secret@database_server.host.com:446/database1')
result = engine.execute("select USER from USERS")
for row in result:
print("USER:", row['USER'])
This produces the following error:
Traceback (most recent call last):
File "/usr/local/lib/python3.5/dist-packages/ibm_db_dbi.py", line 1235, in _set_cursor_helper
num_columns = ibm_db.num_fields(self.stmt_handler)
Exception: SQLNumResultCols failed: [IBM][CLI Driver][AS] SQL0104N An unexpected token "ISOLATION" was found following "". Expected tokens may include: "SERVER CLIENT_ACCTNG CLIENT_APPLNAME CLIENT_PROGRAMID CLIE". SQLSTATE=42601 SQLCODE=-104
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
return fn()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 403, in connect
return _ConnectionFairy._checkout(self)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 791, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 532, in checkout
rec = pool._do_get()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 1196, in _do_get
self._dec_overflow()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 187, in reraise
raise value
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 1193, in _do_get
return self._create_connection()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 350, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 477, in __init__
self.__connect(first_connect_check=True)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 684, in __connect
exec_once(self.connection, self)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/event/attr.py", line 274, in exec_once
self(*args, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/event/attr.py", line 284, in __call__
fn(*args, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/langhelpers.py", line 1334, in go
return once_fn(*arg, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/strategies.py", line 183, in first_connect
dialect.initialize(c)
File "/usr/local/lib/python3.5/dist-packages/ibm_db_sa/base.py", line 695, in initialize
super(DB2Dialect, self).initialize(connection)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 274, in initialize
self.get_isolation_level(connection.connection)
File "/usr/local/lib/python3.5/dist-packages/ibm_db_sa/ibm_db.py", line 135, in get_isolation_level
cursor.execute('SELECT CURRENT ISOLATION FROM sysibm.sysdummy1')
File "/usr/local/lib/python3.5/dist-packages/ibm_db_dbi.py", line 1355, in execute
self._set_cursor_helper()
File "/usr/local/lib/python3.5/dist-packages/ibm_db_dbi.py", line 1238, in _set_cursor_helper
raise self.messages[len(self.messages) - 1]
ibm_db_dbi.ProgrammingError: ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][AS] SQL0104N An unexpected token "ISOLATION" was found following "". Expected tokens may include: "SERVER CLIENT_ACCTNG CLIENT_APPLNAME CLIENT_PROGRAMID CLIE". SQLSTATE=42601 SQLCODE=-104
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "test_sqla.py", line 12, in <module>
result = engine.execute("select USER from USERS")
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 2074, in execute
connection = self.contextual_connect(close_with_result=True)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 2123, in contextual_connect
self._wrap_pool_connect(self.pool.connect, None),
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 2162, in _wrap_pool_connect
e, dialect, self)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception_noconnection
exc_info
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 186, in reraise
raise value.with_traceback(tb)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
return fn()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 403, in connect
return _ConnectionFairy._checkout(self)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 791, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 532, in checkout
rec = pool._do_get()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 1196, in _do_get
self._dec_overflow()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 187, in reraise
raise value
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 1193, in _do_get
return self._create_connection()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 350, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 477, in __init__
self.__connect(first_connect_check=True)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 684, in __connect
exec_once(self.connection, self)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/event/attr.py", line 274, in exec_once
self(*args, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/event/attr.py", line 284, in __call__
fn(*args, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/langhelpers.py", line 1334, in go
return once_fn(*arg, **kw)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/strategies.py", line 183, in first_connect
dialect.initialize(c)
File "/usr/local/lib/python3.5/dist-packages/ibm_db_sa/base.py", line 695, in initialize
super(DB2Dialect, self).initialize(connection)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 274, in initialize
self.get_isolation_level(connection.connection)
File "/usr/local/lib/python3.5/dist-packages/ibm_db_sa/ibm_db.py", line 135, in get_isolation_level
cursor.execute('SELECT CURRENT ISOLATION FROM sysibm.sysdummy1')
File "/usr/local/lib/python3.5/dist-packages/ibm_db_dbi.py", line 1355, in execute
self._set_cursor_helper()
File "/usr/local/lib/python3.5/dist-packages/ibm_db_dbi.py", line 1238, in _set_cursor_helper
raise self.messages[len(self.messages) - 1]
sqlalchemy.exc.ProgrammingError: (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][AS] SQL0104N An unexpected token "ISOLATION" was found following "". Expected tokens may include: "SERVER CLIENT_ACCTNG CLIENT_APPLNAME CLIENT_PROGRAMID CLIE". SQLSTATE=42601 SQLCODE=-104 (Background on this error at: http://sqlalche.me/e/f405)
I also tried using table models instead of using my own query, tried using Python 2.7 as well. Everything returned the same error code
sqlalchemy.exc.ProgrammingError: (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError:
SQLNumResultCols failed: [IBM][CLI Driver][AS] SQL0104N An unexpected token "ISOLATION" was found following "".
Expected tokens may include: "SERVER CLIENT_ACCTNG CLIENT_APPLNAME CLIENT_PROGRAMID CLIE".
SQLSTATE=42601 SQLCODE=-104 (Background on this error at: http://sqlalche.me/e/f405)
Hi,
I discovered an issue in db engine creation code.
when the code prepares the db connection string it fails to escape the semicolon char. This makes it possible to inject arbitrary parameters in the connection string.
Under some circumstances this can be a security issue.
PoC:
I created two db2 instances in docker one is listening on tcp/50000 (default) and the other is on tcp/50001.
ActivePython 2.7.8.10 (ActiveState Software Inc.) based on
Python 2.7.8 (default, Jul 2 2014, 19:48:49) [MSC v.1500 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
import sqlalchemy
from sqlalchemy import *
import ibm_db_sa
from urllib import quote
import syspw="aaa"
db2 = sqlalchemy.create_engine('ibm_db_sa://db2inst1:%[email protected]/test' % quote(pw))
conn = db2.connect()
conn.execute("select host_name from TABLE(SYSPROC.ENV_GET_SYS_INFO()) AS SYSTEMINFO").fetchall()
[(u'b5b9411e889e',)]
conn.close()pw="aaa;PORT=50001"
db2 = sqlalchemy.create_engine('ibm_db_sa://db2inst1:%[email protected]/test' % quote(pw))
conn = db2.connect()
conn.execute("select host_name from TABLE(SYSPROC.ENV_GET_SYS_INFO()) AS SYSTEMINFO").fetchall()
[(u'e6aa666c18bf',)]
conn.close()
sqlalchemy.version
'0.9.8'
As you can see I was able to trick the program to connect to an alternative database via the password. (Which is a realistic vector I think.)
I originally reported this issue here:
https://groups.google.com/forum/#!topic/sqlalchemy/t0qooflT1Ao
I received this response:
"... This issue is specific to the workings of the dialect, in that it is
concatenating the URL components into a single string for the DBAPI
connect function. As ibm_db_sa is not part of SQLAlchemy, you'd need to
report this issue to them. ..."
Therefore I report it here.
I just found out that ibm_db now is available on conda-force https://anaconda.org/conda-forge/ibm_db.
Are there plans for adding ibm_db_sa to conda-forge?
When i try to pip install it I get
During handling of the above exception, another exception occurred:
Collecting ibm_db>=2.0.0
Downloading http://pypi.site.com/packages/98/cb/f77d9bd5f64246074af364cc30e20e3044c533890f3b67d30e89615c2fc5/ibm_db-3.0.1.tar.gz (642 kB)
|████████████████████████████████| 642 kB 705 kB/s
ERROR: Command errored out with exit status 1:
command: /local/scratch/venv/nd1/bin/python -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-60zm8kcj/ibm-db/setup.py'"'"'; __file__='"'"'/tmp/pip-install-60zm8kcj/ibm-db/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' egg_info --egg-base /tmp/pip-install-60zm8kcj/ibm-db/pip-egg-info
cwd: /tmp/pip-install-60zm8kcj/ibm-db/
Complete output (44 lines):
Detected 64-bit Python
Downloading https://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/linuxx64_odbc_cli.tar.gz
Traceback (most recent call last):
File "/local/scratch/python-base/lib/python3.7/urllib/request.py", line 1317, in do_open
encode_chunked=req.has_header('Transfer-encoding'))
File "/local/scratch//python-base/lib/python3.7/http/client.py", line 1244, in request
self._send_request(method, url, body, headers, encode_chunked)
File "/local/scratch/python-base/lib/python3.7/http/client.py", line 1290, in _send_request
self.endheaders(body, encode_chunked=encode_chunked)
File "/local/scratch/python-base/lib/python3.7/http/client.py", line 1239, in endheaders
self._send_output(message_body, encode_chunked=encode_chunked)
File "/local/scratch/python-base/lib/python3.7/http/client.py", line 1026, in _send_output
self.send(msg)
File "/local/scratch/python-base/lib/python3.7/http/client.py", line 966, in send
self.connect()
File "/local/scratch/python-base/lib/python3.7/http/client.py", line 1406, in connect
super().connect()
File "/local/scratch/python-base/lib/python3.7/http/client.py", line 938, in connect
(self.host,self.port), self.timeout, self.source_address)
File "/local/scratch/python-base/lib/python3.7/socket.py", line 707, in create_connection
for res in getaddrinfo(host, port, 0, SOCK_STREAM):
File "/local/scratch/python-base/lib/python3.7/socket.py", line 748, in getaddrinfo
for res in _socket.getaddrinfo(host, port, family, type, proto, flags):
socket.gaierror: [Errno -2] Name or service not known
Traceback (most recent call last):
File "<string>", line 1, in <module>
File "/tmp/pip-install-60zm8kcj/ibm-db/setup.py", line 185, in <module>
file_stream = BytesIO(request.urlopen(url, context=context).read())
File "/local/scratch/python-base/lib/python3.7/urllib/request.py", line 222, in urlopen
return opener.open(url, data, timeout)
File "/local/scratch/python-base/lib/python3.7/urllib/request.py", line 525, in open
response = self._open(req, data)
File "/local/scratch/python-base/lib/python3.7/urllib/request.py", line 543, in _open
'_open', req)
File "/local/scratch/python-base/lib/python3.7/urllib/request.py", line 503, in _call_chain
result = func(*args)
File "/local/scratch/python-base/lib/python3.7/urllib/request.py", line 1360, in https_open
context=self._context, check_hostname=self._check_hostname)
File "/local/scratch/python-base/lib/python3.7/urllib/request.py", line 1319, in do_open
raise URLError(err)
urllib.error.URLError: <urlopen error [Errno -2] Name or service not known>
----------------------------------------
ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.
It doesn’t look like that this version supports connection to DB2 for Z/OS. Is there any plan to enhance the adapter with this support ? If so, by when can it be expected ?
Is Python3 not officially support for the SA Db2 driver? If not, I think we should add that.
Error:
$ python3 django-test.py
Traceback (most recent call last):
File "django-test.py", line 3, in <module>
import ibm_db_sa.ibm_db_sa
ModuleNotFoundError: No module named 'ibm_db_sa.ibm_db_sa'
On a fresh Ubuntu 18.04 virtual machine, I did this..
sudo apt-get update
sudo apt install python-pip
sudo apt install python3-pip
sudo apt-get install python-setuptools
pip3 install ibm_db
pip3 install camelcase # as a test to ensure pip3 is working
python3 testpy3.py
Exception ibm_db_dbi.Error is raised instead of IntegrityError when updating multiple objects. Works fine when updating single object.
Here's some code to reproduce. In second try IntegrityError was not raised.
from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.exc import IntegrityError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
connstr = 'db2://chinook:p4ssw0rd@localhost:50000/Chinook'
engine = create_engine(connstr)
Base = declarative_base()
class Item(Base):
__tablename__ = 'item'
id = Column(Integer, primary_key=True)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add_all([
Item(id=1),
Item(id=2),
Item(id=3),
Item(id=4)
])
session.commit()
query = session.query(Item).filter(Item.id > 1)
item2 = query.first()
item2.id = 1
try:
session.commit()
except IntegrityError as exc:
print exc
print 'expected error'
finally:
session.rollback()
print '----'
items = query.all()
for i in items:
i.id = 1
try:
session.commit()
except IntegrityError as exc:
print exc
print 'expected error'
except Exception as exc:
print exc
print 'unexpected error'
finally:
session.rollback()
The above code is updating PK to already present in table and database returns error 803.
When updating single object a nice IntegrityError is raised. That is correct.
(ibm_db_dbi.IntegrityError) ibm_db_dbi::IntegrityError: Statement Execute Failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "CHINOOK.Invoice" from having duplicate values for the index key. SQLSTATE=23505 SQLCODE=-803 [SQL: u'UPDATE "Invoice" SET "InvoiceId"=? WHERE "Invoice"."InvoiceId" = ?'] [parameters: (1, 2)] (Background on this error at: http://sqlalche.me/e/gkpj)
But when updating PK of multiple objects in the same session we get general Error instead of IntegrityError
(ibm_db_dbi.Error) ibm_db_dbi::Error:
Error 1: [IBM][CLI Driver][DB2/LINUXX8664] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "CHINOOK.Invoice" from having duplicate values for the index key. SQLSTATE=23505 SQLCODE=-803Error 2: [IBM][CLI Driver][DB2/LINUXX8664] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "CHINOOK.Invoice" from having duplicate values for the index key. SQLSTATE=23505 SQLCODE=-803
[SQL: u'UPDATE "Invoice" SET "InvoiceId"=? WHERE "Invoice"."InvoiceId" = ?'] [parameters: ((1, 2), (1, 3))] (Background on this error at: http://sqlalche.me/e/dbapi)
reflection.py class gives an error as
name 'unicode' is not defined
my connection string is ibm_db_sa+pyodbc400://u000**:es*[email protected]../T10050bp
Script:
import pandas as pd
from sqlalchemy import create_engine
import glob
csv=(glob.glob("/home/nahum/*.csv"))
df_csv = pd.DataFrame(csv)
for index, row in df_csv.iterrows():
datastore=pd.read_csv(str(row[0]), delimiter=',', header=[0],skipfooter=3)
engine = create_engine('ibm_db_sa://USUARIO:[email protected]/*local')
datastore.to_sql('SCHEMACSV.conc', engine, if_exists='append', index=False)
Don't work on AS400 gives this error:
OperationalError: (ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "172.16.0.78". Communication function detecting the error: "connect". Protocol specific error code(s): "111", "", "". SQLSTATE=08001 SQLCODE=-30081
This code works on:
Postgresql, DB2 for Linux, Mysql... in all server but not on DB2 iseries.
From @GoogleCodeExporter on March 16, 2015 6:49
ibm_db_sa-0.3.0-py27.egg/ibm_db_sa/base.py:509
illegal_initial_characters = set(xrange(0, 10)).union(["_", "$"])
The expression 'set(xrange(0, 10))' gives set made of 10 integers, where set of
10 single-char strings is needed.
This can be compared to the specification of this variable originally done in
SQLAlchemy (I'm using versions 0.8.2, file sqlalchemy/sql/compiler.py:54):
ILLEGAL_INITIAL_CHARACTERS = set([str(x) for x in xrange(0, 10)]).union(['$'])
The problem shows up later, when this is used for checking
(sqlalchemy/sql/compiler.py:2419):
or value[0] in self.illegal_initial_characters
You can see below the results of the test made in Python 2.7:
>>> '1test'[0] in set(xrange(0, 10)).union(["_", "$"])
False
>>> '1test'[0] in set([str(x) for x in xrange(0, 10)]).union(['$'])
True
The line mentioned at the beginning should be changed to:
illegal_initial_characters = set([str(x) for x in xrange(0, 10)]).union(["_", "$"])
Original issue reported on code.google.com by [email protected]
on 26 Aug 2013 at 2:01
Copied from original issue: ibmdb/python-ibmdb#133
I have tried the following engine strings:
from sqlalchemy import create_engine
#e = create_engine("db2+ibm_db://user:pass@host[:port]/database")
#e = create_engine("ibm_db_sa://user:pass@host[:port]/database")
retrieving data from table into dataframe is no problem:
import pandas as pd
dff = pd.read_sql("select * from model_output_1 limit 10", con=e)
writing data from a dataframe to a table is no problem in append mode:
dff.to_sql(name="model_output_4", con=e, schema=schema, if_exists="append", index=False)
as soon as I switch the if_exists parameter to "replace", I get the following error:
dff.to_sql(name="model_output_4", con=e, schema=schema, if_exists="replace", index=False)
TypeError Traceback (most recent call last)
in ()
----> 1 dff.to_sql(name='model_output_4', con=e, schema='DASH5294', if_exists='replace', index=False)
/opt/conda/lib/python3.6/site-packages/pandas/core/generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
2529 sql.to_sql(self, name, con, schema=schema, if_exists=if_exists,
2530 index=index, index_label=index_label, chunksize=chunksize,
-> 2531 dtype=dtype, method=method)
2532
2533 def to_pickle(self, path, compression='infer',
/opt/conda/lib/python3.6/site-packages/pandas/io/sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
458 pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
459 index_label=index_label, schema=schema,
--> 460 chunksize=chunksize, dtype=dtype, method=method)
461
462
/opt/conda/lib/python3.6/site-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)
1171 if_exists=if_exists, index_label=index_label,
1172 schema=schema, dtype=dtype)
-> 1173 table.create()
1174 table.insert(chunksize, method=method)
1175 if (not name.isdigit() and not name.islower()):
/opt/conda/lib/python3.6/site-packages/pandas/io/sql.py in create(self)
575 "Table '{name}' already exists.".format(name=self.name))
576 elif self.if_exists == 'replace':
--> 577 self.pd_sql.drop_table(self.name, self.schema)
578 self._execute_create()
579 elif self.if_exists == 'append':
/opt/conda/lib/python3.6/site-packages/pandas/io/sql.py in drop_table(self, table_name, schema)
1220 schema = schema or self.meta.schema
1221 if self.has_table(table_name, schema):
-> 1222 self.meta.reflect(only=[table_name], schema=schema)
1223 self.get_table(table_name, schema).drop()
1224 self.meta.clear()
/opt/conda/lib/python3.6/site-packages/sqlalchemy/sql/schema.py in reflect(self, bind, schema, views, only, extend_existing, autoload_replace, resolve_fks, **dialect_kwargs)
4197
4198 available = util.OrderedSet(
-> 4199 bind.engine.table_names(schema, connection=conn)
4200 )
4201 if views:
/opt/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in table_names(self, schema, connection)
2241 if not schema:
2242 schema = self.dialect.default_schema_name
-> 2243 return self.dialect.get_table_names(conn, schema)
2244
2245 def has_table(self, table_name, schema=None):
/opt/conda/lib/python3.6/site-packages/ibm_db_sa/base.py in get_table_names(self, connection, schema, **kw)
721
722 def get_table_names(self, connection, schema=None, **kw):
--> 723 return self._reflector.get_table_names(connection, schema=schema, **kw)
724
725 def get_view_names(self, connection, schema=None, **kw):
in get_table_names(self, connection, schema, **kw)
/opt/conda/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py in cache(fn, self, con, *args, **kw)
41 info_cache = kw.get("info_cache", None)
42 if info_cache is None:
---> 43 return fn(self, con, *args, **kw)
44 key = (
45 fn.name,
/opt/conda/lib/python3.6/site-packages/ibm_db_sa/reflection.py in get_table_names(self, connection, schema, **kw)
208 where(systbl.c.tabschema == current_schema).
209 order_by(systbl.c.tabname)
--> 210 return [self.normalize_name(r[0]) for r in connection.execute(query)]
211
212 @reflection.cache
/opt/conda/lib/python3.6/site-packages/ibm_db_sa/reflection.py in (.0)
208 where(systbl.c.tabschema == current_schema).
209 order_by(systbl.c.tabname)
--> 210 return [self.normalize_name(r[0]) for r in connection.execute(query)]
211
212 @reflection.cache
TypeError: expected bytes, str found
When reflected SAMPLE DB2 from https://hub.docker.com/r/ibmcom/db2express-c
from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
engine = create_engine('db2://db2inst1:*********@127.0.0.1:50000/sample')
Base = automap_base()
Base.prepare(engine, reflect=True)
error was raised ArgumentError: ForeignKeyConstraint with duplicate source column references are not supported.
Hi,
I am using ibm_db_sa driver.My connection code is "create_engine("db2+ibm_db://dbinst1:********@192.168.XX.XXX:XXXXX/sample",pool_size=20, echo=False, poolclass=QueuePool).connect()"
When I am try to connect to db I got below error
ImportError: No module named 'ibm_db_dbi'
Whats wrong with my code ?
** While creating the engine for 'ibm_db_sa+pyodbc400://u026607:*****@172.219.154.200:446/t10050bp', SQLAlchemy tried to
** import the DB API module 'No driver found for dialect 'ibm_db_sa+pyodbc400'' but failed.
** + This is because 1 of 2 reasons:
** 1.) You forgot to install the DB API module 'No driver found for dialect 'ibm_db_sa+pyodbc400''.
** --> (Try: 'pip install No driver found for dialect 'ibm_db_sa+pyodbc400'')
** 2.) If the above step fails, you most likely forgot to
** --> install the actual database driver on your local
** --> machine! The driver is needed in order to install
** --> the Python DB API ('No driver found for dialect 'ibm_db_sa+pyodbc400'').
The connection configuration CurrentSchema
is a supported configuration for pip package ibm-db
but not in this package where it supports only.
E.g., when using ibm-db
:
import ibm_db
import ibm_db_dbi
import os
conn_str = 'DATABASE={};HOSTNAME={};PORT={};Security=SSL;UID={};PWD={};CurrentSchema={}' \
.format(
'BLUDB',
'db_host',
50001,
'bluadmin',
os.environ['DB2_PASSWORD'],
'NOT_BLUADMIN',
)
conn_handler = ibm_db.connect(conn_str, '', '')
conn = ibm_db_dbi.Connection(conn_handler)
sql_stmt = '''
SELECT * FROM ACTOR LIMIT 10
'''
cur = conn.cursor()
cur.execute(sql_stmt)
row=cur.fetchall()
print(row)
it works well by querying table ``
given table ACTOR
in the schema NOT_BLUADMIN
when using this package:
import os
from sqlalchemy import *
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import *
# Db2
e = create_engine(
'db2+ibm_db://bluadmin:xxxx@db_host:50001/BLUDB?Security=SSL&CurrentSchema=NOT_BLUADMIN'
)
sql = text("select * from ACTOR LIMIT 10")
result = e.execute(sql)
print([row for row in result])
it shall query table NOT_BLUADMIN.ACTOR
successfully.
It will report error which shows it still use current user name as current schema:
sqlalchemy.exc.ProgrammingError: (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: Exception('SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "BLUADMIN.ACTOR" is an undefined name. SQLSTATE=42704 SQLCODE=-204')
[SQL: select * from ACTOR LIMIT 10]
I get the following error when running engine.get_columns
on a table:
reflection.py:608: SAWarning: Did not recognize type 'TIMESTMP' of column`
Hi There,
Use the Flask-admin and connect to DB2 with Flask Sqlalchemy(ORM)
and description on https://flask-admin.readthedocs.io/en/latest/api/mod_contrib_sqla/
Usage
admin = Admin()
admin.add_view(ModelView(User, db.session))
Traceback (most recent call last):
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\flask\app.py", line 2463, in call
return self.wsgi_app(environ, start_response)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\flask\app.py", line 2449, in wsgi_app
response = self.handle_exception(e)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\flask\app.py", line 1866, in handle_exception
reraise(exc_type, exc_value, tb)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\flask_compat.py", line 39, in reraise
raise value
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\flask\app.py", line 2446, in wsgi_app
response = self.full_dispatch_request()
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\flask\app.py", line 1951, in full_dispatch_request
rv = self.handle_user_exception(e)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\flask\app.py", line 1820, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\flask_compat.py", line 39, in reraise
raise value
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\flask\app.py", line 1949, in full_dispatch_request
rv = self.dispatch_request()
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\flask\app.py", line 1935, in dispatch_request
return self.view_functionsrule.endpoint
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\flask_admin\base.py", line 69, in inner
return self._run_view(f, *args, **kwargs)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\flask_admin\base.py", line 368, in _run_view
return fn(self, args, **kwargs)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\flask_admin\model\base.py", line 1970, in index_view
view_args.search, view_args.filters, page_size=page_size)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\flask_admin\contrib\sqla\view.py", line 1065, in get_list
count = count_query.scalar() if count_query else None
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\sqlalchemy\orm\query.py", line 3330, in scalar
ret = self.one()
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\sqlalchemy\orm\query.py", line 3300, in one
ret = self.one_or_none()
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\sqlalchemy\orm\query.py", line 3269, in one_or_none
ret = list(self)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\sqlalchemy\orm\query.py", line 3342, in iter
return self._execute_and_instances(context)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\sqlalchemy\orm\query.py", line 3367, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\sqlalchemy\engine\base.py", line 988, in execute
return meth(self, multiparams, params)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\sqlalchemy\sql\elements.py", line 287, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\sqlalchemy\engine\base.py", line 1107, in _execute_clauseelement
distilled_params,
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\sqlalchemy\engine\base.py", line 1253, in _execute_context
e, statement, parameters, cursor, context
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\sqlalchemy\engine\base.py", line 1473, in _handle_dbapi_exception
util.raise_from_cause(sqlalchemy_exception, exc_info)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
raise value.with_traceback(tb)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\sqlalchemy\engine\base.py", line 1249, in _execute_context
cursor, statement, parameters, context
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\ibm_db_sa\ibm_db.py", line 112, in do_execute
cursor.execute(statement, parameters)
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\ibm_db_dbi.py", line 1356, in execute
self._set_cursor_helper()
File "C:\Users\ct\Anaconda3\envs\rpt_siqx\lib\site-packages\ibm_db_dbi.py", line 1239, in _set_cursor_helper
raise self.messages[len(self.messages) - 1]
sqlalchemy.exc.ProgrammingError: (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: Exception('SQLNumResultCols failed: [IBM][CLI Driver][DB2/AIX64] SQL0245N The invocation of routine "COUNT" is ambiguous. The argument in position "1" does not have a best fit. SQLSTATE=428F5\r SQLCODE=-245')
[SQL: SELECT count(?) AS count_1
FROM "user"]
[parameters: (b'',)]
(Background on this error at: http://sqlalche.me/e/f405)
Many thanks.
Hi,
I am calling stored procedure which has 1 out parameter. So my stored procedure call is like
CALL SOURCE.add_user_data ('BBBB', 'AAAA', 2,?);
When I am executing this statement using connection.execute() method , getting following error
sqlalchemy.exc.DBAPIError: (ibm_db_dbi.Error) ibm_db_dbi::Error: 0 params bound not matching 1 required [SQL: "CALL SOURCE.add_user_data ('BBBB', 'AAAA', 2,?);"] (Background on this error at: http://sqlalche.me/e/dbapi)
Please help
I'm getting error
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('ODBC SQL type -99 is not yet supported. column-index=2 type=-99', 'HY106') (Background on this error at: http://sqlalche.me/e/f405)
when making a reflection of Chinook database on DB2 10.5 server due column DEFAULT being of type CLOB and pyodbc does not support it. Is that default value necessary, since it's server side default?
My current walk around is by selecting None
for default values when collecting column information https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/ibm_db_sa/reflection.py#L240. Any drawbacks doing such?
Below more detail:
import urllib
from sqlalchemy import create_engine
odbcstr = 'DRIVER=DB2; HOSTNAME=127.0.0.1; DATABASE=Chinook; PROTOCOL=TCPIP; PORT=50000; UID=chinook; PWD=p4ssw0rd'
odbcstr = urllib.quote(odbcstr)
connstr = 'db2+pyodbc:///?odbc_connect={}'.format(odbcstr)
engine = create_engine(connstr)
Base = automap_base()
Base.prepare(engine, reflect=True)
Full traceback
Traceback (most recent call last):
File "/home/mike/projects/slashdb/experiments/db2_over_pyodbc.py", line 69, in <module>
Base.prepare(engine, reflect=True)
File "build/bdist.linux-x86_64/egg/sqlalchemy/ext/automap.py", line 761, in prepare
File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 4159, in reflect
File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 469, in __new__
File "build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py", line 67, in __exit__
File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 464, in __new__
File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 552, in _init
File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 574, in _autoload
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1596, in run_callable
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line 415, in reflecttable
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py", line 626, in reflecttable
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py", line 370, in get_columns
File "/home/mike/envs/slashdb/local/lib/python2.7/site-packages/ibm_db_sa/base.py", line 734, in get_columns
connection, table_name, schema=schema, **kw)
File "<string>", line 2, in get_columns
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py", line 55, in cache
File "/home/mike/envs/slashdb/local/lib/python2.7/site-packages/ibm_db_sa/reflection.py", line 250, in get_columns
for r in connection.execute(query):
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/result.py", line 946, in __iter__
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/result.py", line 1276, in fetchone
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1458, in _handle_dbapi_exception
File "build/bdist.linux-x86_64/egg/sqlalchemy/util/compat.py", line 296, in raise_from_cause
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/result.py", line 1268, in fetchone
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/result.py", line 1148, in _fetchone_impl
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('ODBC SQL type -99 is not yet supported. column-index=2 type=-99', 'HY106') (Background on this error at: http://sqlalche.me/e/f405)
The error is raised when executing sql for column information of tables in this reflection code https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/ibm_db_sa/reflection.py#L240
The executed sql is like
SELECT
"SYSCAT"."COLUMNS"."COLNAME",
"SYSCAT"."COLUMNS"."TYPENAME",
"SYSCAT"."COLUMNS"."DEFAULT",
"SYSCAT"."COLUMNS"."NULLS",
"SYSCAT"."COLUMNS"."LENGTH",
"SYSCAT"."COLUMNS"."SCALE",
"SYSCAT"."COLUMNS"."IDENTITY",
"SYSCAT"."COLUMNS"."GENERATED"
FROM
"SYSCAT"."COLUMNS"
WHERE "SYSCAT"."COLUMNS"."TABSCHEMA" = 'CHINOOK'
AND "SYSCAT"."COLUMNS"."TABNAME" = 'Album'
ORDER BY "SYSCAT"."COLUMNS"."COLNO"
According to below the column "SYSCAT"."COLUMNS"."DEFAULT"
is CLOB.
SELECT NAME, COLTYPE, TBNAME FROM SYSIBM.SYSCOLUMNS WHERE NAME = 'DEFAULT';
NAME,COLTYPE,TBNAME
DEFAULT,"CLOB ",SYSCOLUMNS
DEFAULT,"CLOB ",SYSROUTINEPARMS
DEFAULT,"CLOB ",SYSATTRIBUTES
DEFAULT,"CLOB ",SYSVARIABLES
DEFAULT,"CLOB ",COLUMNS
DEFAULT,"CLOB ",ROUTINEPARMS
DEFAULT,"CLOB ",ROWFIELDS
DEFAULT,"CLOB ",VARIABLES
DEFAULT,"VARCHAR ",ADMINTEMPCOLUMNS
Pyodbc does not support CLOB or BLOB and there we get an error.
I don't have sudo access where I am installing sqlalchemy. here is how I installed:
this is the method I'm using
import pandas as pd
from sqlalchemy import create_engine
def load_esx_data():
sql = "my_sql_stmt"
con = create_engine("db2+ibm_db://dashdb-entry-yp-dal09-09.services.dal.bluemix.net:50000/BLUDB")
return pd.read_sql_query(sql,con)
and this is the error
NoSuchModuleErrorTraceback (most recent call last)
<ipython-input-31-d4e40433e6a4> in <module>()
----> 1 esx_data = load_esx_data()
2 esx.head()
<ipython-input-30-d5ba26ec9fe0> in load_esx_data()
4 def load_esx_data():
5 sql = "select * from DASH7228.ESXCAPACITY UNION select * from DASH7228.ESXCAPACITY1X UNION select * from DASH7228.ESXCAPACITY2X select * from DASH7228.ESXDRCAPACITY select * from DASH7228.ESXDRCAPACITY1X"
----> 6 con = create_engine("db2+ibm_db://dashdb-entry-yp-dal09-09.services.dal.bluemix.net:50000/BLUDB")
7 return pd.read_sql_query(sql,con)
/gpfs/fs01/user/s5d6-da75800f90bd2c-09e06b09ef9b/.local/lib/python2.7/site-packages/sqlalchemy/engine/__init__.pyc in create_engine(*args, **kwargs)
389 strategy = kwargs.pop('strategy', default_strategy)
390 strategy = strategies.strategies[strategy]
--> 391 return strategy.create(*args, **kwargs)
392
393
/gpfs/fs01/user/s5d6-da75800f90bd2c-09e06b09ef9b/.local/lib/python2.7/site-packages/sqlalchemy/engine/strategies.pyc in create(self, name_or_url, **kwargs)
54 u.query.pop('plugin', None)
55
---> 56 entrypoint = u._get_entrypoint()
57 dialect_cls = entrypoint.get_dialect_cls(u)
58
/gpfs/fs01/user/s5d6-da75800f90bd2c-09e06b09ef9b/.local/lib/python2.7/site-packages/sqlalchemy/engine/url.pyc in _get_entrypoint(self)
137 else:
138 name = self.drivername.replace('+', '.')
--> 139 cls = registry.load(name)
140 # check for legacy dialects that
141 # would return a module with 'dialect' as the
/gpfs/fs01/user/s5d6-da75800f90bd2c-09e06b09ef9b/.local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.pyc in load(self, name)
216 raise exc.NoSuchModuleError(
217 "Can't load plugin: %s:%s" %
--> 218 (self.group, name))
219
220 def register(self, name, modulepath, objname):
NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:db2.ibm_db
``
Using jython 2.7.0
and jt400.jar
, I am receiving this error on first attempts using engine.connect()
.
I can get the connection to work on a second attempt by catching the initial exception.
engine = create_engine('db2+zxjdbc400://user:pass@as400;prompt=false')
try:
# this will fail
connection = engine.connect()
except AttributeError:
# this will pass
connection = engine.connect()
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.