The set-up functions

dwopt.save_url(db_nme, url, method='keyring')[source]

Save encoded database engine url to keyring or config.

See notes for details, see examples for quick-start.

A sqlalchemy engine url combines the user name, password, database names, etc into a single string.

Parameters
  • db_nme (str) – Relevant database code. Either pg for postgre, lt for sqlite, or oc for oracle.

  • url (str) – Sqlalchemy engine url.

  • method (str) – Method used to save, either ‘keyring’, or ‘config’. Default ‘keyring’.

Returns

Message anouncing completion.

Return type

str

Notes

Credential locations:

  • The system keyring service is accessed via the keyring package. The service on Windows is the Windows Credential Manager. The service id is the full path to the dwopt package files. For example: E:\python\python3.9\Lib\site-packages\dwopt. The user name will be either pg, lt, or oc. The url will be encoded before saving.

  • The config file is created with name .dwopt on the system HOME directory. There will be a url section on the config file, with option names being the database names. The url will be encoded before saving.

  • The environment variables should be manually made with the names dwopt_pg, dwopt_lt or dwopt_oc, and the value being the raw url string.

Base 64 encoding is done to prevent raw password being stored on files. User could rewrite the _encode and the _decode functions to implement custom encryption algorithm.

On package import, default url are taken firstly from keyring if available, then the config file if available, then the environment variable if available, lastly a set of hard-coded testing urls.

To extend this feature and add additional default urls, add to the instantiation lines in the __init__.py file, and save url to the symbol, example:

dev = db(_get_url("lt_dev")) # add to __init__.py
dwopt.save_url('lt_dev', "sqlite://")# run in a session

Examples

Save connection urls in various methods for various databases. Also manually create following environment variable variable = value pair: dwopt_lt = sqlite:///:memory:.

>>> import dwopt
>>> dwopt.save_url('pg', 'postgresql://dwopt_tester:1234@localhost/dwopt_test')
'Saved pg url to keyring'
>>> url = ("oracle://dwopt_test:1234@localhost:1521/?service_name=XEPDB1"
...     "&encoding=UTF-8&nencoding=UTF-8")
>>>
>>> dwopt.save_url('oc', url, 'config')
'Saved oc url to config'

Exit and re-enter python for changes to take effect.

>>> from dwopt import pg, lt, oc
>>> pg.eng
Engine(postgresql://dwopt_tester:***@localhost/dwopt_test)
>>> lt.eng
Engine(sqlite:///:memory:)
>>> str(oc.eng)[:50]
'Engine(oracle://dwopt_test:***@localhost:1521/?enc'
dwopt.db(eng)[source]

The database operator object factory.

Parameters

eng (str, or sqlalchemy.engine.Engine) –

A sqlalchemy engine url, which combines the user name, password, database names, etc.

Alternatively a Database connection engine to be used. Use the dwopt.make_eng() function to make engine.

Returns

The relevant database operator object.

Return type

dwopt.dbo._Db

Examples

Produce a sqlite database operator object:

>>> from dwopt import db
>>> d = db("sqlite://")
>>> d.mtcars()
>>> d.run('select count(1) from mtcars')
   count(1)
0        32

Produce a postgre database operator object:

>>> from dwopt import db
>>> url = "postgresql://dwopt_tester:1234@localhost/dwopt_test"
>>> db(url).iris(q=True).len()
150

Produce using engine object:

>>> from dwopt import db, make_eng
>>> eng = make_eng("sqlite://")
>>> db(eng).mtcars(q=1).len()
32

Produce an oracle database operator object:

>>> from dwopt import db, Oc
>>> url = "oracle://scott2:tiger@tnsname"
>>> isinstance(db(url), Oc)
True
dwopt.Db(eng)[source]

Alias for dwopt.db()

dwopt.make_eng(url)[source]

Make database connection engine.

Use the database connection engine to instantiate the database opeartor class. This function is provided outside of the class because the engine object is best to be created only once per application.

A sqlalchemy engine url combines the user name, password, database names, etc into a single string.

Parameters

url (str) – Sqlalchemy engine url.

Return type

sqlalchemy engine

Examples

Instantiate and use a database operator object.

>>> from dwopt import make_eng, Pg
>>> url = "postgresql://dwopt_tester:1234@localhost/dwopt_test"
>>> pg_eng = make_eng(url)
>>> pg = Pg(pg_eng)
>>> pg.iris()
>>> pg.run('select count(1) from iris')
   count
0    150
dwopt.make_test_tbl(db, sch_tbl_nme='test', n=10000)[source]

Make or remake a test table on database.

Uses Sqlalchemy toolkits for table drop, creation, insertion.

Parameters
  • db (dwopt.dbo._Db, or str) – Dwopt database operator object. Or one of 'pg', 'lt', and 'oc', indicating usage of pre-defined testing database engines.

  • sch_tbl_nme (str) – Table name in form my_schema1.my_table1 or my_table1.

  • n (int) – Number of records.

Returns

Tuple of database operator used, and the test dataframe.

Return type

(dwopt.dbo._Db, pandas.DataFrame)

Notes

Table specifications

Column

Column type

Object type

None

Example

id

int64

int64

0

score

float64

float64

NaN

4.066531

amt

int64

int64

867

cat

object

str

None

train

dte

object

datetime.date

None

2022-03-03

time

datetime64[ns]

datetime64[ns]

NaT

2022-02-02 23:00:00

Test database table specifications

Column

Postgre

Sqlite

Oracle

id

bigint

integer

number

score

float8

real

float

amt

bigint

integer

number

cat

varchar(20)

text

varchar2(20)

dte

date

text

date

time

timestamp

text

date

These datatypes are implemented via closest Sqlalchemy datatypes.

The id column is made primary key in the test database tables.

Floating point types

The score column’s NaN objects are converted into None before insertion for oracle.

Datetime types

The time column’s NaT objects are converted into None before insertion for Postgre and Oracle. The time column are converted into str and None before insertion for Sqlite.

Pre-defined testing database engines

  • pg: postgresql://dwopt_tester:1234@localhost/dwopt_test

  • lt: sqlite://

  • oc: oracle://dwopt_test:1234@localhost:1521/?service_name=XEPDB1 &encoding=UTF-8&nencoding=UTF-8.

Install testing databases

Postgre

  • Install from postgresql windows.

  • Add postgres bin directory to path.

  • Run commands:

    psql -U postgres
    CREATE DATABASE dwopt_test;
    CREATE USER dwopt_tester WITH PASSWORD '1234';
    GRANT ALL PRIVILEGES ON DATABASE dwopt_test to dwopt_tester;
    

Oracle

  • Install from oracle xe.

  • Set ORACLE_HOME environment variable to point to installation location.

  • Run commands:

    cd /d %ORACLE_HOME%\bin
    sqlplus sys/[password]]@//localhost:1521/XEPDB1 as sysdba
    create user dwopt_test identified by 1234;
    grant create session to dwopt_test;
    grant create table to dwopt_test;
    grant unlimited tablespace to dwopt_test;
    

Examples

Make test table via provided database operator:

>>> from dwopt import lt, make_test_tbl
>>> _ = make_test_tbl(lt)
>>> lt.qry('test').len()
10000

Make database operator for the pre-defined test databases, then make test table on it:

>>> from dwopt import make_test_tbl
>>> lt, df = make_test_tbl('lt', 'foo', 999)
>>> lt.eng
Engine(sqlite://)
>>> lt.qry('foo').len()
999
>>> len(df)
999