def executeSql(sql):
data = pkgutil.get_data(__package__, 'database.dat')
values = re.split("\W+", data)
try:
db = sybpydb.connect(servername=values[0],
user=values[1],
password=values[2])
c = db.cursor()
c.execute("use us")
# print("executeSql. SQL: "+sql)
# logger.info("executeSql. SQL: "+sql)
c.execute(sql)
c.execute("SELECT @@rowcount")
count = c.fetchall()[0][0]
c.close()
db.close()
return count
except (SystemExit, KeyboardInterrupt):
raise
except sybpydb.Error:
for err in c.connection.messages:
logger.error("Exception %s, Value %s" % (err[0], err[1]))
except Exception:
logger.error('executeSql Failed', exc_info=False)
return 0
def getOneValue(sql):
# logger.info("getOneValue")
data = pkgutil.get_data(__package__, 'database.dat')
values = re.split("\W+", data)
try:
db = sybpydb.connect(servername=values[0],
user=values[1],
password=values[2])
c = db.cursor()
c.execute("use us")
# print("executeSql. SQL: "+sql)
# logger.info("executeSql. SQL: " + sql)
c.execute(sql)
# print(c.rowcount)
data = c.fetchall()
# c.execute("SELECT @@rowcount")
# count = c.fetchall()[0][0]
# print(count)
c.close()
db.close()
return data[0][0] # first row, first column
#return (string.join([row[0] for row in c.fetchall()], '\n'))
except (SystemExit, KeyboardInterrupt):
raise
except sybpydb.Error:
for err in c.connection.messages:
logger.error("Exception %s, Value %s" % (err[0], err[1]))
except Exception:
logger.error('getOneValue Failed', exc_info=False)
return ''
def exec_cmd(sql):
conn = sybpydb.connect(user='******',password='******',servername="PDSQTS1")
cur=conn.cursor()
cur.execute(sql)
result=cur.fetchall()
cur.close()
conn.close()
return result
def exec_cmd(sql):
conn = sybpydb.connect(user='******',password='******',servername="PDSQTS1")
cur=conn.cursor()
cur.execute(sql)
result=cur.fetchall()
cur.close()
conn.close()
return result
def _cursor(self):
"""
Get new cursor
"""
new_conn = False
settings_dict = self.settings_dict
db_str, user_str, passwd_str, port_str, interfaces_path, charset, connection_timeout = None, None, "", None, None, None, None
options = settings_dict['OPTIONS']
# rewrite using dsn
if settings_dict['NAME']:
db_str = settings_dict['NAME']
if settings_dict['USER']:
user_str = settings_dict['USER']
if settings_dict['PASSWORD']:
passwd_str = settings_dict['PASSWORD']
"""
api 2.0 start using dsn
so get additional parametrs
"""
if 'Interfaces' in options:
interfaces_path = options['Interfaces']
if 'Charset' in options:
charset = options['Charset']
else:
# use default utf8
charset = 'UTF8'
if 'Timeout' in options:
connection_timeout = options['Timeout']
if self.connection is None:
new_conn = True
# from sybpydb 2.0 support normal dsn
# if Database.apilevel == '2.0':
# self.connection = Database.connect(dsn=self._create_dsn(user=user_str, pwd=passwd_str, server=db_str, interfaces=interfaces_path, charset=charset, timeout=connection_timeout))
# else:
self.connection = Database.connect(servername=db_str, user=user_str, password=passwd_str)
connection_created.send(sender=self.__class__)
cursor = self.connection.cursor()
if new_conn:
# Set date format for the connection. Also, make sure Sunday is
# considered the first day of the week (to be consistent with the
# Django convention for the 'week_day' Django lookup) if the user
# hasn't told us otherwise
cursor.execute("SET CHAINED OFF")
cursor.execute("SET DATEFORMAT ymd")
self.connection.commit()
# cursor.execute("SET OPTION Date_format = 'yyyy-mm-dd'")
cursor.execute("SET DATEFIRST %s" % self.datefirst)
self.connection.commit()
# set database auto convert to client charset(utf-8)
cursor.execute("SET char_convert UTF8")
self.connection.commit()
# change default database
if self.server_database is not None:
cursor.execute('use %s' % self.server_database)
self.connection.commit()
return CursorWrapper(cursor, self.database_encoding)
def connect_to_db(cls, db_type, server, db_name):
db_conn = ''
if db_type == 'sybase':
try:
db_conn = sybpydb.connect(dsn="servername=%s;database=%s" %
(server, db_name))
break
except Exception as e:
logging.info('Could NOT connect to db')
elif db_type == 'db2':
try:
db_conn = db2.connect(server)
break
except Exception as e:
logging.info('Could NOT connect to db')
return db_conn
def conn_db(conn_data):
'''
连接数据库
:param conn_data:数据库连接配置，在config.py文件中
:return: 返回数据库连接cur
'''
try:
if conn_data['SERVER'] == 'mysql':
cur, conn = connect_mysql(conn_data)
return cur, conn
elif conn_data['SERVER'] == 'sybase':
import sybpydb
conn = sybpydb.connect(user=conn_data['user'],
password=conn_data['passwd'],
servername=conn_data['db'])
cur = conn.cursor()
return cur, conn
except Exception as e:
print(e.__str__() + "\nUsing default :mysql-database...")
def main():
conn = sybase.connect(user=db_UN, password=db_PW, servername=db_SN)
initial_Check()
query = get_Keys
missing_BMCs = get_Missing_BMCs
file_Name = find_File()
stored_Value = store_Values(file_Name)
if use_File == True:
querydb = query_DB(query, conn)
frm_File = get_Missing_From_File()
results = match_Values(stored_Value, querydb, frm_File)
else:
querydb = query_DB(query, conn)
queryformissing = query_DB(missing_BMCs, conn)
results = match_Values(stored_Value, querydb, queryformissing)
conn.close()
generate_Insert(results)
def connectDB(db_type, db_name, connection_strings=['NA', 'NA', 'NA']):
logMsg('connectDB(db_type,db_name,connection_strings=[\'NA\'])')
varValidate('string', 'db_type', db_type)
varValidate('string', 'db_name', db_name)
varValidate('list', 'connection_strings', connection_strings)
try:
db_type = db_type.lower()
if db_type == 'mysql':
import MySQLdb
db = MySQLdb.connect(host=connection_strings[0],
user=connection_strings[1],
password=connection_strings[2],
database=db_name)
elif db_type == 'sybase':
import sybpydb
db = sybpydb.connect(host=connection_strings[0],
user=connection_strings[1],
password=connection_strings[2],
database=db_name)
elif db_type == 'oracle':
import cx_Oracle
db = cx_Oracle.connect(connection_strings[1],
connection_strings[2], db_name)
elif db_type == 'msaccess':
import pypyodbc
db = pypyodbc.connect(
'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=' + db_name)
else:
logMsg('Invalid DB type: ' + str(db_type), 'ERROR')
return 'ops_auto_error'
# db.close()
logMsg('DB connection made to: ' + str(connection_strings[0]), 'INFO')
return db
except Exception as e:
logMsg('DB connection failed:\n' + str(e), 'ERROR')
return 'ops_auto_error'
def __init__(self, username, password, servername) :
self._username = username
self._password = password
self._servername = servername
self.conn = sybase.connect(user=self._username, password=self._password, servername=self._servername)
# INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
# BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
# OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
# TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
# USE OF THE SAMPLE CODE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
# DAMAGE.
#
# Description
# -----------
# This example demonstrates connecting to a server.
# It can be used as an aid in checking a successful installation.
# If it runs to completion, then the SAP ASE Python extension module
# was found and successfully loaded. And it also verifies that a
# connection to the database can be established.
#
# Inputs
# ------
# None.
#
# Server Tables
# -------------
# None.
#
import sybpydb
conn = sybpydb.connect(user='******', password='')
print("Successfully established connection, exiting.")
conn.close()
len = lob.readinto(chunk)
if (len == None):
break
outarr.extend(chunk[:len])
print(outarr.decode())
# Get two file names
if len(sys.argv) < 3:
sys.exit("Usage: %s file1 file2" % sys.argv[0])
if not os.path.exists(sys.argv[1]):
sys.exit('ERROR: file %s not found!' % sys.argv[1])
if not os.path.exists(sys.argv[2]):
sys.exit('ERROR: file %s not found!' % sys.argv[2])
conn = sybpydb.connect(dsn="user=sa;bulklogin=true;chainxacts=off")
cur = conn.cursor()
cur.execute("if exists (select name from sysdatabases where name = \"mydb\") \
drop database mydb")
cur.execute("create database mydb")
cur.execute("exec sp_dboption mydb, \"select into/bulkcopy\", true")
cur.execute("use mydb")
cur.execute("create table mytable (id int, t text, i image)")
cur.close()
# Start the copy in operation
blk = conn.blkcursor()
blk.copy("mytable", direction="in")
# First transfer text and image data using a bytearray.
arr1 = bytearray(b"hello this is some text data")
def checkDBtables(tmPrefsOnly):
okay = False
data = pkgutil.get_data(__package__, 'database.dat')
values = re.split("\W+", data)
db = sybpydb.connect(servername=values[0],
user=values[1],
password=values[2])
c = db.cursor()
try:
c.execute("use us")
c.execute("SELECT name FROM tm_prefs")
data = c.fetchall()
# name = data[0][0] # first row, first column
okay = True
logger.info("tm_prefs exists")
except (SystemExit, KeyboardInterrupt):
raise
except sybpydb.Error:
logger.info("tm_prefs is missing")
okay = False
except Exception:
okay = False
if not okay:
sql = "create table tm_prefs (name varchar(64) not null, param varchar(64) not null, value varchar(255) null, primary key (name, param) )"
try:
c = db.cursor()
c.execute(sql)
okay = True
except (SystemExit, KeyboardInterrupt):
raise
except sybpydb.Error:
# for err in c.connection.messages:
# logger.error("Exception %s, Value %s" % (err[0], err[1]))
logger.info("failed to create tm_prefs")
except Exception:
logger.error('Failed', exc_info=True)
if tmPrefsOnly:
c.close()
db.close()
return okay
okay2 = False
try:
c = db.cursor()
c.execute("SELECT privilege FROM user_privileges")
data = c.fetchall()
# name = data[0][0] # first row, first column
okay2 = True
logger.info("user_privileges exists")
except (SystemExit, KeyboardInterrupt):
raise
except sybpydb.Error:
logger.info("user_privileges is missing")
okay2 = False
except Exception:
okay2 = False
if not okay2:
sql = "create table user_privileges (privilege varchar(30) not null, availability int not null, group_id varchar(30) null, user_id varchar(30) null, description varchar(128) null )"
try:
c = db.cursor()
c.execute(sql)
okay2 = True
except (SystemExit, KeyboardInterrupt):
raise
except sybpydb.Error:
# for err in c.connection.messages:
# logger.error("Exception %s, Value %s" % (err[0], err[1]))
logger.info("failed to create user_privileges")
except Exception:
logger.error('Failed', exc_info=True)
okay3 = False
try:
c = db.cursor()
c.execute("SELECT indx FROM user_privileges_lc")
data = c.fetchall()
# name = data[0][0] # first row, first column
okay3 = True
logger.info("user_privileges_lc exists")
except (SystemExit, KeyboardInterrupt):
raise
except sybpydb.Error:
logger.info("user_privileges_lc is missing")
okay3 = False
except Exception:
okay3 = False
if not okay3:
sql = "create table user_privileges_lc (indx int not null, privilege varchar(30) not null, description varchar(128) null, licensed int not null )"
try:
c = db.cursor()
c.execute(sql)
okay3 = True
except (SystemExit, KeyboardInterrupt):
raise
except sybpydb.Error:
# for err in c.connection.messages:
# logger.error("Exception %s, Value %s" % (err[0], err[1]))
logger.info("failed to create user_privileges_lc")
except Exception:
logger.error('Failed', exc_info=True)
okay4 = False
try:
c = db.cursor()
c.execute("SELECT * FROM groups")
data = c.fetchall()
# name = data[0][0] # first row, first column
okay4 = True
logger.info("groups exists")
except (SystemExit, KeyboardInterrupt):
raise
except sybpydb.Error:
logger.info("groups is missing")
okay4 = False
except Exception:
okay4 = False
if not okay4:
sql = "create table groups (group_name varchar(30) not null, user_id varchar(30) null )"
try:
c = db.cursor()
c.execute(sql)
okay4 = True
except (SystemExit, KeyboardInterrupt):
raise
except sybpydb.Error:
# for err in c.connection.messages:
# logger.error("Exception %s, Value %s" % (err[0], err[1]))
logger.info("failed to create groups")
except Exception:
logger.error('Failed', exc_info=True)
sql = "insert into groups (group_name, user_id) values ('everyone', '')"
try:
c = db.cursor()
c.execute(sql)
okay4 = True
except (SystemExit, KeyboardInterrupt):
raise
except sybpydb.Error:
# for err in c.connection.messages:
# logger.error("Exception %s, Value %s" % (err[0], err[1]))
logger.info("failed to insert groups")
except Exception:
logger.error('Failed', exc_info=True)
c.close()
db.close()
return okay and okay2 and okay3 and okay4
def updatePrivBasedOnLicensing():
data = pkgutil.get_data(__package__, 'database.dat')
values = re.split("\W+", data)
try:
db = sybpydb.connect(servername=values[0],
user=values[1],
password=values[2])
c = db.cursor()
c.execute("use us")
sql = "SELECT distinct user_privileges_lc.privilege, user_privileges_lc.description FROM user_privileges, user_privileges_lc where availability = 0 and user_privileges.privilege = user_privileges_lc.privilege and licensed = 0 "
c.execute(sql)
data = c.fetchall()
for row in data:
privilege = row[0]
description = row[1]
sql = "delete FROM user_privileges where privilege = '" + privilege + "' and availability = 0"
c.execute(sql)
sql = "insert into user_privileges (privilege, availability, group_id, user_id, description) values ('" + privilege + "', 1, '', '', '" + description + "')"
c.execute(sql)
bEveryoneCheck = False
sql = "SELECT distinct user_privileges_lc.privilege, user_privileges_lc.description FROM user_privileges, user_privileges_lc where availability = 1 and user_privileges.privilege = user_privileges_lc.privilege and licensed = 1 "
" and user_privileges_lc.privilege in ('omusl_manage_patients', 'omusl_manage_studies', 'omusl_push_monitor', 'omusl_run', 'omusl_study_status',"
" 'omv_add_report', 'omv_edit_report', 'omv_email', 'omv_push', 'omv_save_anno', 'omv_search', 'omv_show_anno', 'omv_view', 'omx_multy', 'omx_run', 'omusl_vcd',"
" 'allpro_images', 'omusl_wklst_scu', 'omusl_scanner', 'omusl_attach', 'omusl_non_dicom', 'omusl_lightscribe', 'omusl_cd_import', 'omusl_jpeg_export',"
" 'omv_adv_anno', 'omv_https', 'omusl_radviewer')"
c.execute(sql)
data = c.fetchall()
for row in data:
privilege = row[0]
description = row[1]
sql = "delete FROM user_privileges where privilege = '" + privilege + "' and availability = 1"
c.execute(sql)
sql = "insert into user_privileges (privilege, availability, group_id, user_id, description) values ('" + privilege + "', 0, 'everyone', '', '" + description + "')"
c.execute(sql)
bEveryoneCheck = True
if (bEveryoneCheck):
sql = "SELECT group_name FROM groups WHERE group_name = 'everyone'"
c.execute(sql)
data = c.fetchall()
if (data[0][0] != ""):
sql = "insert into groups (group_name) values ('everyone', '')"
c.execute(sql)
bAdminCheck = False
sql = "SELECT distinct user_privileges_lc.privilege, user_privileges_lc.description FROM user_privileges, user_privileges_lc where availability = 1 and user_privileges.privilege = user_privileges_lc.privilege and licensed = 1 "
" and user_privileges_lc.privilege in ('omacm_add_priv', 'omacm_admin', 'omadmin_cc', 'omadmin_console', 'omadmin_db_check', 'omadmin_dict', 'omadmin_distr',"
" 'omadmin_erpr', 'omadmin_file_audit', 'omadmin_flex', 'omadmin_hp', 'omadmin_kds', 'omadmin_push', 'omadmin_run', 'omadmin_utils', 'omsdm_power_on',"
" 'omstm_run', 'omstm_admin', 'omusl_profile', 'omv_vitrea', 'pacs_hl7_adv', 'omv_push_adv', 'pacs_ipad', 'pacs_android', 'omx_publishing',"
" 'omusl_vcd_import', 'omusl_oncall_caching', 'rsvw_dictation', 'omv_print', 'omv_dicom_print', 'omv_multi_monitor', 'autoupdate_run', 'omusl_adv_demo',"
" 'omusl_adv_filters', 'pacs_wklst_scp', 'pacs_report_activity', 'pacs_backup', 'pacs_backup_adv', 'pacs_hl7', 'pacs_hl7_adv')"
for row in data:
privilege = row[0]
description = row[1]
sql = "delete FROM user_privileges where privilege = '" + privilege + "' and availability = 1"
c.execute(sql)
sql = "insert into user_privileges (privilege, availability, group_id, user_id, description) values ('" + privilege + "', 0, '', 'admin', '" + description + "')"
c.execute(sql)
bAdminCheck = True
if (bAdminCheck):
sql = "SELECT user_id FROM users where user_id = 'admin'"
c.execute(sql)
data = c.fetchall()
if (data[0][0] != ""):
sql = "insert into users (user_id, name, last_name, first_name, password) values ('admin', 'PACSimple Admin', 'Admin', 'PACSimple', 'admin!')"
c.execute(sql)
sql = "SELECT privilege FROM user_privileges where user_id = 'admin' and privilege = 'omadmin_run'"
c.execute(sql)
data = c.fetchall()
if (data[0][0] != ""):
c.execute(
"SELECT description FROM user_privileges where privilege = 'omadmin_run'"
)
sql = "delete FROM user_privileges where privilege = 'omadmin_run' and availability = 1"
c.execute(sql)
data = c.fetchall()
sDescription = data[0][0]
sql = "insert into user_privileges (privilege, availability, group_id, user_id, description) values ('omadmin_run', 0, '', 'admin', '" + sDescription + "')"
c.execute(sql)
sql = "SELECT privilege FROM user_privileges where user_id = 'admin' and privilege = 'omacm_admin'"
c.execute(sql)
data = c.fetchall()
if (data[0][0] != ""):
c.execute(
"SELECT description FROM user_privileges where privilege = 'omacm_admin'"
)
sql = "delete FROM user_privileges where privilege = 'omacm_admin' and availability = 1"
c.execute(sql)
data = c.fetchall()
sDescription = data[0][0]
sql = "insert into user_privileges (privilege, availability, group_id, user_id, description) values ('omacm_admin', 0, '', 'admin', '" + sDescription + "')"
c.execute(sql)
sql = "SELECT privilege FROM user_privileges where user_id = 'admin' and privilege = 'omacm_add_priv'"
c.execute(sql)
data = c.fetchall()
if (data[0][0] != ""):
c.execute(
"SELECT description FROM user_privileges where privilege = 'omacm_add_priv'"
)
sql = "delete FROM user_privileges where privilege = 'omacm_add_priv' and availability = 1"
c.execute(sql)
data = c.fetchall()
sDescription = data[0][0]
sql = "insert into user_privileges (privilege, availability, group_id, user_id, description) values ('omacm_add_priv', 0, '', 'admin', '" + sDescription + "')"
c.execute(sql)
c.close()
db.close()
except (SystemExit, KeyboardInterrupt):
raise
except sybpydb.Error:
for err in c.connection.messages:
logger.error("Exception %s, Value %s" % (err[0], err[1]))
except Exception:
logger.error('updatePrivBasedOnLicensing Failed', exc_info=False)
len = lob.readinto(chunk);
if (len == None):
break
outarr.extend(chunk[:len])
print(outarr.decode())
# Get two file names
if len(sys.argv) < 3:
sys.exit("Usage: %s file1 file2" % sys.argv[0])
if not os.path.exists(sys.argv[1]):
sys.exit('ERROR: file %s not found!' % sys.argv[1])
if not os.path.exists(sys.argv[2]):
sys.exit('ERROR: file %s not found!' % sys.argv[2])
conn = sybpydb.connect(user='******', password='', dsn='chainxacts=off')
cur = conn.cursor()
cur.execute("if exists (select name from sysdatabases where name = \"mydb\") \
drop database mydb")
cur.execute("create database mydb")
cur.execute("use mydb")
cur.execute("create table mytable (i int, t1 text, i1 image)")
# First transfer text and image data using a bytearray.
arr1 = bytearray(b"hello this is some text data")
lob1 = sybpydb.Lob(sybpydb.TEXT, arr1)
arr2 = bytearray(b"hello this is some image data")
lob2 = sybpydb.Lob(sybpydb.IMAGE, arr2)
cur.execute("insert into mytable values (?, ?, ?)", [1, lob1, lob2])
#
# Description
# -----------
# This example demonstrates connecting to a server using a dsn
#
# Inputs
# ------
# None.
#
# Server Tables
# -------------
#
import os
import sybpydb
server = os.getenv("DSQUERY")
user = "******"
dsn_str = "servername=%s;user=%s" % (server, user)
conn = sybpydb.connect(dsn=dsn_str)
cur = conn.cursor()
cur.execute("select suser_name()")
row = cur.fetchone()
print ("We're logged in as '%s'" % row[0])
cur.execute("select db_name()")
row = cur.fetchone()
print ("The current database is '%s'" % row[0])
cur.close()
conn.close()