summaryrefslogtreecommitdiff
path: root/test/dialect/oracle/test_dialect.py
Commit message (Collapse)AuthorAgeFilesLines
* Improve ``oracledb`` thick mode flag.Federico Caselli2023-02-141-7/+14
| | | | | | | | Adjusted ``oracledb`` thick mode flag to make ``thick_mode=False`` not enable thick mode. Previously only ``None`` was accepted as off value. Fixes: #9295 Change-Id: I1a8397c19d065dfc2dda597e719922fc8d31acb1
* Oracle COLUMN_VALUE is a column name, not a keywordMike Bayer2022-12-071-5/+16
| | | | | | | | | Fixed issue in Oracle compiler where the syntax for :meth:`.FunctionElement.column_valued` was incorrect, rendering the name ``COLUMN_VALUE`` without qualifying the source table correctly. Fixes: #8945 Change-Id: Ia04bbdc68168e78b67a74bb3834a63f5d5000627
* Try running pyupgrade on the codeFederico Caselli2022-11-161-2/+0
| | | | | | | | command run is "pyupgrade --py37-plus --keep-runtime-typing --keep-percent-format <files...>" pyupgrade will change assert_ to assertTrue. That was reverted since assertTrue does not exists in sqlalchemy fixtures Change-Id: Ie1ed2675c7b11d893d78e028aad0d1576baebb55
* Tighten password security by removing `URL.__str__`Yassen Damyanov2022-09-231-1/+1
| | | | | | | | | | | | | | | For improved security, the :class:`_url.URL` object will now use password obfuscation by default when ``str(url)`` is called. To stringify a URL with cleartext password, the :meth:`_url.URL.render_as_string` may be used, passing the :paramref:`_url.URL.render_as_string.hide_password` parameter as ``False``. Thanks to our contributors for this pull request. Fixes: #8567 Closes: #8563 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/8563 Pull-request-sha: d1f1127f753849eb70b8d6cc64badf34e1b9219b Change-Id: If756c8073ff99ac83876d9833c8fe1d7c76211f9
* Use FETCH FIRST N ROWS / OFFSET for Oracle LIMIT/OFFSETMike Bayer2022-07-201-12/+13
| | | | | | | | | | | | | | | | | | | | | Oracle will now use FETCH FIRST N ROWS / OFFSET syntax for limit/offset support by default for Oracle 12c and above. This syntax was already available when :meth:`_sql.Select.fetch` were used directly, it's now implied for :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset` as well. I'm currently setting this up so that the new syntax renders in Oracle using POSTCOMPILE binds. I really have no indication if Oracle's SQL optimizer would be better with params here, so that it can cache the SQL plan, or if it expects hardcoded numbers for these. Since we had reports that the previous ROWNUM thing really needed hardcoded ints, let's guess for now that hardcoded ints would be preferable. it can be turned off with a single boolean if users report that they'd prefer real bound values. Fixes: #8221 Change-Id: I812ec24ffc947199866947b666d6ec6e6a690f22
* update cx_Oracle / oracledb LOB handlingMike Bayer2022-06-101-2/+2
| | | | | | | | | | Adjustments made to the BLOB / CLOB / NCLOB datatypes in the cx_Oracle and oracledb dialects, to improve performance based on recommendations from Oracle developers. References: https://github.com/oracle/python-cx_Oracle/issues/596 Fixes: #7494 Change-Id: I0d8cc3579140aa65cacf5b7d3373f7e1929a8f85
* Merge "Add support for the new oracle driver ``oracledb``." into mainmike bayer2022-06-071-68/+162
|\
| * Add support for the new oracle driver ``oracledb``.Federico Caselli2022-06-071-68/+162
| | | | | | | | | | Fixes: #8054 Change-Id: Idd7c1bbb7ca39499f53bdf59a63a6a9d65f144a5
* | Generalize RETURNING and suppor for MariaDB / SQLiteDaniel Black2022-06-021-1/+1
|/ | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | As almost every dialect supports RETURNING now, RETURNING is also made more of a default assumption. * the default compiler generates a RETURNING clause now when specified; CompileError is no longer raised. * The dialect-level implicit_returning parameter now has no effect. It's not fully clear if there are real world cases relying on the dialect-level parameter, so we will see once 2.0 is released. ORM-level RETURNING can be disabled at the table level, and perhaps "implicit returning" should become an ORM-level option at some point as that's where it applies. * Altered ORM update() / delete() to respect table-level implicit returning for fetch. * Since MariaDB doesnt support UPDATE returning, "full_returning" is now split into insert_returning, update_returning, delete_returning * Crazy new thing. Dialects that have *both* cursor.lastrowid *and* returning. so now we can pick between them for SQLite and mariadb. so, we are trying to keep it on .lastrowid for simple inserts with an autoincrement column, this helps with some edge case test scenarios and i bet .lastrowid is faster anyway. any return_defaults() / multiparams etc then we use returning * SQLite decided they dont want to return rows that match in ON CONFLICT. this is flat out wrong, but for now we need to work with it. Fixes: #6195 Fixes: #7011 Closes: #7047 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7047 Pull-request-sha: d25d5ea3abe094f282c53c7dd87f5f53a9e85248 Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com> Change-Id: I9908ce0ff7bdc50bd5b27722081767c31c19a950
* apply bindparam escape name to processors dictionaryMike Bayer2022-05-251-0/+19
| | | | | | | | | | | | | Fixed SQL compiler issue where the "bind processing" function for a bound parameter would not be correctly applied to a bound value if the bound parameter's name were "escaped". Concretely, this applies, among other cases, to Oracle when a :class:`.Column` has a name that itself requires quoting, such that the quoting-required name is then used for the bound parameters generated within DML statements, and the datatype in use requires bind processing, such as the :class:`.Enum` datatype. Fixes: #8053 Change-Id: I39d060a87e240b4ebcfccaa9c535e971b7255d99
* deprecate .connection on _ConnectionFairy, _ConnectionRecordMike Bayer2022-05-221-2/+2
| | | | | | | | | | | | These are replaced by the read-only ManagesConnection.dbapi_connection attribute. For some reason both of these objects had "setter" for .connection as well; there's no use case for that at all so just remove setter logic entirely. Fixes: #6981 Change-Id: I6425de4a017f6370e1a7476cd491cabc55e55e67
* pep484: schema APIMike Bayer2022-04-151-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | implement strict typing for schema.py this module has lots of public API, lots of old decisions and very hard to follow construction sequences in many cases, and is also where we get a lot of new feature requests, so strict typing should help keep things clean. among improvements here, fixed the pool .info getters and also figured out how to get ColumnCollection and related to be covariant so that we may set them up as returning Column or ColumnClause without any conflicts. DDL was affected, noting that superclasses of DDLElement (_DDLCompiles, added recently) can now be passed into "ddl_if" callables; reorganized ddl into ExecutableDDLElement as a new name for DDLElement and _DDLCompiles renamed to BaseDDLElement. setting up strict also located an API use case that is completely broken, which is connection.execute(some_default) returns a scalar value. This case has been deprecated and new paths have been set up so that connection.scalar() may be used. This likely wasn't possible in previous versions because scalar() would assume a CursorResult. The scalar() change also impacts Session as we have explicit support (since someone had reported it as a regression) for session.execute(Sequence()) to work. They will get the same deprecation message (which omits the word "Connection", just uses ".execute()" and ".scalar()") and they can then use Session.scalar() as well. Getting this to type correctly while still supporting ORM use cases required some refactoring, and I also set up a keyword only delimeter for Session.execute() and related as execution_options / bind_arguments should always be keyword only, applied these changes to AsyncSession as well. Additionally simpify Table __init__ now that we are Python 3 only, we can have positional plus explicit kwargs finally. Simplify Column.__init__ as well again taking advantage of kw only arguments. Fill in most/all __init__ methods in sqltypes.py as the constructor for types is most of the API. should likely do this for dialect-specific types as well. Apply _InfoType for all info attributes as should have been done originally and update descriptor decorators. Change-Id: I3f9f8ff3f1c8858471ff4545ac83d68c88107527
* cx_Oracle modernizeMike Bayer2022-04-071-15/+20
| | | | | | | | | | | | | | | | | | | Full "RETURNING" support is implemented for the cx_Oracle dialect, meaning multiple RETURNING rows are now recived for DML statements that produce more than one row for RETURNING. cx_Oracle 7 is now the minimum version for cx_Oracle. Getting Oracle to do multirow returning took about 5 minutes. however, getting Oracle's RETURNING system to integrate with ORM-enabled insert, update, delete, is a big deal because that architecture wasn't really working very robustly, including some recent changes in 1.4 for FromStatement were done in a hurry, so this patch also cleans up the FromStatement situation and begins to establish it more concretely as the base for all ReturnsRows / TextClause ORM scenarios. Fixes: #6245 Change-Id: I2b4e6007affa51ce311d2d5baa3917f356ab961f
* support cx_Oracle DPI disconnect codesMike Bayer2022-02-241-3/+51
| | | | | | | | | Added support to parse "DPI" error codes from cx_Oracle exception objects such as ``DPI-1080`` and ``DPI-1010``, both of which now indicate a disconnect scenario as of cx_Oracle 8.3. Fixes: #7748 Change-Id: I4a10d606d512c0d7f9b4653c47ea5734afffb8a5
* Accommodate escaped_bind_names for defaults/insert paramsMike Bayer2022-02-081-0/+29
| | | | | | | | | | Fixed issue in Oracle dialect where using a column name that requires quoting when written as a bound parameter, such as ``"_id"``, would not correctly track a Python generated default value due to the bound-parameter rewriting missing this value, causing an Oracle error to be raised. Fixes: #7676 Change-Id: I5a54426d24f2f9b336e3597d5595fb3e031aad97
* Update Black's target-version to py37Hugo van Kemenade2022-01-051-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | <!-- Provide a general summary of your proposed changes in the Title field above --> ### Description <!-- Describe your changes in detail --> Black's `target-version` was still set to `['py27', 'py36']`. Set it to `[py37]` instead. Also update Black and other pre-commit hooks and re-format with Black. ### Checklist <!-- go over following points. check them with an `x` if they do apply, (they turn into clickable checkboxes once the PR is submitted, so no need to do everything at once) --> This pull request is: - [ ] A documentation / typographical error fix - Good to go, no issue or tests are needed - [ ] A short code fix - please include the issue number, and create an issue if none exists, which must include a complete example of the issue. one line code fixes without an issue and demonstration will not be accepted. - Please include: `Fixes: #<issue number>` in the commit message - please include tests. one line code fixes without tests will not be accepted. - [ ] A new feature implementation - please include the issue number, and create an issue if none exists, which must include a complete example of how the feature would look. - Please include: `Fixes: #<issue number>` in the commit message - please include tests. **Have a nice day!** Closes: #7536 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7536 Pull-request-sha: b3aedf5570d7e0ba6c354e5989835260d0591b08 Change-Id: I8be85636fd2c9449b07a8626050c8bd35bd119d5
* Clean up most py3k compatFederico Caselli2021-11-241-11/+9
| | | | Change-Id: I8172fdcc3103ff92aa049827728484c8779af6b7
* Merge "Deprecate create_engine.implicit_returning" into mainmike bayer2021-11-181-5/+4
|\
| * Deprecate create_engine.implicit_returningjonathan vanasco2021-11-091-5/+4
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The :paramref:`_sa.create_engine.implicit_returning` parameter is deprecated on the :func:`_sa.create_engine` function only; the parameter remains available on the :class:`_schema.Table` object. This parameter was originally intended to enable the "implicit returning" feature of SQLAlchemy when it was first developed and was not enabled by default. Under modern use, there's no reason this parameter should be disabled, and it has been observed to cause confusion as it degrades performance and makes it more difficult for the ORM to retrieve recently inserted server defaults. The parameter remains available on :class:`_schema.Table` to specifically suit database-level edge cases which make RETURNING infeasible, the sole example currently being SQL Server's limitation that INSERT RETURNING may not be used on a table that has INSERT triggers on it. Also removed from the Oracle dialect some logic that would upgrade an Oracle 8/8i server version to use implicit returning if the parameter were explictly passed; these versions of Oracle still support RETURNING so the feature is now enabled for all Oracle versions. Fixes: #6962 Change-Id: Ib338e300cd7c8026c3083043f645084a8211aed8
* | removals: all unicode encoding / decodingMike Bayer2021-11-101-15/+16
|/ | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Removed here includes: * convert_unicode parameters * encoding create_engine() parameter * description encoding support * "non-unicode fallback" modes under Python 2 * String symbols regarding Python 2 non-unicode fallbacks * any concept of DBAPIs that don't accept unicode statements, unicode bound parameters, or that return bytes for strings anywhere except an explicit Binary / BLOB type * unicode processors in Python / C Risk factors: * Whether all DBAPIs do in fact return Unicode objects for all entries in cursor.description now * There was logic for mysql-connector trying to determine description encoding. A quick test shows Unicode coming back but it's not clear if there are still edge cases where they return bytes. if so, these are bugs in that driver, and at most we would only work around it in the mysql-connector DBAPI itself (but we won't do that either). * It seems like Oracle 8 was not expecting unicode bound parameters. I'm assuming this was all Python 2 stuff and does not apply for modern cx_Oracle under Python 3. * third party dialects relying upon built in unicode encoding/decoding but it's hard to imagine any non-SQLAlchemy database driver not dealing exclusively in Python unicode strings in Python 3 Change-Id: I97d762ef6d4dd836487b714d57d8136d0310f28a References: #7257
* First round of removal of python 2Federico Caselli2021-11-011-43/+2
| | | | | References: #4600 Change-Id: I61e35bc93fe95610ae75b31c18a3282558cd4ffe
* Fix MSSQL / Oracle limit/offset regressionsFederico Caselli2021-03-311-0/+9
| | | | | | | | | | | | | | Fixed a regression in MSSQL 2012+ that prevented the order clause to be rendered when ``offset=0`` is used in a subquery. Fixed critical regression where the Oracle compiler would not maintain the correct parameter values in the LIMIT/OFFSET for a select due to a caching issue. Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com> Fixes: #6163 Fixes: #6173 Change-Id: Ieb12354271d09ad935d684ee0db4fa0128837215
* Implement support for functions as FROM with columns clause supportMike Bayer2021-02-031-0/+108
| | | | | | | | | | | | | | | | Implemented support for "table valued functions" along with additional syntaxes supported by PostgreSQL, one of the most commonly requested features. Table valued functions are SQL functions that return lists of values or rows, and are prevalent in PostgreSQL in the area of JSON functions, where the "table value" is commonly referred towards as the "record" datatype. Table valued functions are also supported by Oracle and SQL Server. Moved from I5b093b72533ef695293e737eb75850b9713e5e03 due to accidental push Fixes: #3566 Change-Id: Iea36d04c80a5ed3509dcdd9ebf0701687143fef5
* Revert "Implement support for functions as FROM with columns clause support"Mike Bayer2021-01-211-108/+0
| | | | | | | This reverts commit 05a31f2708590161d4b3b4c7ff65196c99b4a22b. Atom has this little button called "push" and just pushes to master, I wasn't even *on* master. oops
* Implement support for functions as FROM with columns clause supportMike Bayer2021-01-201-0/+108
| | | | | | | WIP Fixes: #3566 Change-Id: I5b093b72533ef695293e737eb75850b9713e5e03
* update execute() arg formats in modules and testsMike Bayer2021-01-151-2/+2
| | | | | | | | | | | | | continuing with producing a SQLAlchemy 1.4.0b2 that internally does not emit any of its own 2.0 deprecation warnings, migrate the *args and **kwargs passed to execute() methods that now must be a single list or dictionary. Alembic 1.5 is again waiting on this internal consistency to be present so that it can pass all tests with no 2.0 deprecation warnings. Change-Id: If6b792e57c8c5dff205419644ab68e631575a2fa
* reinvent xdist hooks in terms of pytest fixturesMike Bayer2021-01-131-2/+2
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | To allow the "connection" pytest fixture and others work correctly in conjunction with setup/teardown that expects to be external to the transaction, remove and prevent any usage of "xdist" style names that are hardcoded by pytest to run inside of fixtures, even function level ones. Instead use pytest autouse fixtures to implement our own r"setup|teardown_test(?:_class)?" methods so that we can ensure function-scoped fixtures are run within them. A new more explicit flow is set up within plugin_base and pytestplugin such that the order of setup/teardown steps, which there are now many, is fully documented and controllable. New granularity has been added to the test teardown phase to distinguish between "end of the test" when lock-holding structures on connections should be released to allow for table drops, vs. "end of the test plus its teardown steps" when we can perform final cleanup on connections and run assertions that everything is closed out. From there we can remove most of the defensive "tear down everything" logic inside of engines which for many years would frequently dispose of pools over and over again, creating for a broken and expensive connection flow. A quick test shows that running test/sql/ against a single Postgresql engine with the new approach uses 75% fewer new connections, creating 42 new connections total, vs. 164 new connections total with the previous system. As part of this, the new fixtures metadata/connection/future_connection have been integrated such that they can be combined together effectively. The fixture_session(), provide_metadata() fixtures have been improved, including that fixture_session() now strongly references sessions which are explicitly torn down before table drops occur afer a test. Major changes have been made to the ConnectionKiller such that it now features different "scopes" for testing engines and will limit its cleanup to those testing engines corresponding to end of test, end of test class, or end of test session. The system by which it tracks DBAPI connections has been reworked, is ultimately somewhat similar to how it worked before but is organized more clearly along with the proxy-tracking logic. A "testing_engine" fixture is also added that works as a pytest fixture rather than a standalone function. The connection cleanup logic should now be very robust, as we now can use the same global connection pools for the whole suite without ever disposing them, while also running a query for PostgreSQL locks remaining after every test and assert there are no open transactions leaking between tests at all. Additional steps are added that also accommodate for asyncio connections not explicitly closed, as is the case for legacy sync-style tests as well as the async tests themselves. As always, hundreds of tests are further refined to use the new fixtures where problems with loose connections were identified, largely as a result of the new PostgreSQL assertions, many more tests have moved from legacy patterns into the newest. An unfortunate discovery during the creation of this system is that autouse fixtures (as well as if they are set up by @pytest.mark.usefixtures) are not usable at our current scale with pytest 4.6.11 running under Python 2. It's unclear if this is due to the older version of pytest or how it implements itself for Python 2, as well as if the issue is CPU slowness or just large memory use, but collecting the full span of tests takes over a minute for a single process when any autouse fixtures are in place and on CI the jobs just time out after ten minutes. So at the moment this patch also reinvents a small version of "autouse" fixtures when py2k is running, which skips generating the real fixture and instead uses two global pytest fixtures (which don't seem to impact performance) to invoke the "autouse" fixtures ourselves outside of pytest. This will limit our ability to do more with fixtures until we can remove py2k support. py.test is still observed to be much slower in collection in the 4.6.11 version compared to modern 6.2 versions, so add support for new TOX_POSTGRESQL_PY2K and TOX_MYSQL_PY2K environment variables that will run the suite for fewer backends under Python 2. For Python 3 pin pytest to modern 6.2 versions where performance for collection has been improved greatly. Includes the following improvements: Fixed bug in asyncio connection pool where ``asyncio.TimeoutError`` would be raised rather than :class:`.exc.TimeoutError`. Also repaired the :paramref:`_sa.create_engine.pool_timeout` parameter set to zero when using the async engine, which previously would ignore the timeout and block rather than timing out immediately as is the behavior with regular :class:`.QueuePool`. For asyncio the connection pool will now also not interact at all with an asyncio connection whose ConnectionFairy is being garbage collected; a warning that the connection was not properly closed is emitted and the connection is discarded. Within the test suite the ConnectionKiller is now maintaining strong references to all DBAPI connections and ensuring they are released when tests end, including those whose ConnectionFairy proxies are GCed. Identified cx_Oracle.stmtcachesize as a major factor in Oracle test scalability issues, this can be reset on a per-test basis rather than setting it to zero across the board. the addition of this flag has resolved the long-standing oracle "two task" error problem. For SQL Server, changed the temp table style used by the "suite" tests to be the double-pound-sign, i.e. global, variety, which is much easier to test generically. There are already reflection tests that are more finely tuned to both styles of temp table within the mssql test suite. Additionally, added an extra step to the "dropfirst" mechanism for SQL Server that will remove all foreign key constraints first as some issues were observed when using this flag when multiple schemas had not been torn down. Identified and fixed two subtle failure modes in the engine, when commit/rollback fails in a begin() context manager, the connection is explicitly closed, and when "initialize()" fails on the first new connection of a dialect, the transactional state on that connection is still rolled back. Fixes: #5826 Fixes: #5827 Change-Id: Ib1d05cb8c7cf84f9a4bfd23df397dc23c9329bfe
* remove metadata.bind use from test suiteMike Bayer2021-01-031-21/+11
| | | | | | | | | | | | | | importantly this means we can remove bound metadata from the fixtures that are used by Alembic's test suite. hopefully this is the last one that has to happen to allow Alembic to be fully 1.4/2.0. Start moving from @testing.provide_metadata to a pytest metadata fixture. This does not seem to have any negative effects even though TablesTest uses a "self.metadata" attribute. Change-Id: Iae6ab95938a7e92b6d42086aec534af27b5577d3
* Merge "test fixes for oracle 18c"mike bayer2020-12-181-1/+1
|\
| * test fixes for oracle 18cMike Bayer2020-12-181-1/+1
| | | | | | | | Change-Id: I4968aa3bde3c4d11d7fe84f18b4a846ba357d16a
* | Gracefully degrade on v$transaction not readableMike Bayer2020-12-181-0/+67
|/ | | | | | | | | | | | | | | | Fixed regression which occured due to [ticket:5755] which implemented isolation level support for Oracle. It has been reported that many Oracle accounts don't actually have permission to query the ``v$transaction`` view so this feature has been altered to gracefully fallback when it fails upon database connect, where the dialect will assume "READ COMMITTED" is the default isolation level as was the case prior to SQLAlchemy 1.3.21. However, explicit use of the :meth:`_engine.Connection.get_isolation_level` method must now necessarily raise an exception, as Oracle databases with this restriction explicitly disallow the user from reading the current isolation level. Fixes: #5784 Change-Id: Iefc82928744f3c944c18ae8000eb3c9e52e523bc
* Merge "correct for "autocommit" deprecation warning"mike bayer2020-12-111-43/+50
|\
| * correct for "autocommit" deprecation warningMike Bayer2020-12-111-43/+50
| | | | | | | | | | | | | | | | | | | | | | | | Ensure no autocommit warnings occur internally or within tests. Also includes fixes for SQL Server full text tests which apparently have not been working at all for a long time, as it used long removed APIs. CI has not had fulltext running for some years and is now installed. Change-Id: Id806e1856c9da9f0a9eac88cebc7a94ecc95eb96
* | Implement Oracle SERIALIZABLE + real read of isolation levelMike Bayer2020-12-091-1/+7
|/ | | | | | | | | | | | | | There's some significant awkwardness in that we can't read the level unless a transaction is started, which normally does not occur unless DML is emitted. The implementation uses the local_transaction_id function to start a transaction. It is not known what the performance impact of this might have, however by default the function is called only once on first connect and later only if the get_isolation_level() method is used. Fixes: #5755 Change-Id: I0453a6b0a49420826707f660931002ba2338fbf0
* Support pool.connect() event firing before all elseMike Bayer2020-11-191-0/+46
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Fixed regression where a connection pool event specified with a keyword, most notably ``insert=True``, would be lost when the event were set up. This would prevent startup events that need to fire before dialect-level events from working correctly. The internal mechanics of the engine connection routine has been altered such that it's now guaranteed that a user-defined event handler for the :meth:`_pool.PoolEvents.connect` handler, when established using ``insert=True``, will allow an event handler to run that is definitely invoked **before** any dialect-specific initialization starts up, most notably when it does things like detect default schema name. Previously, this would occur in most cases but not unconditionally. A new example is added to the schema documentation illustrating how to establish the "default schema name" within an on-connect event (upcoming as part of I882edd5bbe06ee5b4d0a9c148854a57b2bcd4741) Addiional changes to support setting default schema name: The Oracle dialect now uses ``select sys_context( 'userenv', 'current_schema' ) from dual`` to get the default schema name, rather than ``SELECT USER FROM DUAL``, to accommodate for changes to the session-local schema name under Oracle. Added a read/write ``.autocommit`` attribute to the DBAPI-adaptation layer for the asyncpg dialect. This so that when working with DBAPI-specific schemes that need to use "autocommit" directly with the DBAPI connection, the same ``.autocommit`` attribute which works with both psycopg2 as well as pg8000 is available. Fixes: #5716 Fixes: #5708 Change-Id: I7dce56b4345ffc720e25e2aaccb7e42bb29e5671
* Add reflection for Identity columnsFederico Caselli2020-09-281-6/+12
| | | | | | | | | | | | | | | | | | | | | | | | | Added support for reflecting "identity" columns, which are now returned as part of the structure returned by :meth:`_reflection.Inspector.get_columns`. When reflecting full :class:`_schema.Table` objects, identity columns will be represented using the :class:`_schema.Identity` construct. Fixed compilation error on oracle for sequence and identity column ``nominvalue`` and ``nomaxvalue`` options that require no space in them. Improved test compatibility with oracle 18. As part of the support for reflecting :class:`_schema.Identity` objects, the method :meth:`_reflection.Inspector.get_columns` no longer returns ``mssql_identity_start`` and ``mssql_identity_increment`` as part of the ``dialect_options``. Use the information in the ``identity`` key instead. The mssql dialect will assume that at least MSSQL 2005 is used. There is no hard exception raised if a previous version is detected, but operations may fail for older versions. Fixes: #5527 Fixes: #5324 Change-Id: If039fe637c46b424499e6bac54a2cbc0dc54cb57
* upgrade to black 20.8b1Mike Bayer2020-09-281-9/+32
| | | | | | | It's better, the majority of these changes look more readable to me. also found some docstrings that had formatting / quoting issues. Change-Id: I582a45fde3a5648b2f36bab96bad56881321899b
* Add future=True to create_engine/Session; unify select()Mike Bayer2020-07-081-8/+6
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Several weeks of using the future_select() construct has led to the proposal there be just one select() construct again which features the new join() method, and otherwise accepts both the 1.x and 2.x argument styles. This would make migration simpler and reduce confusion. However, confusion may be increased by the fact that select().join() is different Current thinking is we may be better off with a few hard behavioral changes to old and relatively unknown APIs rather than trying to play both sides within two extremely similar but subtly different APIs. At the moment, the .join() thing seems to be the only behavioral change that occurs without the user taking any explicit steps. Session.execute() will still behave the old way as we are adding a future flag. This change also adds the "future" flag to Session() and session.execute(), so that interpretation of the incoming statement, as well as that the new style result is returned, does not occur for existing applications unless they add the use of this flag. The change in general is moving the "removed in 2.0" system further along where we want the test suite to fully pass even if the SQLALCHEMY_WARN_20 flag is set. Get many tests to pass when SQLALCHEMY_WARN_20 is set; this should be ongoing after this patch merges. Improve the RemovedIn20 warning; these are all deprecated "since" 1.4, so ensure that's what the messages read. Make sure the inforamtion link is on all warnings. Add deprecation warnings for parameters present and add warnings to all FromClause.select() types of methods. Fixes: #5379 Fixes: #5284 Change-Id: I765a0b912b3dcd0e995426427d8bb7997cbffd51 References: #5159
* Use cx_oracle.LONG_STRING /LONG_BINARY for CLOB/BLOBMike Bayer2020-05-081-60/+26
| | | | | | | | | | | | | | Changed the implementation of fetching CLOB and BLOB objects to use cx_Oracle's native implementation which fetches CLOB/BLOB objects inline with other result columns, rather than performing a separate fetch. As always, this can be disabled by setting auto_convert_lobs to False. As part of this change, the behavior of a CLOB that was given a blank string on INSERT now returns None on SELECT, which is now consistent with that of VARCHAR on Oracle. Fixes: #5314 Change-Id: I7b46c91704b6f5d6c157e083505dac6e0cb3ef6e
* Clean up (engine|db).execute for oracleGord Thompson2020-04-061-33/+33
| | | | Change-Id: I6064fe348394152b2a47e83e43c469a153d34d27
* Deprecate plain string in execute and introduce `exec_driver_sql`Federico Caselli2020-03-211-26/+34
| | | | | | | | | | | | | | | Execution of literal sql string is deprecated in the :meth:`.Connection.execute` and a warning is raised when used stating that it will be coerced to :func:`.text` in a future release. To execute a raw sql string the new connection method :meth:`.Connection.exec_driver_sql` was added, that will retain the previous behavior, passing the string to the DBAPI driver unchanged. Usage of scalar or tuple positional parameters in :meth:`.Connection.execute` is also deprecated. Fixes: #4848 Fixes: #5178 Change-Id: I2830181054327996d594f7f0d59c157d477c3aa9
* Support for generated columnsCaselIT2019-11-081-0/+67
| | | | | | | | | | | | | | | | | | | | | | | | | | Added DDL support for "computed columns"; these are DDL column specifications for columns that have a server-computed value, either upon SELECT (known as "virtual") or at the point of which they are INSERTed or UPDATEd (known as "stored"). Support is established for Postgresql, MySQL, Oracle SQL Server and Firebird. Thanks to Federico Caselli for lots of work on this one. ORM round trip tests included. The ORM makes use of existing FetchedValue support and no additional ORM logic is present for the basic feature. It has been observed that Oracle RETURNING does not return the new value of a computed column upon UPDATE; it returns the prior value. As this is very dangerous, a warning is emitted if a computed column is rendered into the RETURNING clause of an UPDATE statement. Fixes: #4894 Closes: #4928 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4928 Pull-request-sha: d39c521d5ac6ebfb4fb5b53846451de79752e64c Change-Id: I2610b2999a5b1b127ed927dcdaeee98b769643ce
* Implment encoding_errors for cx_oracleMike Bayer2019-10-211-0/+195
| | | | | | | | | | | | | | | Added dialect-level flag ``encoding_errors`` to the cx_Oracle dialect, which can be specified as part of :func:`.create_engine`. This is passed to SQLAlchemy's unicode decoding converter under Python 2, and to cx_Oracle's ``cursor.var()`` object as the ``encodingErrors`` parameter under Python 3, for the very unusual case that broken encodings are present in the target database which cannot be fetched unless error handling is relaxed. The value is ultimately one of the Python "encoding errors" parameters passed to ``decode()``. Closes: #4801 Fixes: #4799 Change-Id: I1d542ba367bcd187347c54db1fee815f7890e71c
* Repair Oracle compat version check; dont warn if failedMike Bayer2019-10-091-0/+106
| | | | | | | | | | | | | Fixed regression in Oracle dialect that was inadvertently using max identifier length of 128 characters on Oracle server 12.2 and greater even though the stated contract for the remainder of the 1.3 series is that this value stays at 30 until version SQLAlchemy 1.4. Also repaired issues with the retrieval of the "compatibility" version, and removed the warning emitted when the "v$parameter" view was not accessible as this was causing user confusion. Fixes: #4898 Change-Id: Ieb7b3e093610896c5aa12d0789b63262e0ecf9d8
* Add max_identifier_length parameter; warn for OracleMike Bayer2019-10-021-0/+2
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | Added new :func:`.create_engine` parameter :paramref:`.create_engine.max_identifier_length`. This overrides the dialect-coded "max identifier length" in order to accommodate for databases that have recently changed this length and the SQLAlchemy dialect has not yet been adjusted to detect for that version. This parameter interacts with the existing :paramref:`.create_engine.label_length` parameter in that it establishes the maximum (and default) value for anonymously generated labels. The Oracle dialect now emits a warning if Oracle version 12.2 or greater is used, and the :paramref:`.create_engine.max_identifier_length` parameter is not set. The version in this specific case defaults to that of the "compatibility" version set in the Oracle server configuration, not the actual server version. In version 1.4, the default max_identifier_length for 12.2 or greater will move to 128 characters. In order to maintain forwards compatibility, applications should set :paramref:`.create_engine.max_identifier_length` to 30 in order to maintain the same length behavior, or to 128 in order to test the upcoming behavior. This length determines among other things how generated constraint names are truncated for statements like ``CREATE CONSTRAINT`` and ``DROP CONSTRAINT``, which means a the new length may produce a name-mismatch against a name that was generated with the old length, impacting database migrations. Fixes: #4857 Change-Id: Ib62efb00c6180c375869029b57353d90385d7950
* Render LIMIT/OFFSET conditions after compile on select dialectsMike Bayer2019-08-301-0/+13
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | Added new "post compile parameters" feature. This feature allows a :func:`.bindparam` construct to have its value rendered into the SQL string before being passed to the DBAPI driver, but after the compilation step, using the "literal render" feature of the compiler. The immediate rationale for this feature is to support LIMIT/OFFSET schemes that don't work or perform well as bound parameters handled by the database driver, while still allowing for SQLAlchemy SQL constructs to be cacheable in their compiled form. The immediate targets for the new feature are the "TOP N" clause used by SQL Server (and Sybase) which does not support a bound parameter, as well as the "ROWNUM" and optional "FIRST_ROWS()" schemes used by the Oracle dialect, the former of which has been known to perform better without bound parameters and the latter of which does not support a bound parameter. The feature builds upon the mechanisms first developed to support "expanding" parameters for IN expressions. As part of this feature, the Oracle ``use_binds_for_limits`` feature is turned on unconditionally and this flag is now deprecated. - adds limited support for "unique" bound parameters within a text() construct. - adds an additional int() check within the literal render function of the Integer datatype and tests that non-int values raise ValueError. Fixes: #4808 Change-Id: Iace97d544d1a7351ee07db970c6bc06a19c712c6
* Add deprecation warnings to all deprecated APIsMike Bayer2019-01-231-9/+8
| | | | | | | | | | | | | | | A large change throughout the library has ensured that all objects, parameters, and behaviors which have been noted as deprecated or legacy now emit ``DeprecationWarning`` warnings when invoked. As the Python 3 interpreter now defaults to displaying deprecation warnings, as well as that modern test suites based on tools like tox and pytest tend to display deprecation warnings, this change should make it easier to note what API features are obsolete. See the notes added to the changelog and migration notes for further details. Fixes: #4393 Change-Id: If0ea11a1fc24f9a8029352eeadfc49a7a54c0a1b
* Post black reformattingMike Bayer2019-01-061-24/+24
| | | | | | | | | | | | | Applied on top of a pure run of black -l 79 in I7eda77fed3d8e73df84b3651fd6cfcfe858d4dc9, this set of changes resolves all remaining flake8 conditions for those codes we have enabled in setup.cfg. Included are resolutions for all remaining flake8 issues including shadowed builtins, long lines, import order, unused imports, duplicate imports, and docstring issues. Change-Id: I4f72d3ba1380dd601610ff80b8fb06a2aff8b0fe
* Run black -l 79 against all source filesMike Bayer2019-01-061-135/+150
| | | | | | | | | | | | | | This is a straight reformat run using black as is, with no edits applied at all. The black run will format code consistently, however in some cases that are prevalent in SQLAlchemy code it produces too-long lines. The too-long lines will be resolved in the following commit that will resolve all remaining flake8 issues including shadowed builtins, long lines, import order, unused imports, duplicate imports, and docstring issues. Change-Id: I7eda77fed3d8e73df84b3651fd6cfcfe858d4dc9
* Modernize cx_Oracle parametersMike Bayer2018-11-151-19/+111
| | | | | | | | | | Updated the parameters that can be sent to the cx_Oracle DBAPI to both allow for all current parameters as well as for future parameters not added yet. In addition, removed unused parameters that were deprecated in version 1.2, and additionally we are now defaulting "threaded" to False. Fixes: #4369 Change-Id: I599668960e7b2d5bd1f5e6850e10b5b3ec215ed3
View Lorry Controller Status