def test_sqlite_dbparam_not_supported():
sqlitedb = DbParams(dbtype='SQLITE',
filename='sqlite.db',
dbname='etlhelper',
user='******')
with pytest.raises(ValueError):
sqlitedb.is_reachable()
def test_bad_connect(tmpdir):
# Attemping to create file in non-existent directory should fail
try:
db_params = DbParams(dbtype='SQLITE', filename='/does/not/exist')
with pytest.raises(ETLHelperConnectionError):
connect(db_params)
finally:
# Restore permissions prior to cleanup
os.chmod(tmpdir, 0o666)
def sqlitedb(tmp_path):
"""Get DbParams for temporary SQLite database."""
filename = f'{tmp_path.absolute()}.db'
yield DbParams(dbtype='SQLITE', filename=filename)
These currently run against internal BGS instance.
"""
# pylint: disable=unused-argument, missing-docstring
from datetime import datetime, date
import os
from textwrap import dedent
import cx_Oracle
import pytest
from etlhelper import connect, get_rows, copy_rows, DbParams
from etlhelper.exceptions import ETLHelperConnectionError
from test.conftest import db_is_unreachable
# Skip these tests if database is unreachable
ORADB = DbParams.from_environment(prefix='TEST_ORACLE_')
if db_is_unreachable(ORADB.host, ORADB.port):
pytest.skip('Oracle test database is unreachable', allow_module_level=True)
# -- Tests here --
def test_connect():
conn = connect(ORADB, 'TEST_ORACLE_PASSWORD')
assert isinstance(conn, cx_Oracle.Connection)
def test_connect_wrong_password(monkeypatch):
monkeypatch.setitem(os.environ, 'TEST_ORACLE_PASSWORD', 'bad_password')
with pytest.raises(ETLHelperConnectionError):
connect(ORADB, 'TEST_ORACLE_PASSWORD')
import pytest
from etlhelper import (
DbParams,
connect,
copy_rows,
copy_table_rows,
execute,
get_rows,
load,
)
from etlhelper.exceptions import (ETLHelperConnectionError,
ETLHelperInsertError, ETLHelperQueryError)
# Skip these tests if database is unreachable
MSSQLDB = DbParams.from_environment(prefix='TEST_MSSQL_')
if not MSSQLDB.is_reachable():
pytest.skip('MSSQL test database is unreachable', allow_module_level=True)
# -- Tests here --
def test_connect():
conn = connect(MSSQLDB, 'TEST_MSSQL_PASSWORD')
assert isinstance(conn, pyodbc.Connection)
def test_connect_wrong_password(monkeypatch):
monkeypatch.setitem(os.environ, 'TEST_MSSQL_PASSWORD', 'bad_password')
with pytest.raises(ETLHelperConnectionError):
connect(MSSQLDB, 'TEST_MSSQL_PASSWORD')
"""
import datetime as dt
import os
from pathlib import Path
import socket
from textwrap import dedent
from zipfile import ZipFile
import pytest
from psycopg2.extras import execute_batch
from etlhelper import connect, DbParams
PGTESTDB = DbParams(
dbtype='PG',
host='localhost',
port=5432,
dbname='etlhelper',
user='******')
@pytest.fixture(scope='module')
def pgtestdb_insert_sql():
"""Return SQL command used to populate test database."""
insert_sql = dedent("""
INSERT INTO src (id, value, simple_text, utf8_text,
day, date_time)
VALUES
(%s, %s, %s, %s, %s, %s)
;""").strip()
return insert_sql
from etlhelper import executemany, DbParams, get_rows
sqlite_first = DbParams(dbtype='SQLITE',
filename='C:\\Users\\vipin.soni\\db1.db')
sqlite_second = DbParams(dbtype='SQLITE',
filename='C:\\Users\\vipin.soni\\db2.db')
rows = [(5, 'vinay'), (6, 'rohit')]
insert_sql = "INSERT INTO company (id, name) VALUES (?, ?)"
select_sql = "SELECT * from company"
with sqlite_first.connect() as conn:
executemany(insert_sql, conn, rows)
xx = get_rows(select_sql, conn)
print(xx)
DCLEAR = D_CLEAR
os.environ['S_PG_PASSWORD'] = SDBPASS
os.environ['D_PG_PASSWORD'] = DDBPASS
ENGINES = {
'postgres': 'PG',
'mysql': 'MSSQL',
'oracle': 'ORACLE',
'sqlite': 'SQLITE'
}
engine = ENGINES.get(SDBENG, 'PG')
if engine == ENGINES['mysql']:
SOURCE_DB_PARAM = DbParams(dbtype=engine, host=SHOST, port=SPORT, dbname=SDBNAME, user=SDBUSER,
odbc_driver="ODBC Driver 17 for SQL Server")
elif engine == ENGINES['sqlite']:
SOURCE_DB_PARAM = DbParams(dbtype=engine, filename='/path/to/file.db')
else:
SOURCE_DB_PARAM = DbParams(dbtype=engine, host=SHOST, port=SPORT, dbname=SDBNAME, user=SDBUSER)
DESTINATION_DB_PARAMS = DbParams(dbtype='PG', host=DHOST, port=DPORT, dbname=DDBNAME, user=DDBUSER)
def copy_src_to_dest():
delete_sql = table_delete_query(D_TABLE) # USE THIS TO CLEAR DESTINATION FOR IDEMPOTENCE
src_conn = get_source_connection()
print('Connected to source')
dest_conn = get_destination_connection()
print('Connected to destination')
import sqlite3
import cx_Oracle
import pyodbc
import psycopg2
from etlhelper import DbParams
from etlhelper.db_helper_factory import DB_HELPER_FACTORY
from etlhelper.db_helpers import (
OracleDbHelper, MSSQLDbHelper, PostgresDbHelper, SQLiteDbHelper
)
from etlhelper.exceptions import ETLHelperConnectionError
# pylint: disable=missing-docstring
ORACLEDB = DbParams(dbtype='ORACLE', host='server', port='1521',
dbname='testdb', user='******')
MSSQLDB = DbParams(dbtype='MSSQL', host='server', port='1521', dbname='testdb',
user='******', odbc_driver='test driver')
POSTGRESDB = DbParams(dbtype='PG', host='server', port='1521', dbname='testdb',
user='******')
SQLITEDB = DbParams(dbtype='SQLITE', filename='/myfile.db')
@pytest.mark.parametrize('helper, expected', [
(OracleDbHelper, (cx_Oracle.DatabaseError)),
(MSSQLDbHelper, (pyodbc.DatabaseError)),
(PostgresDbHelper, (psycopg2.ProgrammingError, psycopg2.InterfaceError,
psycopg2.InternalError, psycopg2.errors.UniqueViolation)),
from etlhelper import DbParams
from sqlalchemy import create_engine
import pathlib
path = pathlib.Path(__file__).parent.absolute()
SQLITEDB_SOURCE_PATH = str(path) + '\source.db'
SQLITEDB_DESTINATION_PATH = str(path) + '\destination.db'
SQLITEDB_SOURCE = DbParams(dbtype='SQLITE', filename=SQLITEDB_SOURCE_PATH)
SQLITEDB_DESTINATION = DbParams(dbtype='SQLITE',
filename=SQLITEDB_DESTINATION_PATH)
#-----------------------------------------Dummy methods for DB Connection----------------------------------------------------#
# sqlalchemy#
REMOTE_MYSQL_MACHINE = 'ccpocmysql.mysql.database.azure.com:3306'
REMOTE_USERNAME = '******'
REMOTE_PASSWORD = '******'
REMOTE_MYSQL_SOURCE_DB = 'stateinsurance'
REMOTE_MYSQL_DESTINATION_DB = 'reports'
MYSQL_SOURCE = create_engine("mysql+pymysql://" + REMOTE_USERNAME + ":" +
REMOTE_PASSWORD + "@" + REMOTE_MYSQL_MACHINE +
"/" + REMOTE_MYSQL_SOURCE_DB,
echo=False)
MYSQL_DESTINATION = create_engine(
"mysql+pymysql://" + REMOTE_USERNAME + ":" + REMOTE_PASSWORD + "@" +
REMOTE_MYSQL_MACHINE + "/" + REMOTE_MYSQL_DESTINATION_DB,
echo=False)