As an alternative to escaping special characters in order to create a completeURL string, the object passed to create_engine() may instead be aninstance of the URL object, which bypasses the parsingphase and can accommodate for unescaped strings directly. See the nextsection for an example.
The value passed to create_engine() may be an instance ofURL, instead of a plain string, which bypasses the need for stringparsing to be used, and therefore does not need an escaped URL string to beprovided.
**kwargs takes a wide variety of options which are routedtowards their appropriate components. Arguments may be specific tothe Engine, the underlying Dialect,as well as thePool. Specific dialects also accept keyword arguments thatare unique to that dialect. Here, we describe the parametersthat are common to most create_engine() usage.
Once established, the newly resulting Engine willrequest a connection from the underlying Pool onceEngine.connect() is called, or a method which depends on itsuch as Engine.execute() is invoked. ThePool in turnwill establish the first actual DBAPI connection when this requestis received. The create_engine() call itself does notestablish any actual DBAPI connections directly.
The create_engine.isolation_level parameter isin contrast to theConnection.execution_options.isolation_levelexecution option, which may be set on an individualConnection, as well as the same parameter passed toEngine.execution_options(), where it may be used to createmultiple engines with different isolation levels that share a commonconnection pool and dialect.
Changed in version 2.0: Thecreate_engine.isolation_levelparameter has been generalized to work on all dialects which supportthe concept of isolation level, and is provided as a more succinct,up front configuration switch in contrast to the execution optionwhich is more of an ad-hoc programmatic option.
URLs are typically constructed from a fully formatted URL string, where themake_url() function is used internally by thecreate_engine() function in order to parse the URL string intoits individual components, which are then used to construct a newURL object. When parsing from a formatted URL string, the parsingformat generally followsRFC-1738, with some exceptions.
For cases where special connection methods are needed, in the vast majorityof cases, it is most appropriate to use one of several hooks at thecreate_engine() level in order to customize this process. Theseare described in the following sub-sections.
The DialectEvents.do_connect() hook supersedes the previouscreate_engine.creator hook, which remains available.DialectEvents.do_connect() has the distinct advantage that thecomplete arguments parsed from the URL are also passed to the user-definedfunction which is not the case with create_engine.creator.
sqlalchemy.engine - controls SQL echoing. Set to logging.INFO forSQL query output, logging.DEBUG for query + result set output. Thesesettings are equivalent to echo=True and echo="debug" oncreate_engine.echo, respectively.
sqlalchemy.pool - controls connection pool logging. Set tologging.INFO to log connection invalidation and recycle events; set tologging.DEBUG to additionally log all pool checkins and checkouts.These settings are equivalent to pool_echo=True and pool_echo="debug"on create_engine.echo_pool, respectively.
The logger name of instance such as an Engine orPool defaults to using a truncated hex identifierstring. To set this to a specific name, use thecreate_engine.logging_name andcreate_engine.pool_logging_name withsqlalchemy.create_engine():
The Connection.execution_options.logging_token parametermay also be established on engines or sub-engines viacreate_engine.execution_options or Engine.execution_options().This may be useful to apply different logging tokens to different componentsof an application without creating new engines:
The logging emitted by Engine also indicates an excerptof the SQL parameters that are present for a particular statement. To preventthese parameters from being logged for privacy purposes, enable thecreate_engine.hide_parameters flag:
The typical usage of create_engine() is once per particular databaseURL, held globally for the lifetime of a single application process. A singleEngine manages many individual DBAPI connections on behalf ofthe process and is intended to be called upon in a concurrent fashion. TheEngine is not synonymous to the DBAPI connect() function, whichrepresents just one connection resource - the Engine is mostefficient when created just once at the module level of an application, notper-object or per-function call.
The Connection.execution_options.isolation_level option mayalso be set engine wide, as is often preferable. This may beachieved by passing the create_engine.isolation_levelparameter to create_engine():
The isolation level may also be set per engine, with a potentially greaterlevel of flexibility, using either thecreate_engine.execution_options parameter tocreate_engine() or the Engine.execution_options()method, the latter of which will create a copy of the Engine thatshares the dialect and connection pool of the original engine, but has its ownper-connection isolation level setting:
With the above setting, the DBAPI connection will be set to use a"REPEATABLE READ" isolation level setting for each new transactionbegun; but the connection as pooled will be reset to the original isolationlevel that was present when the connection first occurred. At the levelof create_engine(), the end effect is not any differentfrom using the create_engine.isolation_level parameter.
The above cache size of 1200 is actually fairly large. For small applications,a size of 100 is likely sufficient. To estimate the optimal size of the cache,assuming enough memory is present on the target host, the size of the cacheshould be based on the number of unique SQL strings that may be rendered for thetarget engine in use. The most expedient way to see this is to useSQL echoing, which is most directly enabled by using thecreate_engine.echo flag, or by using Python logging; see thesection Configuring Logging for background on logging configuration.
The caching badge we see for the first occurrence of each of these twostatements is [generated in 0.00011s]. This indicates that the statementwas not in the cache, was compiled into a String in .00011s and was thencached. When we see the [generated] badge, we know that this meansthere was a cache miss. This is to be expected for the first occurrence ofa particular statement. However, if lots of new [generated] badges areobserved for a long-running application that is generally using the same seriesof SQL statements over and over, this may be a sign that thecreate_engine.query_cache_size parameter is too small. When astatement that was cached is then evicted from the cache due to the LRUcache pruning lesser used items, it will display the [generated] badgewhen it is next used.
Above, the three lambda callables that are used to define the structureof a SELECT statement are invoked exactly once, and the resulting SQLstring cached in the compilation cache of the engine. From that pointforward, the run_my_statement() function may be invoked any numberof times and the lambda callables within it will not be called, onlyused as cache keys to retrieve the already-compiled SQL.
When a program uses multiprocessing or fork(), and anEngine object is copied to the child process,Engine.dispose() should be called so that the engine createsbrand new database connections local to that fork. Database connectionsgenerally do not travel across process boundaries. Use theEngine.dispose.close parameter set to False in this case.See the section Using Connection Pools with Multiprocessing or os.fork() for more background on thisuse case.
Connections that are checked out are not discarded when theengine is disposed or garbage collected, as these connections are stillstrongly referenced elsewhere by the application.However, after Engine.dispose() is called, thoseconnections are no longer associated with that Engine; when theyare closed, they will be returned to their now-orphaned connection poolwhich will ultimately be garbage collected, once all connections which referto it are also no longer referenced anywhere.Since this process is not easy to control, it is strongly recommended thatEngine.dispose() is called only after all checked out connectionsare checked in or otherwise de-associated from their pool.
A dictionary where Compiled objectswill be cached when the Connectioncompiles a clauseexpression into a Compiled object. This dictionary willsupersede the statement cache that may be configured on theEngine itself. If set to None, cachingis disabled, even if the engine has a configured cache size.
Set the transaction isolation level for the lifespan of thisConnection object.Valid values include those stringvalues accepted by the create_engine.isolation_levelparameter passed to create_engine(). These levels aresemi-database specific; see individual dialect documentation forvalid levels.
b2a8ae9291