Which behaviour are you referring to?
- Cx Oracle Python Executemany Example 2
- Cx Oracle Python Executemany Example Pdf
- Cx Oracle Python Executemany Example For Kids
- Cx Oracle Python Executemany Example For Students
- Cx Oracle Python Executemany Example List
As in equivalence to cxOracle.executemany, what i could use to exclude? With a very simple example im using this block of code to insert data, but its possible to do it for delete entries? I have some monthly weather data that I want to insert into an Oracle database table but I want to insert the corresponding records in a batch in order to be more efficient. Can anyone advise as to how I'd go about doing this in Python? For example let's say my table has four fields: a station ID, a date, and two value fields. Welcome to cxOracle’s documentation!¶ cxOracle is a module that enables access to Oracle Database and conforms to the Python database API specification. This module is currently tested against Oracle Client 19, 18, 12, and 11.2, and Python 2.7, 3.5, 3.6 and 3.7.
This article shows how batch statement execution in the Python cxOracle interface for Oracle Database can significantly improve performance and make working with large data sets easy. In many cxOracle applications, executing SQL and PL/SQL statements using the method cursor.execute is perfect. But if you intend to execute the same statement. Oracle / python-cxOracle. When I try to use the cxOracle executemany command, I have no errors but also, no data inserted in the database!
When I pretend to use 3.5Million records x 225 columns with np.nan in some columns, the cx_Oracle library ends the executemany + commit statements without errors. It gives the impression that it works but it does not insert any data in the database. Ping drivers ebay. Very dangerous behavior to have it in production!.
Nevertheless, I did some further investigation:
- Before trying to save the pandas dataframe (df), I replaced all nan with None:
df = df.where(pd.notnull(df), None) - Given that the performance of the executemany seems to be exponentially affected by the number of records to insert (I can provide the stats if required), and that there was an error saying 'cx_Oracle.DatabaseError: DPI-1015: array size of 3500000 is too large' (btw, it would be nice to document the limit somewhere), I decided to split the df into 100k chunks, decision that speed up the process.. but:
- I have 1 prepare statement
cursor.prepare(sql) - I setinputsizes before each executemany
cursor.setinputsizes(*cx_types) - But, when I am in the X number of executemany, the following error appears:
'TypeError: expecting string or bytes object'
Reading the documentation from Cursor.execute, it seems that I have a columns with None that comes up with a number:
'For maximum efficiency when reusing an statement, it is best to use the setinputsizes() method to specify the parameter types and sizes ahead of time; in particular, None is assumed to be a string of length 1 so any values that are later bound as numbers or dates will raise a TypeError exception.'
'For maximum efficiency when reusing an statement, it is best to use the setinputsizes() method to specify the parameter types and sizes ahead of time; in particular, None is assumed to be a string of length 1 so any values that are later bound as numbers or dates will raise a TypeError exception.'
But, I do have a cursor.setinputsizes that says that the expected column is a cx_Oracle.NATIVE_FLOAT
Cx Oracle Python Executemany Example 2
Questions:
- I cannot see in the documentation that cursor.setinputsizes is taken into account for executemany. Is this a missing entry in the documentation? (http://cx-oracle.readthedocs.io/en/latest/cursor.html?highlight=executemany) or is this a fact and therefore, when I am slicing the df I will have different datatypes based on the 'None's that the executemany is finding in the first column?
Thanks
DT
DT
Join GitHub today
GitHub is home to over 40 million developers working together to host and review code, manage projects, and build software together.
Sign upHave a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. Alicia keys free download. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Comments
commented Aug 31, 2017
the changes related to numeric have broken existing behavior for executemany(): with cx_oracle 6.0.2: Traceback (most recent call last): File 'test.py', line 32, in [{'x': decimal.Decimal('25.8')}, {'x': decimal.Decimal('30.0')}] TypeError: expecting float succeeds with oracle 6.0.1 |
commented Aug 31, 2017
Yes, this is expected as a result of correcting issue #68. The call to execute() succeeds because the initial failure is swallowed and a new bind variable is created -- or in other words the setinputsizes() call is effectively ignored. For executemany(), however, that can't be done. For the scenario noted above, you can skip the call to setinputsizes() completely or you can use this instead to specify that you want to use decimal.Decimal. cursor.setinputsizes(x=decimal.Decimal) If I simply coerce to float, then issue #68 will reappear. :-) One other possibility is to do a similar thing to execute() -- namely, if the setinputsizes() call is invalid, ignore it and create the variable based on the input data instead. Any thoughts on that? |
commented Aug 31, 2017
So as you know I'm just in the middle here, my users have SQLAlchemy installed and so far we had it so that when they upgrade cx_Oracle from 5.x to 6, first due to #68 they'd lose precision, and now on a minor point release numeric support breaks entirely. I can push out new SQLAlchemy's but changing the setinputsizes behavior here, if I do that in older SQLAlchemy versions, now is that going to break people that are pinned on cx_Oracle 5.x or not. This breaks-in-a-new-way-each-time is creating a lot of release work for me and as far as bigger setinputsizes changes I'd really rather not have to backport that if possible. |
commented Aug 31, 2017
I would favor this if it means a loss in performance until I can get the correct improvements ported downstream, graceful degradation is always preferred. |
commented Aug 31, 2017
I do understand and am really not trying to make your life more difficult! Please keep reporting issues and I'll do my best to resolve them for you. Its the only way I can figure out other people's requirements! Further work is being done on the test suite so that future issues like these won't take place, too. I just checked and cursor.setinputsizes(decimal.Decimal) is not supported in cx_Oracle 5.x. If you avoid the call to setinputsizes() it works fine, but the direct call to cursor.setinputsizes() doesn't work, unfortunately. I could add that support as it is very simple to do, but that wouldn't help you out very much so I'll just leave that alone. I'll look into what changes would be required to support the graceful degradation (ie cursor.setinputsizes() is ignored) and report back later. |
commented Aug 31, 2017
OK that's fine, I'm in an intense period of getting SQLAlchemy 1.2 going so I can likely modernize setinputsizes() there, your earlier suggestion that I just don't need to call it at all for numerics is likely the best for me, that will work on 5.x also ? |
commented Aug 31, 2017
also thanks a ton for moving to github and having great responses here. I've been working with cx_Oracle for years and it's only now that I finally get to have a dialogue with you :) |
commented Aug 31, 2017
Yes, that should work just fine.
You're very welcome. It was a good move all around. :-) |
Cx Oracle Python Executemany Example Pdf
- pin on cx_Oracle 6.0.1 for the moment while we wait for
- pin on cx_Oracle 6.0.1 for the moment while we wait for
Ensure that a call to setinputsizes() with an invalid type prior to a…
Ensure that a call to setinputsizes() with an invalid type prior to a…
added the bug label Sep 1, 2017
commented Sep 1, 2017
I have added a commit to master and the v6.0.x branch (what will become 6.0.3) that addresses this issue. Specifically, if a call to setinputsizes() is made with the wrong type prior to calling executemany(), no attempt is made to create a new variable like with execute(). This issue is present in cx_Oracle 5.x as well. Just change your code to say cursor.setinputsizes(x=str) to see what I mean! |
closed this Sep 1, 2017
commented Sep 1, 2017
Cx Oracle Python Executemany Example For Kids
oh definitely, I had to disallow putting string types in setinputsizes from the beginning, things broke. for 1.2 I'm going with nothing going into setinputsizes except the LOB types. |
commented Sep 1, 2017
Sounds good. Note that passing a long string (or long bytes) when executing insert/update statements can be considerably faster than using LOBs if you pass the entire value at once. I'm not sure whether that is relevant for you so just giving you that factoid and you can do with it what you want! :-) |
- pin on cx_Oracle 6.0.1 for the moment while we wait for
Closed
Cx Oracle Python Executemany Example For Students
Closed
Cx Oracle Python Executemany Example List
Sign up for freeto join this conversation on GitHub. Already have an account? Sign in to comment