The set-up functions

dwopt.save_url(db_nme: str, url: str | None = None, method: str = 'keyring', **kwargs)

Save or delete 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 or None) – Sqlalchemy engine url. None to delete url from keyring or config.

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

• kwargs (Additional engine creation parameters.) –

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.

  Does not work if no keyring service is not available on the system, see keyring package documentation for details.

• 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, or string representing dictionary of engine parameters, see examples.

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://.

>>> import dwopt
>>> dwopt.save_url('pg', 'postgresql://dwopt_tester:1234@localhost/dwopt_test')
'Saved pg url to keyring'
>>> url = ("oracle+oracledb://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://)
>>> str(oc.eng)[:50]
'Engine(oracle+oracledb://dwopt_test:***@localhost:'

Remove saved urls.

>>> dwopt.save_url('pg', url=None, method='keyring')
'Deleted pg url from keyring'
>>> dwopt.save_url('oc', method='config')
'Deleted oc url from config'

Save additional engine parameters alongside url, username for location of oracle bin to be replaced by actual username:

import dwopt
dwopt.save_url(
    db_nme='oc',
    url=(
        "oracle+oracledb://dwopt_test:1234@localhost:1521/"
        "?service_name=XEPDB1 &encoding=UTF-8&nencoding=UTF-8"
    ),
    method='keyring',
    thick_mode={"lib_dir":"C:/app/{username}/product/21c/dbhomeXE/bin"}
)
# restart python
from dwopt import oc
oc.run("select * from dual")

# check if thick mode is enabled
import oracledb
oracledb.is_thin_mode()# False

Environment variable storing additional parameters, example variable = value pair: dwopt_oc = {"url": "oracle+oracledb://dwopt_test:1234@localhost:1521/ ?service_name=XEPDB1 &encoding=UTF-8&nencoding=UTF-8", "thick_mode":{"lib_dir":"C:/app/{user_name}/product/21c/dbhomeXE/bin"}} . Then assuming oc url not saved to keyring or config:

from dwopt import oc
oc.run("select * from dual")

dwopt.db(eng, **kwargs)

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.

• kwargs (Additional engine creation arguments.) –

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+oracledb://scott2:tiger@tnsname"
>>> isinstance(db(url), Oc)
True

Use additional engine creation arguments:

from dwopt import db
url = (
    "oracle+oracledb://dwopt_test:1234@localhost:1521/?service_name=XEPDB1 "
    "&encoding=UTF-8&nencoding=UTF-8"
)
lib_dir = "C:/app/{user_name}/product/21c/dbhomeXE/bin"
o = db(url, thick_mode={"lib_dir": lib_dir})
o.run("select * from dual")

dwopt.Db(eng, **kwargs)

Alias for dwopt.db()

dwopt.make_eng(url, **kwargs)

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.

• kwargs (additional engine creation parameters.) –

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)

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;
  ALTER DATABASE dwopt_test OWNER TO dwopt_tester;
  

Postgre on linux

sudo su postgres
psql
CREATE DATABASE dwopt_test;
CREATE USER dwopt_tester WITH PASSWORD '1234';
GRANT ALL PRIVILEGES ON DATABASE dwopt_test to dwopt_tester;
ALTER DATABASE dwopt_test OWNER TO dwopt_tester;

Oracle

• Install from oracle xe.

• Set ORACLE_HOME, ORACLE_SID environment variable, and path based on the oracle configuration.

• Run commands:

  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