def test_run_query_empty(self):
script = os.path.join(self.SQL_DIR, 'test_sqlplus_commando.sql')
sqlplus = SqlplusCommando(configuration=self.CONFIG)
sqlplus.run_script(script)
result = sqlplus.run_query("INSERT INTO test (id, name, age) VALUES "
"(2, 'Mignonne', 12);")
self.assertEqual((), result)
def test_run_script_error(self):
sqlplus = SqlplusCommando(configuration=self.CONFIG)
try:
sqlplus.run_script("unknown.sql")
self.fail('Should have failed')
except Exception, e:
self.assertTrue("Script 'unknown.sql' was not found" in str(e))
def test_run_query_error(self):
sqlplus = SqlplusCommando(configuration=self.CONFIG)
try:
sqlplus.run_query("SELECT 42 FROM DUO;")
self.fail('Should have failed')
except Exception, e:
self.assertTrue("ERROR at line 1" in e.message)
def test_run_query_empty(self):
script = os.path.join(self.SQL_DIR, 'test_sqlplus_commando.sql')
sqlplus = SqlplusCommando(configuration=self.CONFIG)
sqlplus.run_script(script)
result = sqlplus.run_query("INSERT INTO test (id, name, age) VALUES "
"(2, 'Mignonne', 12);")
self.assertEqual((), result)
def test_warning(self):
sqlplus = SqlplusCommando(configuration=self.CONFIG)
try:
sqlplus.run_query(self.WARNING)
self.fail('Should have failed')
except Exception, e:
self.assertTrue("Warning: " in e.message)
def test_run_unknown_command(self):
sqlplus = SqlplusCommando(configuration=self.CONFIG)
try:
sqlplus.run_query("BAD SQL QUERY;")
self.fail('Should have failed')
except Exception, e:
self.assertTrue("unknown command" in e.message)
def test_run_query_error(self):
sqlplus = SqlplusCommando(configuration=self.CONFIG)
try:
sqlplus.run_query("SELECT 42 FROM DUO;")
self.fail('Should have failed')
except Exception, e:
self.assertTrue("ERROR at line 1" in e.message)
def test_warning(self):
sqlplus = SqlplusCommando(configuration=self.CONFIG)
try:
sqlplus.run_query(self.WARNING)
self.fail('Should have failed')
except Exception, e:
self.assertTrue("Warning: " in e.message)
def test_run_unknown_command(self):
sqlplus = SqlplusCommando(configuration=self.CONFIG)
try:
sqlplus.run_query("BAD SQL QUERY;")
self.fail('Should have failed')
except Exception, e:
self.assertTrue("unknown command" in e.message)
def test_run_script_error(self):
sqlplus = SqlplusCommando(configuration=self.CONFIG)
try:
sqlplus.run_script("unknown.sql")
self.fail('Should have failed')
except Exception, e:
self.assertTrue("Script 'unknown.sql' was not found" in str(e))
def test_run_script_syntax_error(self):
script = os.path.join(self.SQL_DIR, 'test_sqlplus_commando_error.sql')
sqlplus = SqlplusCommando(configuration=self.CONFIG)
try:
sqlplus.run_script(script)
self.fail('Should have failed')
except Exception, e:
self.assertTrue("ERROR" in e.message)
def test_run_query_nominal(self):
sqlplus = SqlplusCommando(configuration=self.CONFIG)
result = sqlplus.run_query("SELECT 42 AS response, 'This is a test' "
"AS question FROM DUAL;")
self.assertEqual(({
'RESPONSE': 42,
'QUESTION': 'This is a test'
}, ), result)
def test_run_script_syntax_error(self):
script = os.path.join(self.SQL_DIR, 'test_sqlplus_commando_error.sql')
sqlplus = SqlplusCommando(configuration=self.CONFIG)
try:
sqlplus.run_script(script)
self.fail('Should have failed')
except Exception, e:
self.assertTrue("ERROR" in e.message)
def test_run_unknown_command_disable(self):
message = 'This is an Error!'
sqlplus = SqlplusCommando(configuration=self.CONFIG)
result = sqlplus.run_query("SELECT '%s' AS message FROM DUAL;" % message,
check_errors=False)
self.assertTrue(message in result[0]['MESSAGE'])
message = 'This is a warning!'
result = sqlplus.run_query("SELECT '%s' AS message FROM DUAL;" % message,
check_errors=False)
self.assertTrue(message in result[0]['MESSAGE'])
def test_run_unknown_command_disable(self):
message = 'This is an Error!'
sqlplus = SqlplusCommando(configuration=self.CONFIG)
result = sqlplus.run_query("SELECT '%s' AS message FROM DUAL;" %
message,
check_errors=False)
self.assertTrue(message in result[0]['MESSAGE'])
message = 'This is a warning!'
result = sqlplus.run_query("SELECT '%s' AS message FROM DUAL;" %
message,
check_errors=False)
self.assertTrue(message in result[0]['MESSAGE'])
def test_cast_query(self):
driver = SqlplusCommando(configuration=self.CONFIG)
driver.run_script(
os.path.join(self.SQL_DIR, 'test_sqlplus_commando_cast_query.sql'))
expected = (
{
'I': 123,
'F': 1.23,
'D': datetime.datetime(2014, 3, 29, 11, 18, 0),
'S': 'test'
},
{
'I': -456,
'F': -1.2e-34,
'D': datetime.datetime(2014, 3, 29),
'S': 123
},
)
actual = driver.run_query("SELECT i, f, d, s FROM test;", cast=True)
self.assertEqual(expected, actual)
driver.run_script(
os.path.join(self.SQL_DIR, 'test_sqlplus_commando_cast_query.sql'))
expected = (
{
'I': '123',
'F': '1,23',
'D': '29/03/14 11:18:00,000000',
'S': 'test'
},
{
'I': '-456',
'F': '-1,200E-34',
'D': '29/03/14 00:00:00,000000',
'S': '123'
},
)
actual = driver.run_query("SELECT i, f, d, s FROM test;", cast=False)
self.assertEqual(expected, actual)
def test_cast_query(self):
driver = SqlplusCommando(configuration=self.CONFIG)
driver.run_script(os.path.join(self.SQL_DIR,
'test_sqlplus_commando_cast_query.sql'))
expected = (
{'I': 123, 'F': 1.23,
'D': datetime.datetime(2014, 3, 29, 11, 18, 0), 'S': 'test'},
{'I': -456, 'F': -1.2e-34,
'D': datetime.datetime(2014, 3, 29), 'S': 123},
)
actual = driver.run_query("SELECT i, f, d, s FROM test;", cast=True)
self.assertEqual(expected, actual)
driver.run_script(os.path.join(self.SQL_DIR,
'test_sqlplus_commando_cast_query.sql'))
expected = (
{'I': '123', 'F': '1,23',
'D': '29/03/14 11:18:00,000000', 'S': 'test'},
{'I': '-456', 'F': '-1,200E-34',
'D': '29/03/14 00:00:00,000000', 'S': '123'},
)
actual = driver.run_query("SELECT i, f, d, s FROM test;", cast=False)
self.assertEqual(expected, actual)
def test_run_script_nominal(self):
script = os.path.join(self.SQL_DIR, 'test_sqlplus_commando.sql')
sqlplus = SqlplusCommando(configuration=self.CONFIG)
expected = ({'ID': 1, 'NAME': 'Réglisse', 'AGE': 14},)
actual = sqlplus.run_script(script)
self.assertEqual(expected, actual)
def test_run_query_nominal(self):
sqlplus = SqlplusCommando(configuration=self.CONFIG)
result = sqlplus.run_query("SELECT 42 AS response, 'This is a test' "
"AS question FROM DUAL;")
self.assertEqual(({'RESPONSE': 42, 'QUESTION': 'This is a test'},),
result)
def test_run_query_parameters(self):
sqlplus = SqlplusCommando(configuration=self.CONFIG)
params = {'response': 42}
result = sqlplus.run_query(
"SELECT %(response)s AS response FROM DUAL;", parameters=params)
self.assertEqual(({'RESPONSE': 42}, ), result)
def test_run_query_parameters(self):
sqlplus = SqlplusCommando(configuration=self.CONFIG)
params = {'response': 42}
result = sqlplus.run_query("SELECT %(response)s AS response FROM DUAL;",
parameters=params)
self.assertEqual(({'RESPONSE': 42},), result)
def test_process_parameters(self):
query = "%s %s %s"
parameters = [1, 'deux', datetime.datetime(2014, 01, 22, 13, 10, 33)]
expected = "1 'deux' '2014-01-22 13:10:33'"
actual = SqlplusCommando._process_parameters(query, parameters)
self.assertEqual(expected, actual)
def test_warning_disable(self):
sqlplus = SqlplusCommando(configuration=self.CONFIG)
sqlplus.run_query("SELECT 'Warning: test' FROM DUAL;", check_errors=False)
def test_warning_disable(self):
sqlplus = SqlplusCommando(configuration=self.CONFIG)
sqlplus.run_query("SELECT 'Warning: test' FROM DUAL;",
check_errors=False)
def test_run_script_nominal(self):
script = os.path.join(self.SQL_DIR, 'test_sqlplus_commando.sql')
sqlplus = SqlplusCommando(configuration=self.CONFIG)
expected = ({'ID': 1, 'NAME': 'Réglisse', 'AGE': 14}, )
actual = sqlplus.run_script(script)
self.assertEqual(expected, actual)
#!/usr/bin/python
import subprocess
from sqlplus_commando import SqlplusCommando
TARGET_FILE="/home/karthikeyankrishnasw/USERSFILE.sql"
ADMIN_USER="******"
ADMIN_PASS="******"
SID="ORCL"
PROTO="TCP"
HOSTNAME="******"
PORT="1521"
USERS="/home/karthikeyankrishnasw/properties.txt"
open(TARGET_FILE, "w").close()
with open(USERS, r) as f:
var = f.readlines()
for i in var:
j=i.split(":")
USER=j[0]
PASS=j[1]
str1 = "create user %s identified by %s profile ORA_STIG_PROFILE default tablespace users quota unlimited on users temporary tablespace temp password expire;" % (USER,PASS)
str2 = "grant DBA to %s with admin option;" % (USER)
sqlplus = SqlplusCommando(hostname=HOSTNAME, database=SID, username=ADMIN_USER, password=ADMIN_PASS)
result1 = sqlplus.run_query("%s" % str1)
result2 = sqlplus.run_query("%s" % str2)
print result1
print result2
def test_process_parameters(self):
query = "%s %s %s"
parameters = [1, 'deux', datetime.datetime(2014, 01, 22, 13, 10, 33)]
expected = "1 'deux' '2014-01-22 13:10:33'"
actual = SqlplusCommando._process_parameters(query, parameters)
self.assertEqual(expected, actual)