def _set_connections(self):
"""
Setup db connections
"""
ip_sql = (
'SELECT node_address FROM v_catalog.nodes '
'ORDER BY node_name LIMIT 1'
)
self._source_con = get_connection(
dsn=self._source_dsn,
user=self._kwargs.get('source_user'),
password=self._kwargs.get('source_pwd'),
reconnect=self._kwargs.get('source_reconnect', True),
)
self._source = self._source_con.cursor()
self._source_ip = self._source.execute(ip_sql).fetchone()[0]
self._target_con = get_connection(
dsn=self._target_dsn,
user=self._kwargs.get('target_user'),
password=self._kwargs.get('target_pwd'),
reconnect=self._kwargs.get('target_reconnect', True)
)
self._target = self._target_con.cursor()
self._target_ip = self._target.execute(ip_sql).fetchone()[0]
def _set_connections(self):
"""
Setup db connections
"""
ip_sql = (
'SELECT node_address FROM v_catalog.nodes '
'ORDER BY node_name LIMIT 1'
)
self._source_con = get_connection(
dsn=self._source_dsn,
user=self._kwargs.get('source_user'),
password=self._kwargs.get('source_pwd'),
reconnect=self._kwargs.get('source_reconnect', True),
)
self._source = self._source_con.cursor()
self._source_ip = self._source.execute(ip_sql).fetchone()[0]
self._target_con = get_connection(
dsn=self._target_dsn,
user=self._kwargs.get('target_user'),
password=self._kwargs.get('target_pwd'),
reconnect=self._kwargs.get('target_reconnect', True)
)
self._target = self._target_con.cursor()
self._target_ip = self._target.execute(ip_sql).fetchone()[0]
def get_last_imported_batch_source_path(cls, odbc_kwargs):
"""
Return the last imported batch source-path.
:param odbc_kwargs:
A ``dict`` containing the ODBC connection keyword arguments. E.g.::
{
'dsn': 'TestDSN',
}
.. seealso:: https://code.google.com/p/pyodbc/wiki/Module
:return:
A ``str`` representing the last imported batch source-path.
"""
connection = get_connection(**odbc_kwargs)
cursor = connection.cursor()
cursor.execute(
'SELECT batch_source_path FROM {batch_history_table} '
'WHERE batch_source_name = ? AND batch_source_type_name = ? '
'ORDER BY batch_import_timestamp DESC LIMIT 1'.format(
batch_history_table=cls.batch_history_table
),
cls.batch_source_name,
cls.batch_source_type_name
)
row = cursor.fetchone()
if row:
return row[0]
return None
def main():
settings = config.SQL_CONFIG
connection = get_connection(dsn = 'VerticaDSN', unicode_results='True')
cursor = connection.cursor()
try:
cursor.execute('select year(CURRENT_DATE - 2), month(CURRENT_DATE - 2), day(CURRENT_DATE - 2 )')
rows = cursor.fetchall()
month = '{0:4d}{1:02d}'.format(rows[0].year, rows[0].month);
day = '{0:4d}{1:02d}{2:02d}'.format(rows[0].year, rows[0].month, rows[0].day)
BASEDIR = os.path.join(os.path.realpath(sys.path[0]), 'report')
folder = os.path.join(BASEDIR, month)
filename = os.path.join(BASEDIR, month, day) + '.xls'
if not os.path.exists(folder):
os.mkdir(folder)
wb = xlwt.Workbook()
for setting in settings:
print str(datetime.datetime.now()) + ''' Pull data for "%s" ... ''' % setting['name']
cursor.execute(setting['sql'])
#print setting['sql']
AddSheet(wb, setting['name'], cursor, setting['row'], setting['col'])
wb.save(filename)
except Exception as ex:
print str(datetime.datetime.now()) + ''' Pull data for "%s" failed''' % setting['name']
print Exception, ":", ex
finally:
connection.close()
def generateReport(adID, startDate, endDate, macFormat, queryID = 'tmp'):
connection = get_connection(dsn = 'VerticaDSN', unicode_results='True')
cursor = connection.cursor()
try:
BASEDIR = os.path.join(os.path.dirname(os.path.realpath(__file__)), 'tmp_report')
folder = BASEDIR
filename = os.path.join(BASEDIR, queryID) + '.xls'
if not os.path.exists(folder):
os.mkdir(folder)
wb = xlwt.Workbook()
print str(datetime.datetime.now()) + ''' Pull data for offer "%s" ... ''' % adID
sql = ''' select distinct to_char(a.time + interval '8:00') as clicktime, b.mac_address
from (select udid, time from analytics.actions where offer_id = '%s'
and time between date('%s') - interval '8:00' and date('%s') + interval '16:00') a
join analytics.connects_bi b on a.udid = b.udid
where b.mac_address != 'NULL' and b.day between date('%s') - interval '15' and '%s'
order by 1 asc''' % (adID, startDate, endDate, startDate, endDate)
#print sql
cursor.execute(sql)
#cursor.execute('''select '12123' as clicktime, 'abc12' as mac_addr''')
AddSheet(wb, 'mac_addr', cursor, macFormat, 0, 0)
wb.save(filename)
except Exception as ex:
print str(datetime.datetime.now()) + ''' Failed to pull data for offer "%s" ... ''' % adID
print Exception, ":", ex
finally:
connection.close()
def get_batch_source_path_exists(cls, batch_source_path, odbc_kwargs={}):
"""
Check if the batch source-path exists in the database.
:param batch_source_path:
The batch source-path (``str``).
:param odbc_kwargs:
A ``dict`` containing the ODBC connection keyword arguments. E.g.::
{
'dsn': 'TestDSN',
}
.. seealso:: https://code.google.com/p/pyodbc/wiki/Module
:return:
``True`` if it already exists, else ``False``.
"""
connection = get_connection(**odbc_kwargs)
cursor = connection.cursor()
cursor.execute(
'SELECT batch_source_path FROM {batch_history_table} '
'WHERE batch_source_name = ? AND batch_source_type_name = ? AND '
'batch_source_path = ? LIMIT 1'.format(
batch_history_table=cls.batch_history_table),
cls.batch_source_name, cls.batch_source_type_name,
batch_source_path)
row = cursor.fetchone()
if row:
return True
return False
def get_batch_source_path_exists(cls, batch_source_path, odbc_kwargs={}):
"""
Check if the batch source-path exists in the database.
:param batch_source_path:
The batch source-path (``str``).
:param odbc_kwargs:
A ``dict`` containing the ODBC connection keyword arguments. E.g.::
{
'dsn': 'TestDSN',
}
.. seealso:: https://code.google.com/p/pyodbc/wiki/Module
:return:
``True`` if it already exists, else ``False``.
"""
connection = get_connection(**odbc_kwargs)
cursor = connection.cursor()
cursor.execute(
"SELECT batch_source_path FROM {batch_history_table} "
"WHERE batch_source_name = ? AND batch_source_type_name = ? AND "
"batch_source_path = ? LIMIT 1".format(batch_history_table=cls.batch_history_table),
cls.batch_source_name,
cls.batch_source_type_name,
batch_source_path,
)
row = cursor.fetchone()
if row:
return True
return False
def __init__(
self,
table_name,
odbc_kwargs={},
truncate_table=False,
reconnect=True,
analyze_constraints=True,
column_list=[],
copy_options={},
connection=None,
multi_batch=False):
if connection and odbc_kwargs:
raise ValueError("May only specify one of "
"[connection, odbc_kwargs]")
self._odbc_kwargs = odbc_kwargs
self._table_name = table_name
self._column_list = column_list
self._analyze_constraints = analyze_constraints
self.copy_options_dict.update(copy_options)
self._batch_initialized = False
self._multi_batch = multi_batch
self._total_count = 0
self._batch_count = 0
self._in_batch = False
if not connection:
# make sure we are not logging any passwords :)
odbc_kwargs_copy = copy.deepcopy(odbc_kwargs)
if 'password' in odbc_kwargs_copy:
odbc_kwargs_copy['password'] = '******'
logger.debug(
'Initializing VerticaBatch with odbc_kwargs={0}, '
'table_name={1}, '
'column_list={2}'.format(
odbc_kwargs_copy, table_name, column_list))
self._connection = get_connection(
reconnect=reconnect, **self._odbc_kwargs)
else:
self._connection = connection
self._cursor = self._connection.cursor()
# truncate table, if needed
if truncate_table:
self._truncate_table()
def __init__(
self,
table_name,
odbc_kwargs={},
truncate_table=False,
reconnect=True,
analyze_constraints=True,
column_list=[],
copy_options={},
connection=None,
multi_batch=False,
):
if connection and odbc_kwargs:
raise ValueError("May only specify one of " "[connection, odbc_kwargs]")
self._odbc_kwargs = odbc_kwargs
self._table_name = table_name
self._column_list = column_list
self._analyze_constraints = analyze_constraints
self.copy_options_dict.update(copy_options)
self._batch_initialized = False
self._multi_batch = multi_batch
self._total_count = 0
self._batch_count = 0
self._in_batch = False
if not connection:
# make sure we are not logging any passwords :)
odbc_kwargs_copy = copy.deepcopy(odbc_kwargs)
if "password" in odbc_kwargs_copy:
odbc_kwargs_copy["password"] = "******"
logger.debug(
"Initializing VerticaBatch with odbc_kwargs={0}, "
"table_name={1}, "
"column_list={2}".format(odbc_kwargs_copy, table_name, column_list)
)
self._connection = get_connection(reconnect=reconnect, **self._odbc_kwargs)
else:
self._connection = connection
self._cursor = self._connection.cursor()
# truncate table, if needed
if truncate_table:
self._truncate_table()
def test_get_connection(self, pyodbc, get_random_node_address):
"""
Test :py:func:`.get_connection`.
"""
pyodbc.connect.side_effect = ['connection1', 'connection2']
connection = get_connection(dsn='TestDSN', foo='bar', bar='foo')
self.assertEqual([
call(dsn='TestDSN', foo='bar', bar='foo'),
call(
dsn='TestDSN',
servername=get_random_node_address.return_value,
foo='bar',
bar='foo',
),
], pyodbc.connect.call_args_list)
get_random_node_address.assert_called_once_with('connection1')
self.assertEqual('connection2', connection)
def test_get_connection(self, pyodbc, get_random_node_address):
"""
Test :py:func:`.get_connection`.
"""
pyodbc.connect.side_effect = ['connection1', 'connection2']
connection = get_connection(dsn='TestDSN', foo='bar', bar='foo')
self.assertEqual([
call(dsn='TestDSN', foo='bar', bar='foo'),
call(
dsn='TestDSN',
servername=get_random_node_address.return_value,
foo='bar',
bar='foo',
),
], pyodbc.connect.call_args_list)
get_random_node_address.assert_called_once_with('connection1')
self.assertEqual('connection2', connection)
def __init__(
self,
table_name,
odbc_kwargs={},
truncate_table=False,
reconnect=True,
analyze_constraints=True,
column_list=[],
copy_options={}):
# make sure we are not logging any passwords :)
odbc_kwargs_copy = copy.deepcopy(odbc_kwargs)
if 'password' in odbc_kwargs_copy:
odbc_kwargs_copy['password'] = '******'
logger.debug(
'Initializing VerticaBatch with odbc_kwargs={0}, table_name={1}, '
'column_list={2}'.format(
odbc_kwargs_copy, table_name, column_list))
self._odbc_kwargs = odbc_kwargs
self._table_name = table_name
self._column_list = column_list
self._analyze_constraints = analyze_constraints
self.copy_options_dict.update(copy_options)
self._total_count = 0
self._batch_count = 0
self._in_batch = False
# setup db connection
self._connection = get_connection(
reconnect=reconnect, **self._odbc_kwargs)
self._cursor = self._connection.cursor()
# truncate table, if needed
if truncate_table:
self._truncate_table()
def __init__(self,
table_name,
odbc_kwargs={},
truncate_table=False,
reconnect=True,
analyze_constraints=True,
column_list=[],
copy_options={}):
# make sure we are not logging any passwords :)
odbc_kwargs_copy = copy.deepcopy(odbc_kwargs)
if 'password' in odbc_kwargs_copy:
odbc_kwargs_copy['password'] = '******'
logger.debug(
'Initializing VerticaBatch with odbc_kwargs={0}, table_name={1}, '
'column_list={2}'.format(odbc_kwargs_copy, table_name,
column_list))
self._odbc_kwargs = odbc_kwargs
self._table_name = table_name
self._column_list = column_list
self._analyze_constraints = analyze_constraints
self.copy_options_dict.update(copy_options)
self._total_count = 0
self._batch_count = 0
self._in_batch = False
# setup db connection
self._connection = get_connection(reconnect=reconnect,
**self._odbc_kwargs)
self._cursor = self._connection.cursor()
# truncate table, if needed
if truncate_table:
self._truncate_table()