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);'