catcher_modules.database package¶
Submodules¶
catcher_modules.database.couchbase module¶
-
class
catcher_modules.database.couchbase.
Couchbase
(**kwargs)[source]¶ Allows you to perform put/get/delete/query operations in Couchbase
Input: Conf: couchbase configuration. Is an object. Required. - bucket: bucket to work with
- user: database user (optional)
- host: database host (optional)
- password: user’s password
Put: put value in the database by the key. Get: get object by key. Delete: delete object by key. Query: query to run. Examples: Put value by key
couchbase: request: conf: bucket: test host: localhost put: key: my_key value: {foo: bar, baz: [1,2,3,4]}
Get value by key
couchbase: request: conf: bucket: test user: test password: test host: localhost get: key: my_key
Delete value by key
couchbase: request: conf: bucket: test user: test password: test host: localhost delete: key: my_key
Query by foo
couchbase: request: conf: bucket: test user: test password: test host: localhost query: "select `baz` from test where `foo` = 'bar'"
catcher_modules.database.postgres module¶
-
class
catcher_modules.database.postgres.
Postgres
(**kwargs)[source]¶ Allows you to run queries in Postgres
Input: Conf: postgres configuration. Can be a single line string or object. Dialect is not mandatory. Required. - dbname: name of the database to connect to
- user: database user
- host: database host
- password: user’s password
- port: database port
Query: query to run. Deprecated since 5.2 Sql: query or sql file from resources to run. Required Examples: Select all from test, use object configuration
postgres: request: conf: dbname: test user: user password: password host: localhost port: 5433 sql: 'select count(*) from test' register: {documents: '{{ OUTPUT }}'}
Run all commands from resources/my_ddl.sql, using string configuration
postgres: request: conf: 'user:password@localhost:5432/test' sql: 'my_ddl.sql'
Insert into test, using string configuration with dialect
postgres: request: conf: 'postgresql://user:password@localhost:5432/test' sql: 'insert into test(id, num) values(3, 3);'
catcher_modules.database.mongo module¶
-
class
catcher_modules.database.mongo.
Mongo
(**kwargs)[source]¶ Allows you to interact with MongoDB NoSQL database. :Input:
Conf: mongodb configuration. Can be a single line, object or object with url as a parameter (for Airflow connection) string url or kv object. Required.
- database: name of the database to connect to
- username: database user. Must be RFC 2396 encoded when in URI.
- host: database host
- password: user’s password. Must be RFC 2396 encoded when in URI.
- port: database port
- authSource: The database to authenticate on. Default is database.
See pymongo for more options. :collection: collection to use. Required
Command: String. Use this if you have to run command without any parameters. Where command’s value is your command to run, like command: find_one. Optional <command>: Object. Use this when you have command with parameters. Where <command> key is your command name and it’s value is parameter object (list or dict). Optional Either <command> or command should exist. Next: Run other operation just after your operation. Can be string like next: count or object with params next: {‘sort’: ‘author’}. You can chain multiple next (see example). `Optional List_params: Pass command params as different arguments. Useful when pymongo command takes several arguments (both *args and **kwargs). *args will be set in case of params in list while **kwargs will be sent in case of dict. See examples for more info. Examples: Find one document. Use command key when no params.
mongo: request: conf: database: test username: test password: test host: localhost port: 27017 collection: 'your_collection' command: 'find_one' register: {document: '{{ OUTPUT }}'}
Use object configuration with extra fields (for Airflow connection). This step will ignore everything except url. Inventory.yaml
mongo_conf: url: 'mongodb://username:password@host' type: 'mongo' extra: '{"key":"value"}'
mongo step itself
mongo: request: conf: '{{ mongo_conf }}' collection: 'your_collection' command: 'find_one'
See more info about connections population in Catcher-Airflow `docs https://catcher-modules.readthedocs.io/en/latest/source/airflow.html`_
Alternatively you can use
conf: '{{ mongo_conf.url }}'
.Insert into test, using string configuration
mongo: request: conf: 'mongodb://username:password@host' collection: 'your_collection' insert_one: 'author': 'Mike' 'text': 'My first blog post!' 'tags': ['mongodb', 'python', 'pymongo'] 'date': '{{ NOW_DT }}'
Find specific document
mongo: request: conf: database: test username: test password: test host: localhost port: 27017 collection: 'your_collection' find_one: {'author': 'Mike'} register: {document: '{{ OUTPUT }}'}
To find multiple documents just use find instead of find_one.
Bulk insert
mongo: request: conf: '{{ mongo_conf }}' collection: 'your_collection' insert_many: - {'foo': 'baz'} - {'foo': 'bar'}
Chaining operations: db.collection.find().sort().count()
mongo: request: conf: database: test username: test password: test host: localhost port: 27017 collection: 'your_collection' find: {'author': 'Mike'} next: sort: 'author' next: 'count' register: {document: '{{ OUTPUT }}'}
Will run every next operation on previous one. You can chain more than one operation.
Run operation with list parameters (**kwargs). Is useful when calling commands with additional arguments.
mongo: request: conf: database: test username: test password: test host: localhost port: 27017 collection: 'your_collection' find: filter: {'author': 'Mike'} projection: {'_id': False} list_params: true # pass list arguments as separate params register: {document: '{{ OUTPUT }}'}
Run operation with list parameters (*args). Run map-reduce.
mongo: request: conf: database: test username: test password: test host: localhost port: 27017 collection: 'your_collection' map_reduce: - 'function () { this.tags.forEach(function(z) { emit(z, 1); }); }' - 'function (key, values) { var total = 0; for (var i = 0; i < values.length; i++) { total += values[i]; } return total; }' - 'myresults' list_params: true # pass list arguments as separate params register: {document: '{{ OUTPUT }}'}
catcher_modules.database.oracle module¶
-
class
catcher_modules.database.oracle.
Oracle
(**kwargs)[source]¶ Allows you to run sql queries in OracleDB.
Input: Conf: oracle configuration. Can be a single line string or object. Dialect is not mandatory. Required. - dbname: name of the database to connect to
- user: database user
- host: database host
- password: user’s password
- port: database port
Query: query to run. Deprecated since 5.2 Sql: query or sql file from resources to run. Required Examples: Select all from test, use object configuration
oracle: request: conf: dbname: test user: user password: password host: localhost port: 1521 sql: 'select count(*) as count from test' register: {documents: '{{ OUTPUT }}'}
Insert into test, using string configuration
oracle: request: conf: 'user:password@localhost:1521/test' sql: 'insert into test(id, num) values(3, 3);'
Insert into test, using string configuration with dialect
oracle: request: conf: 'oracle+cx_oracle://user:password@localhost:1521/test' sql: 'insert into test(id, num) values(3, 3);'
catcher_modules.database.sqlite module¶
-
class
catcher_modules.database.sqlite.
SQLite
(**kwargs)[source]¶ Allows you to create SQLite database on your local filesystem and work with it. Important - for relative path use one slash /. For absolute slash - two //.
Input: Conf: sqlite path string. Dialect is not mandatory. Required. Query: query to run. Deprecated since 5.2 Sql: query or sql file from resources to run. Required Examples: Select all from test, use relative path
sqlite: request: conf: '/foo.db' sql: 'select count(*) as count from test' register: {documents: '{{ OUTPUT }}'}
Note that we alias count. For some reason sqlalchemy for sqlite will return count(*) as a column name instead of count.
Insert into test, using string absolute path (with 2 slashes)
sqlite: request: conf: '//absolute/path/to/foo.db' sql: 'insert into test(id, num) values(3, 3);'
catcher_modules.database.mysql module¶
-
class
catcher_modules.database.mysql.
MySql
(**kwargs)[source]¶ Allows you to run queries on MySQL (and all mysql compatible databases like MariaDB).
Input: Conf: mysql configuration. Can be a single line string or object. Dialect is not mandatory. Required. - dbname: name of the database to connect to
- user: database user
- host: database host
- password: user’s password
- port: database port
Query: query to run. Deprecated since 5.2 Sql: query or sql file from resources to run. Required Examples: Select all from test, use object configuration
mysql: request: conf: dbname: test user: user password: password host: localhost port: 3306 sql: 'select count(*) as count from test' register: {documents: '{{ OUTPUT }}'}
Note that we alias count. For some reason sqlalchemy for mysql will return count(*) as a column name instead of count.
Insert into test, using string configuration
mysql: request: conf: 'user:password@localhost:3306/test' sql: 'insert into test(id, num) values(3, 3);'
Insert into test, using string configuration with dialect
mysql: request: conf: 'mysql+pymysql://user:password@localhost:3306/test' sql: 'insert into test(id, num) values(3, 3);'
catcher_modules.database.mssql module¶
-
class
catcher_modules.database.mssql.
MSSql
(**kwargs)[source]¶ Allows you to run queries on Microsoft SQL Server.
Input: Conf: mssql configuration. Can be a single line string or object. Dialect is not mandatory. Required. - dbname: name of the database to connect to
- user: database user
- host: database host
- password: user’s password
- port: database port
- driver: odbc driver name you’ve installed. Optional If not specified, the default driver, which comes with
- catcher-modules Dockerfile will be used.
Query: query to run. Deprecated since 5.2 Sql: query or sql file from resources to run. Required Examples: Select all from test, use object configuration
mssql: request: conf: dbname: test user: user password: password host: localhost port: 1433 driver: ODBC Driver 17 for SQL Server sql: 'select count(*) as count from test' register: {documents: '{{ OUTPUT }}'}
Note that we alias count. For some reason sqlalchemy for mssql will return count(*) as a column name instead of count.
Insert into test, using string configuration
mssql: request: conf: 'user:password@localhost:5432/test' sql: 'insert into test(id, num) values(3, 3);'
Insert into test, using string configuration with pymssql (pymssql should be installed)
mssql: request: conf: 'mssql+pymssql://user:password@localhost:5432/test' sql: 'insert into test(id, num) values(3, 3);'