def __init__(self):
self.mysqlOFCon = jaydebeapi.connect('com.mysql.jdbc.Driver',
['jdbc:mysql://' + destDBHost + ':' + destDBPort + '/' + destDBname,
destUser, destPw], ['hsqldb.jar', 'mysql.jar'], )
self.mysqlOFCur = self.mysqlOFCon.cursor()
def setUp(self):
self.conn = jaydebeapi.connect('org.jaydebeapi.mockdriver.MockDriver',
'jdbc:jaydebeapi://dummyurl')
def setup_conn():
try:
import jaydebeapi
import jpype
if jpype.isJVMStarted() and not jpype.isThreadAttachedToJVM():
jpype.attachThreadToJVM()
jpype.java.lang.Thread.currentThread().setContextClassLoader(
jpype.java.lang.ClassLoader.getSystemClassLoader())
con = jaydebeapi.connect(
"oracle.jdbc.driver.OracleDriver",
"jdbc:oracle:thin:@wmsdbtst01.sager.com:1521:MV10TST",
["TSTMOVE", "TSTMOVE"])
return con.cursor()
else:
jvm_path = jpype.getDefaultJVMPath()
jpype.startJVM(jvm_path, '-Djava.class.path=C:\ojdbc10.jar')
con = jaydebeapi.connect(
"oracle.jdbc.driver.OracleDriver",
"jdbc:oracle:thin:@wmsdbtst01.sager.com:1521:MV10TST",
["TSTMOVE", "TSTMOVE"])
return con.cursor()
except Exception as e:
print(e)
print('no connection')
return None
def connect_database(self):
"""
连接数据库
"""
lib_path = root_path + os.sep + 'libs' + os.sep
if operator.eq(self._dbtype, 'oralce'):
conn = jdbc.connect(jclassname=self._jdbcclass,
jars=lib_path + 'ojdbc8.jar',
url=self._jdbcurl,
driver_args={
'user': self._username,
'password': self._password
})
cursor = conn.cursor()
elif operator.eq(self._dbtype, 'mysql'):
conn = jdbc.connect(jclassname=self._jdbcclass,
url=self._jdbcurl,
driver_args={
'user': self._username,
'password': self._password
},
jars=lib_path +
'mysql-connector-java-8.0.16.jar')
cursor = conn.cursor()
elif operator.eq(self._dbtype, 'sqlite3'):
conn = jdbc.connect(jclassname=self._jdbcclass,
url=self._jdbcurl,
jars=lib_path + 'sqlite-jdbc-3.28.0.jar')
cursor = conn.cursor()
else:
self._logger.error('暂不支持%s数据库类型' % self._dbtype)
raise
return {'conn': conn, 'cursor': cursor}
def test_connect_with_properties(self):
driver, url, driver_args = ('org.hsqldb.jdbcDriver',
'jdbc:hsqldb:mem:.', {
'user': '******',
'password': ''
})
jaydebeapi.connect(driver, url, driver_args)
def connect(self):
""" 数据库连接
Args:
Returns:
Raise:
使用jaydebeapi的异常
"""
LOG.debug("连接数据库")
try:
if self.__jdbc_driver:
self.__conn = jaydebeapi.connect(self.__jdbc_class,
self.__jdbc_url,
{"user":"******".format(
self.__db_user),
"password":"******".format(
self.__db_pwd)},
self.__jdbc_driver)
else:
self.__conn = jaydebeapi.connect(self.__jdbc_class,
self.__jdbc_url,
{"user":"******".format(
self.__db_user),
"password":"******".format(
self.__db_pwd)})
except:
LOG.error("连接数据库失败")
traceback.print_exc()
raise
else:
LOG.debug("数据库连接成功")
def run_sql_file(filename, db_name):
"""Format and run database table creation script files in mysql"""
print('Trying to create database tables in %s database server...' % DB_TYPE)
# Removing tabs, new lines since cursor.execute doesn't allow to use them
with open(filename, 'r') as my_file:
data = my_file.read()
para = ''
for line in data.splitlines(True):
if not line.startswith('--'):
para += line
para = para.replace('\n', ' ')
para = para.replace('\t', ' ')
if DB_TYPE == 'oracle':
para = para.split('/')
else:
para = para.split(';')
for line in para:
if format(line) != '':
print("Executing Query-> {}".format(line))
# Running formatted dbscript
if DB_TYPE == 'mysql':
conn = jaydebeapi.connect("com.mysql.cj.jdbc.Driver",
'jdbc:mysql://%s:%d/%s' % (HOST, PORT, db_name),
[USER_NAME, PWD],
"../data/dbconnectors/mysql/mysql-connector-java-8.0.13.jar")
elif DB_TYPE == 'oracle':
conn = jaydebeapi.connect("oracle.jdbc.driver.OracleDriver",
'jdbc:oracle:thin:%s@%s:%d/%s' % (USER_NAME, HOST, PORT, SID),
[USER_NAME, PWD],
"../data/dbconnectors/oracle/ojdbc7.jar")
elif DB_TYPE == 'mssql':
conn = jaydebeapi.connect("com.microsoft.sqlserver.jdbc.SQLServerDriver",
'jdbc:sqlserver://%s:%d;databaseName=%s;SendStringParametersAsUnicode=false' % (
HOST, PORT, db_name),
[USER_NAME, PWD],
"../data/dbconnectors/mssql/mssql-jdbc-7.2.1.jre8.jar")
elif DB_TYPE == 'postgresql':
conn = jaydebeapi.connect("org.postgresql.Driver",
'jdbc:postgresql://%s:%d/%s' % (HOST, PORT, db_name),
[USER_NAME, PWD],
"../data/dbconnectors/postgresql/postgresql-42.2.5.jar")
else:
print("Database provided is not valid when creating connection!!!")
cursor = conn.cursor()
# Used very bad exception handling strategy here because it is throwing empty query executing error while reading line by line it gives empty line couldn't handle
try:
cursor.execute(line)
except:
pass
conn.close()
def init_steps():
global dataSet
global dataSource
global conn
dataSet = dsx_core_utils.get_remote_data_set_info('submissions')
dataSource = dsx_core_utils.get_data_source_info(dataSet['datasource'])
if (sys.version_info >= (3, 0)):
conn = jaydebeapi.connect(dataSource['driver_class'], dataSource['URL'], [dataSource['user'], dataSource['password']])
else:
conn = jaydebeapi.connect(dataSource['driver_class'], [dataSource['URL'], dataSource['user'], dataSource['password']])
load_dataset()
def load_dataset(dataSet):
global conn
try:
conn
except NameError:
dataSource = dsx_core_utils.get_data_source_info(dataSet['datasource'])
if (sys.version_info >= (3, 0)):
conn = jaydebeapi.connect(dataSource['driver_class'], dataSource['URL'], [dataSource['user'], dataSource['password']])
else:
conn = jaydebeapi.connect(dataSource['driver_class'], [dataSource['URL'], dataSource['user'], dataSource['password']])
query = 'SELECT * FROM ' + (dataSet['schema'] + '.' if (len(dataSet['schema'].strip()) != 0) else '') + dataSet['table']
dataframe = pd.read_sql(query, con=conn)
return dataframe
def connect(self) -> Connection:
"""connect through a jdbc string
:param connection_url: a valid jdbc connection string
:type connection_url: str
:param username: username to connect to the jdbc source
:type username: str
:param password: password to connect to the jdbc source
:type password: str
:return: a jaydebeapi connection object which can be read through pandas
:rtype: jaydebeapi.Connection
"""
if self.trust_store_required:
_startJVM(self.trust_store_location, self.trust_store_password,
self.jdbc_location)
# Create connection
connection = jaydebeapi.connect(
jclassname=self.java_classname,
url=self.connection_url,
driver_args=[self.username, self.password],
jars=self.jdbc_location,
)
self.connection = connection
return connection
def init(thread_count):
for _ in range(thread_count + 1):
DBManager.connections.append(jaydebeapi.connect(
'com.sap.db.jdbc.Driver',
[url, HANA.USER, HANA.PASSWORD],
'jdbc_driver.jar'))
DBManager.current = 0
def open(cls, connection):
if connection.state == 'open':
logger.debug('Connection is already open, skipping open.')
return connection
credentials = cls.get_credentials(connection.credentials)
try:
if jpype.isJVMStarted() and not jpype.isThreadAttachedToJVM():
jpype.attachThreadToJVM()
jpype.java.lang.Thread.currentThread().setContextClassLoader(jpype.java.lang.ClassLoader.getSystemClassLoader())
C = jaydebeapi.connect('com.ibm.db2.jcc.DB2Driver',
'jdbc:db2://' + credentials.host + ':' + str(credentials.port) + '/' + credentials.database,
[credentials.username, credentials.password],
'C:/Users/ilija/Downloads/db2jcc-db2jcc4.jar')
connection.handle = C
connection.state = 'open'
except Exception as e:
logger.debug("Got an error when attempting to open a postgres "
"connection: '{}'"
.format(e))
connection.handle = None
connection.state = 'fail'
raise dbt.exceptions.FailedToConnectException(str(e))
return connection
def connect(self):
# http://hsqldb.org/
# hsqldb.jar
driver = 'org.hsqldb.jdbcDriver'
url = 'jdbc:hsqldb:mem:.'
info = {'user': '******', 'password': ''}
return jaydebeapi, jaydebeapi.connect(driver, (url, info))
def db_connection(configuration):
conn = jaydebeapi.connect(
configuration['libName'], configuration['connectionUri'],
[configuration['user'], configuration['password']],
configuration['libUri'])
return conn
def read_jdbc(sql_statement, database="TELCOANAPRD"):
'''
read jdbc into panda dataframe
'''
#init
dsn_database = database
dsn_hostname = "10.50.78.21"
dsn_port = "5480"
dsn_uid = "Kasidi3"
dsn_pwd = "12345678"
jdbc_driver_name = "org.netezza.Driver"
jdbc_driver_loc = os.path.join('C:\\JDBC\\nzjdbc.jar')
connection_string = 'jdbc:netezza://' + dsn_hostname + ':' + dsn_port + '/' + dsn_database
try:
conn = jdbc.connect(jdbc_driver_name,
connection_string, {
'user': dsn_uid,
'password': dsn_pwd
},
jars=jdbc_driver_loc)
except jdbc.DatabaseError as de:
raise
try:
curs = conn.cursor()
curs.execute(sql_statement)
columns = [desc[0]
for desc in curs.description] #getting column headers
#convert the list of tuples from fetchall() to a df
return pd.DataFrame(curs.fetchall(), columns=columns)
except jdbc.DatabaseError as de:
raise
finally:
curs.close()
conn.close()
def import_sqlserver(window, schema, jar, auth, port=1433):
import jaydebeapi
global connection
global cursor
from PyQt5.QtWidgets import QMessageBox
msgBox = QMessageBox()
msgBox.setWindowTitle("Information")
msgBox.setIcon(QMessageBox.NoIcon)
msgBox.setText(
'''A SQL Server instance must be running with TCP/IP connectivity enabled'''
)
msgBox.exec_()
driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
dbURL = "jdbc:sqlserver://localhost;database=TEST2;integratedSecurity=true"
jar = "data//dbfiles//sqljdbc41.jar"
connection = jaydebeapi.connect(driver, dbURL, jars=jar)
cursor = connection.cursor()
cursor.execute(
"SELECT DISTINCT TABLE_NAME FROM TEST2.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA ='dbo'"
)
tables = [t[0] for t in cursor.fetchall()]
print("tables")
print(tables)
for table in tables:
cursor.execute(
"SELECT COLUMN_NAME FROM TEST2.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='{}' AND TABLE_SCHEMA ='dbo'"
.format(table))
attrs = [c[0] for c in cursor.fetchall()]
print(table)
print(attrs)
connection.close()
def connect(self):
"Connect / Re-Connect to Database"
import jaydebeapi
cred = self._cred
profile = self.profile
jar_path = get_jar_path(cred.type, profile)
os.environ['CLASSPATH'] = '{}:{}'.format(
jar_path,
os.environ['CLASSPATH'] if 'CLASSPATH' in os.environ else '')
self.connection = jaydebeapi.connect(
profile['drivers'][cred.type]['class'],
cred.jdbc_url,
[cred.user, cred.password],
jar_path,
)
self.cursor = None
self.meta = self.get_meta()
# self.connection.autocommit = True
self.name = cred.type
self.username = cred.user if cred else ''
cursor = self.get_cursor()
def _get_connection(self, server, user, password, service, jdbc_driver,
tags):
try:
# Check if the instantclient is available
cx_Oracle.clientversion()
except cx_Oracle.DatabaseError as e:
# Fallback to JDBC
use_oracle_client = False
self.log.debug(
'Oracle instant client unavailable, falling back to JDBC: {}'.
format(e))
connect_string = self.JDBC_CONNECT_STRING.format(server, service)
else:
use_oracle_client = True
self.log.debug('Running cx_Oracle version {0}'.format(
cx_Oracle.version))
connect_string = self.CX_CONNECT_STRING.format(
user, password, server, service)
try:
if use_oracle_client:
con = cx_Oracle.connect(connect_string)
else:
try:
if jpype.isJVMStarted(
) and not jpype.isThreadAttachedToJVM():
jpype.attachThreadToJVM()
jpype.java.lang.Thread.currentThread(
).setContextClassLoader(
jpype.java.lang.ClassLoader.getSystemClassLoader())
con = jdb.connect(self.ORACLE_DRIVER_CLASS, connect_string,
[user, password], jdbc_driver)
except jpype.JException(jpype.java.lang.RuntimeException) as e:
if "Class {} not found".format(
self.ORACLE_DRIVER_CLASS) in str(e):
msg = """Cannot run the Oracle check until either the Oracle instant client or the JDBC Driver
is available.
For the Oracle instant client, see:
http://www.oracle.com/technetwork/database/features/instant-client/index.html
You will also need to ensure the `LD_LIBRARY_PATH` is also updated so the libs are reachable.
For the JDBC Driver, see:
http://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html
You will also need to ensure the jar is either listed in your $CLASSPATH or in the yaml
configuration file of the check.
"""
self.log.error(msg)
raise
self.log.debug("Connected to Oracle DB")
self.service_check(self.SERVICE_CHECK_NAME,
AgentCheck.OK,
tags=tags)
except Exception as e:
self.service_check(self.SERVICE_CHECK_NAME,
AgentCheck.CRITICAL,
tags=tags)
self.log.error(e)
raise
return con
def connect(self):
jarFile = self.jar_package()
conn = jaydebeapi.connect(self.driver, self.url,
[self.user, self.password], jarFile)
curs = conn.cursor()
return curs
def get_visit_log_figure():
jdbc_server = "jdbc:IRIS://" + config["iris"]["host"] + ":" + str(
config["iris"]["port"]) + "/" + config["iris"]["namespace"]
jdbc_driver = 'com.intersystems.jdbc.IRISDriver'
iris_jdbc_jar = "./intersystems-jdbc-3.1.0.jar"
iris_user = config["iris"]["username"]
iris_password = config["iris"]["password"]
conn = jaydebeapi.connect(jdbc_driver, jdbc_server,
[iris_user, iris_password], iris_jdbc_jar)
curs = conn.cursor()
curs.execute(
"SELECT ID, VisitorIP, VisitorPage, VisitorTimeStamp FROM banzai.visit"
)
total_cache = curs.fetchall()
col_visitor_ip = []
col_visitor_page = []
col_visitor_timestamp = []
for row in total_cache:
col_visitor_ip.append(row[1])
col_visitor_page.append(row[2])
col_visitor_timestamp.append(row[3])
data = [col_visitor_timestamp, col_visitor_ip, col_visitor_page]
fig = go.Figure(data=[
go.Table(header=dict(values=['Timestamp', 'IP', 'Page']),
cells=dict(values=data))
])
return fig
def run_consumer():
remote_filepath = os.getenv("PSS_PATH")
local_server = os.getenv("SKORUZ_IP")
local_filepath = os.getenv("SKORUZ_PATH")
remote_server = os.getenv("SKORUZ_WEBSITE")
server_port = os.getenv("SKORUZ_PORT")
server_database = os.getenv("SKORUZ_DB")
local_kafka_server = os.getenv("SKORUZ_KAFKA_SERVER")
remote_kafka_server = 'localhost:9092'
topic_name = sys.argv[1]
conn = jaydebeapi.connect("com.simba.hive.jdbc41.HS2Driver",
"jdbc:hive2://" + local_server + ":" + server_port +
"/" + server_database, {'user': "******", 'password': ""},
local_filepath)
curs = conn.cursor()
print("Connection Established")
consumer = KafkaConsumer(
topic_name,
bootstrap_servers=[local_kafka_server],
auto_offset_reset='earliest',
enable_auto_commit=True,
group_id='my-group',
value_deserializer=lambda x: loads(x.decode('utf-8')))
for message in consumer:
message = message.value
values = []
for i in message:
values.append(message[i])
values = ', '.join(['"{}"'.format(value) for value in values])
try:
curs.execute('INSERT INTO ' + topic_name + ' VALUES (' + values + ')')
print("Intserted : ", message)
except Exception:
print("Insert Error")
conn.close()
def open_connection(self, address=None, username=None, password=None):
"""
Open a connection to the database, if the credentials a re not provided,
uses the ones already provided in set functions
:param address:
:type address:
:param username:
:type username:
:param password:
:type password:
:return:
:rtype:
"""
if not address:
address = self.address
if not username:
username = self.username
if not password:
password = self.password
self.conn = jaydebeapi.connect(
'com.teradata.jdbc.TeraDriver',
'jdbc:teradata:' + address + ',USER='******',PASSWORD='******'terajdbc4.jar', 'tdgssconfig.jar'])
self.cur = self.conn.cursor()
def connect(self):
# http://hsqldb.org/
# hsqldb.jar
driver, driver_args = 'org.hsqldb.jdbcDriver', [
'jdbc:hsqldb:mem:.', 'SA', ''
]
return jaydebeapi, jaydebeapi.connect(driver, driver_args)
def getRowCount(sql):
"""
Gets the row count using the provided SQL. Really this just handles the parsing of the results into a single
number, since the logic for the SQL has to be customized prior to the function's call
:param sql: SQL containing a "SELECT count(___) ..." query.
:return: row count if successful, -1 if there was a problem
"""
global jclass, connString, jar
# Set default to -1
rowCount = -1
try:
conn = jaydebeapi.connect(jclass, connString, jars=jar)
curs = conn.cursor()
curs.execute(sql)
data = curs.fetchall()
# Parse data tuple for the actual row count value
rowCount = data[0][0]
except jaydebeapi.DatabaseError as de:
logger.error('Database Connection Error. Returning -1 for count.')
logger.error(de)
finally:
curs.close()
conn.close()
return rowCount.intValue()
def tier_down():
"""This function takes care about tier downing main changes did to the system i.e.database remove[mysql],remove APIM instances"""
if DB_TYPE == 'mysql':
conn = jaydebeapi.connect(
"com.mysql.cj.jdbc.Driver",
'jdbc:mysql://%s:%d/%s' % (HOST, PORT, REG_DB), [USER_NAME, PWD],
"../data/dbconnectors/mysql/mysql-connector-java-8.0.13.jar")
else:
print(
"Manually remove all the database instances by login to the database!!!"
)
dbarr = [AM_DB, USER_DB, REG_DB]
for db in dbarr:
try:
curs = conn.cursor()
curs.execute('DROP DATABASE %s' % db)
print('Successfully removed the %s database.' % db)
except:
print(
"Error occurred while deleting %s. Please do manually remove by login to the database!!!"
% db)
try:
versionarr = [OLD_VERSION, NEW_VERSION]
for version in versionarr:
shutil.rmtree('%s/wso2am-%s' % (APIM_HOME_PATH, version))
print("Successfully removed all unzipped APIM versions.")
except:
print("Error occurred while removing unzipped APIM versions!!!")
def get_db_conn():
conn = jaydebeapi.connect(
"org.apache.calcite.avatica.remote.Driver",
"jdbc:avatica:remote:url=" + host + ";serialization=PROTOBUF", {},
'/scratch/mtopak/pelago2/opt/lib/avatica-1.13.0.jar')
return conn
def connect(self):
# http://hsqldb.org/
# hsqldb.jar
driver = 'org.hsqldb.jdbcDriver'
url = 'jdbc:hsqldb:mem:.'
info = {'user':'******', 'password':''}
return jaydebeapi, jaydebeapi.connect(driver, (url, info))
def main():
# get YAML config file
with open(CONFIG_FILE, "r") as fp:
conf = yaml.safe_load(fp)
# set JAVA_HOME
os.environ['JAVA_HOME'] = conf['java_home']
# connection
conn = jaydebeapi.connect(conf["driver_class"], conf["conn_str"],
conf["driver_args"], conf["driver_path"])
logger.info("Connection successfull")
# Execute queries
curs = conn.cursor()
logger.info("Executing queries...")
curs.execute(conf['query_string'])
# Fetch results and print
logger.info("Fetching results...")
res = curs.fetchall()
pprint(res)
# Close cursor and connection
logger.info("Closing connection...")
curs.close()
conn.close()
def getExternalConnection(self):
try:
# Fix
import jpype
if jpype.isJVMStarted() and not jpype.isThreadAttachedToJVM():
jpype.attachThreadToJVM()
jpype.java.lang.Thread.currentThread().setContextClassLoader(
jpype.java.lang.ClassLoader.getSystemClassLoader())
conn = jaydebeapi.connect("com.ibm.db2.jcc.DB2Driver",
"jdbc:db2://"
"{rechnername}.is.inf.uni-due.de:50{gruppennummer}/{database}".format(
rechnername=rechnername,
gruppennummer=re.match(r"([a-z]+)([0-9]+)", username, re.I).groups()[1],
database=database
#user=username.strip()
),
{
'user': username,
'password': password,
'securityMechanism': "3"
},
os.path.join(os.getcwd(), 'jdbc-1.0.jar')
)
#conn.autocommit = False
return conn
except Exception as e:
print(e)
def read_col_oracle(con_str, table_name):
jdbc_driver_name = 'oracle.jdbc.OracleDriver'
jdbc_driver_loc = './jdbc/ojdbc6.jar'
url = 'jdbc:oracle:thin:@10.54.9.105:4713/TRACEDEV'
username = ''
password = ''
args = '-Djava.class.path=%s' % jdbc_driver_loc
jvm = jpype.getDefaultJVMPath()
if not jpype.isJVMStarted():
jpype.startJVM(jvm)
conn_ora = jaydebeapi.connect(jdbc_driver_name, [url, username, password],
jdbc_driver_loc)
curs_ora = conn_ora.cursor()
print table_name
query_column_info = "select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE FROM USER_TAB_COLUMNS where table_name = '%s' ORDER BY COLUMN_ID" % (
table_name.rstrip())
curs_ora.execute(query_column_info)
column_info = []
val = curs_ora.fetchall()
column_info.append(table_name.rstrip())
for column_name_info in val:
column_info.append(column_name_info)
return column_info
def query(self, sql, db='tap_ods'):
url = 'jdbc:hive2://xxx.xxx.xxx.xxx:10000/' + db
dirver = 'org.apache.hive.jdbc.HiveDriver'
DIR = os.getcwd() + '/lib/'
jarFile = [
DIR + 'hive-jdbc-3.1.1.jar',
DIR + 'commons-logging-1.2.jar',
DIR + 'hive-service-3.1.1.jar',
DIR + 'hive-service-rpc-3.1.1.jar',
DIR + 'libthrift-0.12.0.jar',
DIR + 'httpclient-4.5.9.jar',
DIR + 'httpcore-4.4.11.jar',
DIR + 'slf4j-api-1.7.26.jar',
DIR + 'curator-framework-4.2.0.jar',
DIR + 'curator-recipes-4.2.0.jar',
DIR + 'curator-client-4.2.0.jar',
DIR + 'commons-lang-2.6.jar',
DIR + 'hadoop-common-3.2.0.jar',
DIR + 'httpcore-4.4.11.jar',
DIR + 'hive-common-3.1.1.jar',
DIR + 'hive-serde-3.1.1.jar',
DIR + 'guava-28.0-jre.jar',
]
conn = jaydebeapi.connect(dirver, url, ['hadoop', ''], jarFile)
curs = conn.cursor()
for _sql in self.base_sql:
curs.execute(_sql)
curs.execute(sql)
result = curs.fetchall()
curs.close()
conn.close()
return result
def create_table(table_name):
remote_filepath = os.getenv("PSS_PATH")
local_server = os.getenv("SKORUZ_IP")
local_filepath = os.getenv("SKORUZ_PATH")
remote_server = os.getenv("SKORUZ_WEBSITE")
server_port = os.getenv("SKORUZ_PORT")
server_database = os.getenv("SKORUZ_DB")
data_dir = os.path.join(os.getcwd(), "sampledata/" + sys.argv[1])
f = open(data_dir, "r")
data = json.load(f)
f.close()
header = []
length = len(data)
j = 0
for i in data:
header.append(i)
header.append('STRING')
if j < length - 1:
header.append(";")
j += 1
header = ",".join(header)
header = header.replace(',', ' ')
header = header.replace(';', ',')
try:
conn = jaydebeapi.connect("com.simba.hive.jdbc41.HS2Driver",
"jdbc:hive2://" + local_server + ":" + server_port +
"/" + server_database, {'user': "******", 'password': ""}, local_filepath)
curs = conn.cursor()
curs.execute('CREATE TABLE ' + table_name + '(' + header + ') STORED AS ORC')
conn.close()
except Exception:
print("Error")
conn.close()
def process(self, context):
element=sqlstring[0]
database_user=env_config[connectionprefix+'_database_user']
database_password=env_config[connectionprefix+'_database_password'].decode('base64')
database_host=env_config[connectionprefix+'_database_host']
database_port=env_config[connectionprefix+'_database_port']
database_db=env_config[connectionprefix+'_database']
jclassname = "oracle.jdbc.driver.OracleDriver"
url = ("jdbc:oracle:thin:"+database_user+"/"+database_password+"@"+database_host +":"+database_port+"/"+database_db)
jars = ["/tmp/ojdbc6.jar"]
libs = None
cnx = jaydebeapi.connect(jclassname, url, jars=jars,
libs=libs)
logging.info('Connection Successful..')
cursor = cnx.cursor()
logging.info('Reading Sql Query from the file...')
query = element.replace('v_incr_date',incrementaldate).replace('jobrunid',str(jobrunid)).replace('jobname', jobname)
logging.info('Query is %s',query)
logging.info('Query submitted to Oracle Database..')
for chunk in pd.read_sql(query, cnx, coerce_float=True, params=None, parse_dates=None, columns=None,chunksize=500000):
chunk.apply(lambda x: x.replace(u'\r', u' ').replace(u'\n', u' ') if isinstance(x, str) or isinstance(x, unicode) else x).to_gbq(product_config['datasetname']+"."+targettable, env_config['projectid'],if_exists='replace')
logging.info("Load completed...")
return list("1")
class Connector(GenericConnector):
"""
Homepage: https://pypi.python.org/pypi/JayDeBeApi/ & http://jpype.sourceforge.net/
User guide: https://pypi.python.org/pypi/JayDeBeApi/#usage & http://jpype.sourceforge.net/doc/user-guide/userguide.html
API: -
Debian package: -
License: LGPL & Apache License 2.0
"""
def __init__(self):
GenericConnector.__init__(self)
def connect(self):
self.initConnection()
try:
msg = "what's the location of 'hsqldb.jar'? "
jar = readInput(msg)
checkFile(jar)
args = "-Djava.class.path=%s" % jar
jvm_path = jpype.getDefaultJVMPath()
jpype.startJVM(jvm_path, args)
except Exception, msg:
raise SqlmapConnectionException(msg[0])
try:
driver = 'org.hsqldb.jdbc.JDBCDriver'
connection_string = 'jdbc:hsqldb:mem:.' #'jdbc:hsqldb:hsql://%s/%s' % (self.hostname, self.db)
self.connector = jaydebeapi.connect(driver, connection_string,
str(self.user),
str(self.password))
except Exception, msg:
raise SqlmapConnectionException(msg[0])
def connect(self, options = ""):
try:
self.conn = jaydebeapi.connect("org.mariadb.jdbc.Driver", ["jdbc:mariadb://" + self.host + ":" + self.port + "/test?" + options, self.user, self.password],"./maxscale/java/mariadb-java-client-1.3.3.jar")
except Exception as ex:
print("Failed to connect to " + self.host + ":" + self.port + " as " + self.user + ":" + self.password)
print(unicode(ex))
exit(1)
def connect(self):
# http://hsqldb.org/
# hsqldb.jar
driver, url, driver_args = ( 'org.hsqldb.jdbcDriver',
'jdbc:hsqldb:mem:.',
['SA', ''] )
return jaydebeapi, jaydebeapi.connect(driver, url, driver_args)
def _authenticate(user, pw):
global conn
try:
conn = jaydebeapi.connect('com.sas.net.sharenet.ShareNetDriver', ['jdbc:sharenet://wrds-cloud.wharton.upenn.edu:8551/', user, pw])
except Exception as e:
print(e)
conn = None
return conn
def connect(dbname, user, host, password):
jar = './mapdjdbc-1.0-SNAPSHOT-jar-with-dependencies.jar' # may want to parametrize
try:
return jaydebeapi.connect('com.mapd.jdbc.MapDDriver',
'jdbc:mapd:{}:{}:'.format(host, dbname),
{'user': user, 'password': password},
jar)
except Exception as e:
print('Error: {}'.format(str(e)))
raise e
def teraConnection(tduser='******',tdpwd='slw1234'):
driverClass='com.teradata.jdbc.TeraDriver'
path='jdbc:teradata://s00t0108.searshc.com'
classpath = """C:\\Users\\lshu0\\Documents\\Teradata driver\\tdgssconfig.jar;C:\\Users\\lshu0\\Documents\\Teradata driver\\terajdbc4.jar"""
jvm_path = u'C:\\Program Files\\Java\\jre7\\bin\\server\\jvm.dll'
jpype.startJVM(jvm_path, "-Djava.class.path=%s" % classpath)
conn = jaydebeapi.connect(driverClass,[path,tduser,tdpwd])
cursor = conn.cursor()
return cursor
def get_conn(self):
conn = self.get_connection(getattr(self, self.conn_name_attr))
host = conn.host
login = conn.login
psw = conn.password
jdbc_driver_loc = conn.extra_dejson.get('extra__jdbc__drv_path')
jdbc_driver_name = conn.extra_dejson.get('extra__jdbc__drv_clsname')
conn = jaydebeapi.connect(jdbc_driver_name,
[str(host), str(login), str(psw)],
jdbc_driver_loc,)
return conn
def run_sql(cmd, needResult):
conn = J.connect('org.h2.Driver', ['jdbc:h2:~/%s' %(db_name), db_user, db_pswd], '/path/to/hsqldb.jar',)
curs = conn.cursor()
#print 'function run_sql command:', cmd
curs.execute(cmd)
if needResult:
result = curs.fetchall()
conn.close()
return result
else:
conn.commit()
conn.close()
def getMySQLTableNames(self):
self.mysqlCon = jaydebeapi.connect('com.mysql.jdbc.Driver',
['jdbc:mysql://' + destDBHost + ':' + destDBPort,
destUser, destPw], ['hsqldb.jar', 'mysql.jar'],)
self.mysqlCur = self.mysqlCon.cursor()
self.mysqlCur.execute("SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '%s'" % destDBname)
self.tables = self.mysqlCur.fetchall()
self.mysqlCon.close()
self.tableList = []
for table in self.tables:
self.tableList.append(str(table[0]))
return self.tableList
def connect(self):
#http://bitbucket.org/xerial/sqlite-jdbc
# sqlite-jdbc-3.7.2.jar
driver, url = 'org.sqlite.JDBC', 'jdbc:sqlite::memory:'
# db2jcc
# driver, driver_args = 'com.ibm.db2.jcc.DB2Driver', \
# ['jdbc:db2://4.100.73.81:50000/db2t', 'user', 'passwd']
# driver from http://www.ch-werner.de/javasqlite/ seems to be
# crap as it returns decimal values as VARCHAR type
# sqlite.jar
# driver, driver_args = 'SQLite.JDBCDriver', 'jdbc:sqlite:/:memory:'
# Oracle Thin Driver
# driver, driver_args = 'oracle.jdbc.OracleDriver', \
# ['jdbc:oracle:thin:@//hh-cluster-scan:1521/HH_TPP',
# 'user', 'passwd']
return jaydebeapi, jaydebeapi.connect(driver, url)
def execute(self, notebook, snippet):
if 'jaydebeapi' not in sys.modules:
raise Exception('Required jaydebeapi module is not imported.')
user = '******'
password = '******'
host = 'localhost'
port = 3306
database = 'test'
autocommit = True
jclassname = "com.mysql.jdbc.Driver"
url = "jdbc:mysql://{host}:{port}/{database}".format(host=host, port=port, database=database)
driver_args = [url, user, password]
jars = None
libs = None
db = jaydebeapi.connect(jclassname, driver_args, jars=jars, libs=libs)
db.jconn.setAutoCommit(autocommit)
curs = db.cursor()
curs.execute(snippet['statement'])
data = curs.fetchmany(100)
description = curs.description
curs.close()
db.close()
return {
'sync': True,
'result': {
'has_more': False,
'data': list(data),
'meta': [{
'name': column[0],
'type': 'TODO',
'comment': ''
} for column in description],
'type': 'table'
}
}
def open(self):
"""Open the connection"""
super(self.__class__, self).open()
if __NATIVE_DB2__:
# Connect to the local system using db2
self.connection = db2.connect('')
else:
# Connect to the remote system using JDBC
self.connection = jaydebeapi.connect(
jclassname='com.ibm.as400.access.AS400JDBCDriver',
driver_args=[
'jdbc:as400://%s' % self.connection_string,
self.username,
self.password],
jars=configuration.get_config_string(
SETTINGS_FILENAME,
'GENERAL',
'JT400.JAR'),
libs=None)
def dwh_query(query, col, user, password, dwh = dwh_string):
dwh_link = "" % (dwh)
cnx = jaydebeapi.connect("com.teradata.jdbc.TeraDriver",dwh_link,user,password)
cursor = cnx.cursor()
cursor.execute(query)
rows = cursor.fetchall()
data = {}
for each in col:
data[each]=[]
for row in rows:
for each in col:
data[each].append(row[col.index(each)])
data = DataFrame(data, columns=col)
cursor.close()
del cursor
cnx.close()
return data
def connect(self):
self.initConnection()
try:
msg = "what's the location of 'hsqldb.jar'? "
jar = readInput(msg)
checkFile(jar)
args = "-Djava.class.path=%s" % jar
jvm_path = jpype.getDefaultJVMPath()
jpype.startJVM(jvm_path, args)
except Exception as ex:
raise SqlmapConnectionException(getSafeExString(ex))
try:
driver = 'org.hsqldb.jdbc.JDBCDriver'
connection_string = 'jdbc:hsqldb:mem:.' # 'jdbc:hsqldb:hsql://%s/%s' % (self.hostname, self.db)
self.connector = jaydebeapi.connect(driver, connection_string, str(self.user), str(self.password))
except Exception as ex:
raise SqlmapConnectionException(getSafeExString(ex))
self.initCursor()
self.printConnected()
def read_jdbc(sql, jclassname, driver_args, jars=None, libs=None):
"""
Reads jdbc compliant data sources and returns a Pandas DataFrame
uses jaydebeapi
https://pypi.python.org/pypi/JayDeBeApi/
:param sql: select statement
:param jclassname: Full qualified Java class name of the JDBC driver.
e.g. org.postgresql.Driver or com.ibm.db2.jcc.DB2Driver
:param driver_args: Argument or sequence of arguments to be passed to the
Java DriverManager.getConnection method. Usually the
database URL (host, db, schema, etc). See
http://docs.oracle.com/javase/6/docs/api/java/sql/DriverManager.html
for more details
:param jars: Optional jar filename or sequence of filenames for the JDBC driver
:param libs: Optional Dll/so filenames or sequence of dlls/sos used as shared library by the JDBC driver
:return: Pandas DataFrame
"""
try:
conn = jaydebeapi.connect(jclassname, driver_args, jars, libs)
except jaydebeapi.DatabaseError as de:
raise
try:
curs = conn.cursor()
curs.execute(sql)
columns = [desc[0] for desc in curs.description] # getting column headers
return pd.DataFrame(curs.fetchall(), columns=columns) # convert the list of tuples from fetchall() to a df
except jaydebeapi.DatabaseError as de:
raise
finally:
curs.close()
conn.close()
def __init__(self, server, port, namespace, username, password, driver_path):
java_namespace = "com.intersys.jdbc.CacheDriver"
conn_string = "jdbc:Cache://{s}:{p}/{n}".format(s=server, p=port, n=namespace)
self.conn = jaydebeapi.connect(java_namespace, [conn_string, username, password], driver_path)
self.curs = self.conn.cursor()
def connect(self):
url = 'jdbc:sap://%s:%s' %(self.HOST, self.PORT)
return jaydebeapi.connect(
'com.sap.db.jdbc.Driver',
[url, self.USER, self.PASSWORD],
'ngdbc.jar')
#print start_lat
#tempTable = sqlContext(None, "jdbc", None, url=urlDest, dbtable=TTCSQL.getStopSQL("stop_lon", route.route_id, "first"), driver=driverName)
#start_lon = tempTable.stop_lon
#tempTable = sqlContext(None, "jdbc", None, url=urlDest, dbtable=TTCSQL.getStopSQL("stop_lat", route.route_id, "last"), driver=driverName)
#end_lat = tempTable.stop_lat
#tempTable = sqlContext(None, "jdbc", None, url=urlDest, dbtable=TTCSQL.getStopSQL("stop_lon", route.route_id, "last"), driver=driverName)
#end_lon = tempTable.stop_lon
#routeTable.sql(TTCSQL.getLocUpdateSQL("routeTable", route.route_id, start_lat, start_lon, end_lat, end_long))
#sqlContext.sql(TTCSQL.getLocUpdateSQL("routeTable", routeTable.first().route_id, 2, 3, 4, 5))
route = routeTable.collect()
#print route[0].route_id
#print routeTable.count()
#Connect to database for writing
conn = jaydebeapi.connect(driverName, urlDest, driverPath)
curs = conn.cursor()
#curs.execute("INSERT INTO TTC_TRAVEL VALUES (0, 156, '156_0_156', '2015-03-21', '1:00', '2:00', 1234);")
#print curs.fetchall()
#print TTCSQL.getStopSQL("stop_lat, stop_lon", route[0].route_id, "last")
#quit()
for i in range(0, routeTable.count()):
# Start lat and lon
#tempTable = sqlContext.load(None, "jdbc", None, url=urlDest, dbtable=TTCSQL.getStopSQL("stop_lat, stop_lon", route[i].route_id, "first"), driver=driverName)
#curs.execute("UPDATE TTC_ROUTES SET start_lat=" + str(tempTable.first().stop_lat) + ", start_lon=" + str(tempTable.first().stop_lon) + " WHERE route_id=" + str(route[i].route_id) + ";")
# End lat and lon
tempTable = sqlContext.load(None, "jdbc", None, url=urlDest, dbtable=TTCSQL.getStopSQL("stop_lat, stop_lon", route[i].route_id, "last"), driver=driverName)
curs.execute("UPDATE BIXI_ROUTES SET end_lat=" + str(tempTable.first().stop_lat) + ", end_lon=" + str(tempTable.first().stop_lon) + " WHERE station_id=" + str(route[i].route_id) + ";")
#!/opt/discoverytools/anaconda/bin/python2.7
import getpass
import jaydebeapi
JDBC_PATH='/usr/aster/noarch-ncluster-jdbc-driver.jar'
USER = getpass.getuser()
PASSWORD = getpass.getpass()
DATASOURCE = 'jdbc:ncluster://ap74qn12.opr.statefarm.org:2406/ncprddb'
# Open a connection
conn = jaydebeapi.connect(
'com.asterdata.ncluster.Driver',
[DATASOURCE, USER, PASSWORD],
JDBC_PATH)
#### !!!! WARNING - my nCluster access is not working, so these queries are invaled
#### To properly test things, you need to insert a valid query
# Get a cursor, which allows buffered fetching of rows
curs = conn.cursor()
curs.execute('SELECT LOS_EST_DIM_ID FROM FDWAE.LOS_EST_VW FETCH FIRST 10 ROWS ONLY')
# curs.fetchall() will fetch all of the results
rows = curs.fetchall()
for rownum, r in enumerate(rows):
print rownum,":",repr(r)
# Close cursors when you're done
def createDatabaseConnection(environment):
conn = jaydebeapi.connect('oracle.jdbc.driver.OracleDriver',
[DATABASES[environment]],
os.path.join(os.path.abspath(os.path.dirname(__file__)), '../ojdbc6.jar'))
conn.jconn.setAutoCommit(False)
return conn
def _get_connection(this):
if this._conn is None:
this._conn = jaydebeapi.connect(this.DRIVER_CLASS,
[this.DB_URL, this.DB_USER, this.DB_PASS],
this.DRIVER_JAR)
return this._conn
#!/usr/bin/env python # https://pypi.python.org/pypi/JayDeBeApi/ import jaydebeapi import sys if __name__ == '__main__': jdbc_url = "jdbc:solr://solr:9983?collection=test" driverName = "org.apache.solr.client.solrj.io.sql.DriverImpl" statement = "select fielda, fieldb, fieldc, fieldd_s, fielde_i from test limit 10" conn = jaydebeapi.connect(driverName, jdbc_url) curs = conn.cursor() curs.execute(statement) print(curs.fetchall()) conn.close() sys.exit(0)
def connect(self):
# http://hsqldb.org/
# hsqldb.jar
driver, driver_args = "org.hsqldb.jdbcDriver", ["jdbc:hsqldb:mem:.", "SA", ""]
return jaydebeapi, jaydebeapi.connect(driver, driver_args)
def main():
ofMySQL = openfireMySQL()
mySQLTables = ofMySQL.getMySQLTableNames()
hsqlCon = jaydebeapi.connect('org.hsqldb.jdbcDriver',
['jdbc:hsqldb:file:' + sourceDBFile,
sourceUser, sourcePw], ['hsqldb.jar', 'mysql.jar'],)
hsqlCur = hsqlCon.cursor()
hsqlCur.execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.SYSTEM_TABLES WHERE TABLE_SCHEM = '%s'" % sourceDBname)
tables = hsqlCur.fetchall()
hsqlTableList = []
for table in tables:
hsqlCur.execute("SELECT COUNT(*) FROM %s.%s" % (sourceDBname, table[0]))
hsqlTableList.append({'oldname': str(table[0]), 'rowcount': list(hsqlCur.fetchone())[0]})
hsqlTableList[:] = [new for new in hsqlTableList if new.get('rowcount') != 0]
for hsqlDict in hsqlTableList:
for mySQLTable in mySQLTables:
if hsqlDict['oldname'] == str(mySQLTable).upper():
hsqlDict['newname'] = mySQLTable
sqliteCon = sqlite3.connect('hsqldbToMySQL.db')
sqliteCur = sqliteCon.cursor()
sqliteCur.execute("CREATE TABLE IF NOT EXISTS status (tableName TEXT UNIQUE, finished TEXT, lastRow INT)")
sqliteCon.commit()
sqliteCur.execute("SELECT * FROM status")
previouslyProcessedRaw = sqliteCur.fetchall()
previouslyProcessed = {}
for prev in previouslyProcessedRaw:
previouslyProcessed[str(prev[0])] = {'finished': str(prev[1]), 'lastRow': prev[2]}
for table in hsqlTableList:
startingRow = 0
if table['newname'] in previouslyProcessed:
if previouslyProcessed[table['newname']]['finished'] == 'y':
continue
startingRow = previouslyProcessed[table['newname']]['lastRow'] + 1
print '\n' + "Processing table: " + table['newname']
startTime = datetime.datetime.now()
percent = 0
timeremaining = 'Calculating...'
hsqlCur.execute("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS WHERE TABLE_SCHEM = '%s' "
"AND TABLE_NAME = '%s'" % (sourceDBname, table['oldname']))
columns = hsqlCur.fetchall()
columns[:] = [str(x[0]) for x in columns]
cleanColumns = ()
for x in columns:
cleanColumns = cleanColumns + (x,)
cleanColumns = str(cleanColumns).replace("'", "")
for i in xrange(startingRow, table['rowcount']):
if table['rowcount'] > 100 and i % 100 == 0:
percentChange = False
newpercent = ((i * 100) / table['rowcount'])
print "Completed " + str(i) + " records out of " + str(table['rowcount']) + "; " \
+ str(percent) + "%" + "; Time remaining: " + str(timeremaining)
if newpercent > percent:
percentChange = True
percent = newpercent
if percent > 0 and percentChange:
timeremaining = (((datetime.datetime.now() - startTime) /
percent) * (100 - percent))
hsqlCur.execute("SELECT LIMIT %s 1 * FROM %s" % (i, table['oldname']))
data = hsqlCur.fetchone()
cleanData = ()
for x in data:
if type(x) == unicode:
x = x.encode('ascii', 'ignore')
elif type(x) == long:
x = int(x)
cleanData = cleanData + (x,)
try:
ofMySQL.addToMySQL(table['newname'], cleanColumns, cleanData)
sqliteCur.execute("INSERT OR REPLACE INTO status VALUES (?, ?, ?) ", (table['newname'], 'n', i))
sqliteCon.commit()
except Exception as e:
print '\nException: ' + str(e)
print '\nClean Data: ' + str(cleanData)
sqliteCur.execute("INSERT OR REPLACE INTO status VALUES (?, ?, ?) ", (table['newname'], 'n', i))
sqliteCon.commit()
exit(1)
sqliteCur.execute("INSERT OR REPLACE INTO status VALUES (?, ?, ?) ", (table['newname'], 'y', 0))
sqliteCon.commit()
ofMySQL.MySQLClose()
def test_connect_with_sequence(self):
driver, url, driver_args = ( 'org.hsqldb.jdbcDriver',
'jdbc:hsqldb:mem:.',
['SA', ''] )
jaydebeapi.connect(driver, url, driver_args)
def test_connect_with_properties(self):
driver, url, driver_args = ( 'org.hsqldb.jdbcDriver',
'jdbc:hsqldb:mem:.',
{'user': '******', 'password': '' } )
jaydebeapi.connect(driver, url, driver_args)
def get_conn(self):
logging.info("Trying to connect using jdbc url: " + self.jdbc_url)
conn = jaydebeapi.connect(self.jdbc_driver_name,
[str(self.jdbc_url), str(self.login), str(self.psw)],
self.jdbc_driver_loc,)
return conn
