| [ Web Proxy ] |
| Viewing: https://tortoise.github.io/databases.html#databases | [Back] [Original] |
[logo]
[logo]
Tortoise ORM v1.1.7 Documentation
Tortoise currently supports the following databases:
SQLite (using aiosqlite
PostgreSQL >= 9.4 (using asyncpg or psycopg)
MySQL/MariaDB (using asyncmy or aiomysql)
Microsoft SQL Server (using asyncodbc)
To use, please ensure that corresponding asyncio driver is installed.
Tortoise supports specifying Database configuration in a URL form. The form is:
DB_TYPE://USERNAME:PASSWORD@HOST:PORT/DB_NAME?PARAM1=value&PARAM2=value
If password contains special characters it need to be URL encoded:
![[logo]](https://tortoise.github.io\/_static/tortoise.png){kind=link}
>>> import urllib.parse
>>> urllib.parse.quote_plus("kx%jj5/g")
'kx%25jj5%2Fg'
Note
Passwords containing % followed by valid hex digits (e.g., foo%bar)
may not be parsed correctly from a URL string, because the URL parser interprets
such sequences as percent-encoded characters. If your password contains %,
either percent-encode it (as shown above) or use the dict-based configuration
format instead, which bypasses URL parsing entirely:
await Tortoise.init(config={
"connections": {
"default": {
"engine": "tortoise.backends.asyncpg",
"credentials": {
"host": "127.0.0.1",
"port": 5432,
"user": "myuser",
"password": "ADM[r$VIS]",
"database": "mydb",
}
}
},
"apps": {
"models": {
"models": ["myapp.models"],
"default_connection": "default",
}
},
})
The supported DB_TYPE:
sqlite:Typically in the form of sqlite://DB_FILE
So if the DB_FILE is /data/db.sqlite3 then the string will be sqlite:///data/db.sqlite (note the three /s)
postgresUsing asyncpg:
Typically in the form of postgres://postgres:pass@db.host:5432/somedb
Or specifically asyncpg/psycopg using:
psycopg: psycopg://postgres:pass@db.host:5432/somedb
asyncpg: asyncpg://postgres:pass@db.host:5432/somedb
mysql:Typically in the form of mysql://myuser:mypass@db.host:3306/somedb
mssql:Typically in the form of mssql://myuser:mypass@db.host:1433/somedb?driver=the odbc driver
You can also pass driver options such as encrypt and
trust_server_certificate (or their ODBC-cased equivalents
Encrypt and TrustServerCertificate) which will be appended
to the DSN. For example:
mssql://myuser:mypass@db.host:1433/somedb?driver=ODBC%20Driver%2018%20for%20SQL%20Server&encrypt=no&trust_server_certificate=yes
Since each database has a different set of features we have a Capabilities that is registered on each client.
Primarily this is to work around larger-than SQL differences, or common issues.
True, requires_limit=False, inline_comment=False, supports_transactions=True, support_for_update=True, support_for_no_key_update=False, support_index_hint=False, support_update_limit_order_by=True, support_for_posix_regex_queries=False, support_json_attributes=False, can_rollback_ddl=False, support_returning=False)[source]DB Client Capabilities indicates the supported feature-set, and is also used to note common workarounds to deficiencies.
Defaults are set with the following standard:
Deficiencies: assume it is working right.
Features: assume it doesnt have it.
Dialect name of the DB Client driver.
TrueIs the DB an external Daemon we connect to?
FalseIndicates that this DB requires a LIMIT statement for
an OFFSET statement to work.
FalseIndicates that comments should be rendered in line with the DDL statement, and not as a separate statement.
TrueIndicates that this DB supports transactions.
TrueIndicates that this DB supports SELECT FOR UPDATE SQL statement.
Indicates that this DB supports SELECT FOR NO KEY UPDATE SQL statement.
FalseSupport force index or use index.
Truesupport update/delete with limit and order by.
Falseindicated if the db supports posix regex queries
Falseindicated if the db supports accessing json attributes
FalseWhether the database supports transactional DDL. Used to determine if migrations can be run atomically.
FalseIndicates that this DB supports INSERT RETURNING.
SQLite is an embedded database, and can run on a file or in-memory. Good database for local development or testing of code logic, but not recommended for production use.
Caution
SQLite doesnt support many of the common datatypes natively, although we do emulation where we can, not everything is perfect.
For example DecimalField has precision preserved by storing values as strings, except when doing aggregates/ordering on it. In those cases we have to cast to/from floating-point numbers.
Similarly case-insensitivity is only partially implemented.
DB URL is typically in the form of sqlite://DB_FILE
So if the DB_FILE is /data/db.sqlite3 then the string will be sqlite:///data/db.sqlite (note the three /s)
file_path:Path to SQLite3 file. :memory: is a special path that indicates in-memory database.
SQLite optional parameters is basically any of the PRAGMA statements documented here.
journal_mode (defaults to WAL):Specify SQLite journal mode.
journal_size_limit (defaults to 16384):The journal size.
foreign_keys (defaults to ON)Set to OFF to not enforce referential integrity.
DB URL is typically in the form of postgres://postgres:pass@db.host:5432/somedb, or, if connecting via Unix domain socket postgres:///somedb.
user:Username to connect with.
password:Password for username.
host:Network host that database is available at.
port:Network port that database is available at. (defaults to 5432)
database:Database to use.
PostgreSQL optional parameters are pass-though parameters to the driver, see here for more details.
minsize (defaults to 1):Minimum connection pool size
maxsize (defaults to 5):Maximum connection pool size
max_queries (defaults to 50000):Maximum no of queries before a connection is closed and replaced.
max_inactive_connection_lifetime (defaults to 300.0):Duration of inactive connection before assuming that it has gone stale, and force a re-connect.
schema (uses users default schema by default):A specific schema to use by default.
ssl (defaults to False``):Either True or a custom SSL context for self-signed certificates. See MSSQL/Oracle for more info.
In case any of user, password, host, port parameters is missing, we are letting asyncpg/psycopg retrieve it from default sources (standard PostgreSQL environment variables or default values).
DB URL is typically in the form of mysql://myuser:mypass@db.host:3306/somedb
user:Username to connect with.
password:Password for username.
host:Network host that database is available at.
port:Network port that database is available at. (defaults to 3306)
database:Database to use.
MySQL optional parameters are pass-though parameters to the driver, see here for more details.
minsize (defaults to 1):Minimum connection pool size
maxsize (defaults to 5):Maximum connection pool size
connect_timeout (defaults to None):Duration to wait for connection before throwing error.
echo (defaults to False):Set to True` to echo SQL queries (debug only)
charset (defaults to utf8mb4):Sets the character set in use
ssl (defaults to False):Either True or a custom SSL context for self-signed certificates. See MSSQL/Oracle for more info.
DB URL is typically in the form of mssql or oracle://myuser:mypass@db.host:1433/somedb?driver=the odbc driver
user:Username to connect with.
password:Password for username.
host:Network host that database is available at.
port:Network port that database is available at. (defaults to 1433)
database:Database to use.
driver:The ODBC driver to use. Actual name of the ODBC driver in your odbcinst.ini file (you can find its location using odbcinst -j command). It requires unixodbc to be installed in your system.
MSSQL/Oracle optional parameters are pass-though parameters to the driver, see here for more details.
minsize (defaults to 1):Minimum connection pool size
maxsize (defaults to 10):Maximum connection pool size
pool_recycle (defaults to -1):Pool recycle timeout in seconds.
echo (defaults to False):Set to True to echo SQL queries (debug only)
If you get ??? values in Varchar fields instead of your actual text (russian/chinese/etc), then set NLS_LANG variable in your client environment to support UTF8. For example, American_America.UTF8.
To pass in a custom SSL Cert, one has to use the verbose init structure as the URL parser cant handle complex objects.
# Here we create a custom SSL context
import ssl
ctx = ssl.create_default_context()
# And in this example we disable validation...
# Please don't do this. Look at the official Python ``ssl`` module documentation
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_NONE
# Here we do a verbose init
await Tortoise.init(
config={
"connections": {
"default": {
"engine": "tortoise.backends.asyncpg",
"credentials": {
"database": None,
"host": "127.0.0.1",
"password": "moo",
"port": 54321,
"user": "postgres",
"ssl": ctx # Here we pass in the SSL context
}
}
},
"apps": {
"models": {
"models": ["some.models"],
"default_connection": "default",
}
},
}
)
The Base DB client interface is provided here, but should only be directly used as an advanced case.
True, **kwargs)[source]Base class for containing a DB connection.
Parameters get passed as kwargs, and is mostly driver specific.
Acquires a connection from the pool. Will return the current context connection if already in a transaction.
Closes the DB connection.
Created the database in the server. Typically only called by the test runner.
Need to have called create_connection()` with parameter with_db=False set to
use the default connection instead of the configured one, else you would get errors
indicating the database doesnt exist.
Delete the database from the Server. Typically only called by the test runner.
Need to have called create_connection()` with parameter with_db=False set to
use the default connection instead of the configured one, else you would get errors
indicating the database is in use.
Executes a RAW SQL insert statement, with provided parameters.
Executes a RAW bulk insert statement, like execute_insert, but returns no data.
None)[source]Executes a RAW SQL query statement, and returns the resultset.
None)[source]Executes a RAW SQL query statement, and returns the resultset as a list of dicts.
| Web Proxy Viewer | New URL | Original Page |