def selColumn(table, column, cursor=None, cnctn=None, datasrc=None):
'''
Function: selColumn
Input:
table - table from which to select
column - column to select
cusror - database cursor
cntn - database connection
datsrc - data source name (e.g., odbc connection name)
Output: valuelist - a list of values of column in table
Functionality: Selects a column from a table given a cursor or, barring
that, a connection or, barring that, a data source.
'''
# if the user provided a cursor, we'll want to use that, but if not
if not cursor:
# if the user provided a connection, we'll want to use that, but if not
if not cnctn:
# get the connection using the datasource name
cnctn = connect('Data Source=%s;Trusted_Connection=true;'%(datasrc))
cursor = cnctn.cursor() # get cursor from connection
# select column from table
valuelist = selColumnCursor(cursor, table, column)
return valuelist # return output
def connect(self):
"""Connect to wincc mssql database using WinCCOLEDBProvider.1"""
if not self.database:
warnings.warn("Initial Database not given. Will try to fetch it's\
name. But this will take some time.")
logging.info("Trying to fetch wincc database name")
self.database = self.fetch_wincc_database_name()
if not self.database:
raise WinCCException("Could not fetch WinCC runtime database. \
Please check databases at host {host}.".format(host=self.host))
try:
logging.info("Trying to connect to %s database %s", self.host,
self.database)
# Python looses it's current working dir in the next instruction
# Reset after connect
curr_dir = os.getcwd()
self.conn = adodbapi.connect(self.conn_str,
provider=self.provider,
host=self.host,
database=self.database)
os.chdir(curr_dir)
self.cursor = self.conn.cursor()
except (adodbapi.DatabaseError, adodbapi.InterfaceError) as e:
print(e)
print(traceback.format_exc())
raise WinCCException(message='Connection to host {host} failed.'
.format(host=self.host))
def testThatTestcasesRunsInATransaction(self):
otherConn=adodbapi.connect(constr)
otherCrsr=otherConn.cursor()
maybeDropTableStatment="""
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'tempDeleteMe'
AND type = 'U')
DROP TABLE tempDeleteMe
"""
maybeDropProcStatment="""
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'ut_evilTestCase'
AND type = 'P')
DROP PROCEDURE ut_evilTestCase
"""
createTableStatement= """
CREATE TABLE tempDeleteMe (
aColumn varchar(500) NULL)
"""
insertStatement="""
INSERT INTO tempDeleteMe(aColumn)
VALUES('Important data, should not be deleted by the testcase')
"""
otherCrsr.execute(maybeDropTableStatment)
otherCrsr.execute(createTableStatement)
otherCrsr.execute(insertStatement)
otherConn.commit()
otherConn.close()
otherConn=None
otherCrsr=None
evilproc="""
CREATE PROCEDURE ut_evilTestCase AS
TRUNCATE TABLE tempDeleteMe
"""
self.crsr.execute(evilproc)
self.crsr.callproc('tsu_runTests')
self.conn.commit()
self.crsr.execute("SELECT aColumn FROM tempDeleteMe")
rs=self.crsr.fetchone()
assert rs[0]=='Important data, should not be deleted by the testcase'
#clean Up
otherConn=adodbapi.connect(constr)
otherCrsr=otherConn.cursor()
otherCrsr.execute(maybeDropTableStatment)
otherCrsr.execute(maybeDropProcStatment)
otherConn.commit()
def testconnstr(connstr):
try:
conn = adodbapi.connect(connstr)
except Exception as ext:
return False
conn.close()
return True
def load_org_table():
"""
Load the correct Org tables for the report date specified
"""
strReportDate = str(sys.argv[1])
print 'strReportDate:' + strReportDate
f = file('AfpReports.cfg')
cfg = Config(f)
strSqlServerConn = cfg.strSqlServerConn
strConn = strSqlServerConn
oConn = adodbapi.connect(strConn)
strSp = "usp_SetupOrgTablesForReports"
lstSpParams = list()
lstSpParams.append(strReportDate)
oCmdData = oConn.cursor()
oCmdData.callproc(strSp, lstSpParams)
oConn.commit()
def connect(self):
"""Connect to wincc mssql database using WinCCOLEDBProvider.1"""
if not self.database:
warnings.warn("Initial Database not given. Will try to fetch it's\
name. But this will take some time.")
logging.info("Trying to fetch wincc database name")
self.database = self.fetch_wincc_database_name()
if not self.database:
raise WinCCException("Could not fetch WinCC runtime database. \
Please check databases at host {host}.".format(host=self.host))
try:
logging.info("Trying to connect to %s database %s", self.host,
self.database)
# Python looses it's current working dir in the next instruction
# Reset after connect
curr_dir = os.getcwd()
self.conn = adodbapi.connect(self.conn_str,
provider=self.provider,
host=self.host,
database=self.database)
os.chdir(curr_dir)
self.cursor = self.conn.cursor()
except (adodbapi.DatabaseError, adodbapi.InterfaceError) as e:
print(e)
print(traceback.format_exc())
raise WinCCException(
message='Connection to host {host} failed.'.format(
host=self.host))
def connectToNlpdev(api='adodbapi'):
'''
Function: connectToNlpdev
Input:
api - which odbc api to use. originally we only used adodbapi but had
problems with that on VMs so added pyodbc, which seems to work
better but I didn't want to chance breaking all the old stuff
Output: cnctn - connection to NLPdev database on ghriNLP server
Functionality: Creates a connection to NLPdev on ghriNLP
History:
12/30/10 - created
7/13/11 - added api input and modified to use pyodbc as well
7/27/11 - added warning logging message for using adodbapi from some
machines at GHRI
'''
if api == 'adodbapi':
logging.warning('Using adodbapi to connect to Nlpdev may not work ' + \
'on some machines, including GHRI VMs')
# make connection with adodbapi
cnctn = connect('Data Source=ghriNLP;Initial Catalog=NLPdev;' + \
'Trusted_Connection=true;')
elif api == 'pyodbc':
# make connection with pyodbc
cnctn = pyoconnect('DSN=ghriNLP;DATABASE=NLPdev;')
else:
logging.warning('Unrecognized api, using pyodbc')
# make connection with pyodbc
cnctn = pyoconnect('DSN=ghriNLP;DATABASE=NLPdev;')
return cnctn # return output
def get_cursor(self, instance):
"""
Return a cursor to execute query against the db
Cursor are cached in the self.connections dict
"""
conn_key = self._conn_key(instance)
host = instance.get("host")
database = instance.get("database")
service_check_tags = ["host:%s" % host, "db:%s" % database]
if conn_key not in self.connections:
try:
conn_str = self._conn_string(instance)
conn = adodbapi.connect(conn_str)
self.connections[conn_key] = conn
self.service_check(self.SERVICE_CHECK_NAME, AgentCheck.OK, tags=service_check_tags)
except Exception:
cx = "%s - %s" % (host, database)
message = "Unable to connect to SQL Server for instance %s." % cx
self.service_check(
self.SERVICE_CHECK_NAME, AgentCheck.CRITICAL, tags=service_check_tags, message=message
)
password = instance.get("password")
tracebk = traceback.format_exc()
if password is not None:
tracebk = tracebk.replace(password, "*" * 6)
raise Exception("%s \n %s" % (message, tracebk))
conn = self.connections[conn_key]
cursor = conn.cursor()
return cursor
def check(self, instance):
try:
import adodbapi
except ImportError:
raise Exception("Unable to import adodbapi module.")
host = instance.get('host', '127.0.0.1;1433')
username = instance.get('username')
password = instance.get('password')
database = instance.get('database', 'master')
dimensions = instance.get('dimensions', {})
conn_key = self._conn_key(host, username, password, database)
if conn_key not in self.connections:
try:
conn_str = self._conn_string(host, username, password, database)
conn = adodbapi.connect(conn_str)
self.connections[conn_key] = conn
except Exception:
cx = "%s - %s" % (host, database)
raise Exception("Unable to connect to SQL Server for instance %s.\n %s"
% (cx, traceback.format_exc()))
conn = self.connections[conn_key]
cursor = conn.cursor()
self._fetch_metrics(cursor, dimensions)
def open_db_connections(self, instance):
"""
We open the db connections explicitly, so we can ensure they are open
before we use them, and are closable, once we are finished. Open db
connections keep locks on the db, presenting issues such as the SQL
Server Agent being unable to stop.
"""
conn_key = self._conn_key(instance)
timeout = int(instance.get('command_timeout',
self.DEFAULT_COMMAND_TIMEOUT))
try:
rawconn = adodbapi.connect(self._conn_string(instance=instance),
timeout=timeout)
if conn_key not in self.connections:
self.connections[conn_key] = {'conn': rawconn, 'timeout': timeout}
else:
try:
# explicitly trying to avoid leaks...
self.connections[conn_key]['conn'].close()
except Exception as e:
self.log.info("Could not close adodbapi db connection\n{0}".format(e))
self.connections[conn_key]['conn'] = rawconn
except Exception as e:
self.log.warning("Could not connect to SQL Server\n{0}".format(e))
def execute(self, connection=None):
conn = connection or OleDb.connect(CONN_STRING)
cursor = execute(str(self), conn)
if connection is None:
return cursor
conn.close()
return cursor
def check(self, instance):
try:
import adodbapi
except ImportError:
raise Exception("Unable to import adodbapi module.")
host = instance.get('host', '127.0.0.1;1433')
username = instance.get('username')
password = instance.get('password')
database = instance.get('database', 'master')
tags = instance.get('tags', [])
conn_key = self._conn_key(host, username, password, database)
if conn_key not in self.connections:
try:
conn_str = self._conn_string(host, username, password, database)
conn = adodbapi.connect(conn_str)
self.connections[conn_key] = conn
except:
raise Exception("Unable to connect to SQL Server for instance %s" \
% instance)
conn = self.connections[conn_key]
cursor = conn.cursor()
self._fetch_metrics(cursor, tags)
def query(sql, limit):
data = []
error = None
idx = 0
cidx = 0
encoding = sys.getfilesystemencoding()
conn = adodbapi.connect(PROVIDER)
try:
cursor = conn.cursor()
cursor.execute(sql)
for idx, record in enumerate(cursor):
if idx >= limit:
break
line = []
for cidx, column in enumerate(record):
if type(column) == str:
column = column.decode(encoding)
elif type(column) == datetime.datetime:
column = int((
column - datetime.datetime.utcfromtimestamp(0)
).total_seconds())
line.append(column)
data.append(tuple(line))
except adodbapi.apibase.DatabaseError, e:
# ZOMG
parts = e.message.split('\n')
code = eval(parts[0])[1].decode(encoding)
error = '\n'.join(parts[1:]) + '\n' + code
def onClickedOK(self):
myapp.SetEnabled_True()
name = (str(self.ui.lineEdit_name.text()))
furigana = (str(self.ui.lineEdit_furigana.text()))
category = (str(self.ui.comboBox.currentText()))
company = (str(self.ui.lineEdit_company.text()))
address = (str(self.ui.lineEdit_address.text()))
phone = (str(self.ui.lineEdit_phone.text()))
mobile = (str(self.ui.lineEdit_mobile.text()))
Fax = (str(self.ui.lineEdit_Fax.text()))
Email = (str(self.ui.lineEdit_Email.text()))
# connect to the database
database = r"./data/Database1.mdb"
constr = 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=%s' % database
conn = adodbapi.connect(constr)
# create a cursor
cur2 = conn.cursor()
# update the data
tablename = "address"
values = """氏名 = '{0}', フリガナ = '{1}', 種別 = '{2}', 会社名 = '{3}', 住所 = '{4}',
電話番号 = '{5}', 携帯 = '{6}', \"Fax\" = '{7}', \"Email\" = '{8}'""" \
.format(name, furigana, category, company, address, phone, mobile, Fax, Email)
sql = 'update {0} set {1} where "ID" = {2};'.format(
tablename, values, ID)
cur2.execute(sql)
conn.commit()
# close the cursor and connection
cur2.close()
conn.close()
myapp.updateUi()
def check(self, instance):
try:
import adodbapi
except ImportError:
raise Exception("Unable to import adodbapi module.")
host = instance.get('host', '127.0.0.1;1433')
username = instance.get('username')
password = instance.get('password')
database = instance.get('database', 'master')
conn_key = self._conn_key(host, username, password, database)
dimensions = self._set_dimensions(None, instance)
if conn_key not in self.connections:
try:
conn_str = self._conn_string(host, username, password, database)
conn = adodbapi.connect(conn_str)
self.connections[conn_key] = conn
except Exception:
cx = "%s - %s" % (host, database)
raise Exception("Unable to connect to SQL Server for instance %s.\n %s"
% (cx, traceback.format_exc()))
conn = self.connections[conn_key]
cursor = conn.cursor()
self._fetch_metrics(cursor, dimensions)
def updateUi(self):
# connect to the database
database = r"./data/Database1.mdb"
constr = 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=%s' % database
conn = adodbapi.connect(constr)
# create a cursor
cur = conn.cursor()
# extract all the data
tablename = "address"
sql = "select * from %s order by フリガナ" % tablename
cur.execute(sql)
# show the cursor
r = 0
self.ui.tableWidget.setRowCount(1000)
self.ui.tableWidget.setColumnCount(10)
for row in cur:
self.ui.tableWidget.setItem(r, 0, QtGui.QTableWidgetItem(row.ID))
self.ui.tableWidget.setItem(r, 1, QtGui.QTableWidgetItem(row.氏名))
self.ui.tableWidget.setItem(r, 2, QtGui.QTableWidgetItem(row.フリガナ))
self.ui.tableWidget.setItem(r, 3, QtGui.QTableWidgetItem(row.種別))
self.ui.tableWidget.setItem(r, 4, QtGui.QTableWidgetItem(row.会社名))
self.ui.tableWidget.setItem(r, 5, QtGui.QTableWidgetItem(row.住所))
self.ui.tableWidget.setItem(r, 6, QtGui.QTableWidgetItem(row.電話番号))
self.ui.tableWidget.setItem(r, 7, QtGui.QTableWidgetItem(row.携帯))
self.ui.tableWidget.setItem(r, 8, QtGui.QTableWidgetItem(row.Fax))
self.ui.tableWidget.setItem(r, 9,
QtGui.QTableWidgetItem(row.Email))
r += 1
# close the cursor and connection
cur.close()
conn.close()
def check(self, instance):
try:
import adodbapi
except ImportError:
self.log.error("Unable to import adodbapi module.")
return
host = instance.get('host', '127.0.0.1;1433')
username = instance.get('username')
password = instance.get('password')
database = instance.get('database', 'master')
conn_key = self._conn_key(host, username, password, database)
if conn_key not in self.connections:
try:
conn_str = self._conn_string(host, username, password,
database)
conn = adodbapi.connect(conn_str)
self.connections[conn_key] = conn
except:
self.log.exception("Unable to connect to SQL Server for instance %s" \
% instance)
return
conn = self.connections[conn_key]
cursor = conn.cursor()
self._fetch_metrics(cursor)
def open_db_connections(self, instance):
"""
We open the db connections explicitly, so we can ensure they are open
before we use them, and are closable, once we are finished. Open db
connections keep locks on the db, presenting issues such as the SQL
Server Agent being unable to stop.
"""
conn_key = self._conn_key(instance)
timeout = int(
instance.get('command_timeout', self.DEFAULT_COMMAND_TIMEOUT))
try:
rawconn = adodbapi.connect(self._conn_string(instance=instance),
timeout=timeout)
if conn_key not in self.connections:
self.connections[conn_key] = {
'conn': rawconn,
'timeout': timeout
}
else:
try:
# explicitly trying to avoid leaks...
self.connections[conn_key]['conn'].close()
except Exception as e:
self.log.info(
"Could not close adodbapi db connection\n{0}".format(
e))
self.connections[conn_key]['conn'] = rawconn
except Exception as e:
self.log.warning("Could not connect to SQL Server\n{0}".format(e))
def GetGeneralComments(intMaxCommentNo, intCode, intLevel, strReportDate, strConn, oFile, strGeneralCommentID):
strStartReportDate = GetCommentStartReportDate(strReportDate, intLevel)
oCommentsConn = adodbapi.connect(strConn)
strCommentHeader = GetGeneralCommentHeader()
oFile.write(strCommentHeader)
oData = GetGeneralCommentsData(intMaxCommentNo, intCode, strGeneralCommentID, strStartReportDate, strReportDate, intLevel, oCommentsConn)
intRowCount = len(oData)
if (intRowCount != 0):
oFile.write(GetGeneralCommentHtml())
for x in range(intRowCount): #loop through all valid Factors
oComment = oData[x][0]
oDemographicComment = oData[x][1]
strEncodedComment = oComment.encode('utf8')
strEncodedDemographicComment = oDemographicComment.encode('utf8')
oFile.write(strEncodedComment + '<br>' + strEncodedDemographicComment + '<br><br>')
else:
oFile.write(GetNoCommentsForStatementHtml())
oFile.write(GetStatementCommentEndHtml())
oFile.write(GetCommentSeperatorHtml())
oFile.write("</div>")
oFile.write("<br class='clear'/>")
oFile.write(GetGlobalCommentFooter())
def try_connection(constr):
import adodbapi
try:
s = adodbapi.connect(constr) #connect to server
s.close()
except adodbapi.DatabaseError, inst:
print(inst.args[0]) # should be the error message
return False
def connect(self):
conn_str = "Provider=%(provider)s; Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=%(database)s;Data Source=%(host)s"
provider = 'SQLOLEDB.1'
self.conn = adodbapi.connect(conn_str,
provider=provider,
host=self.host,
database=self.database)
self.cursor = self.conn.cursor()
def connect(self):
conn_str = "Provider=%(provider)s;Catalog=%(database)s;Data Source=%(host)s"
provider = 'WinCCOLEDBProvider.1'
self.conn = adodbapi.connect(conn_str,
provider=provider,
host=self.host,
database=self.database)
self.cursor = self.conn.cursor()
def open_db_connections(self, db_key, db_name=None):
"""
We open the db connections explicitly, so we can ensure they are open
before we use them, and are closable, once we are finished. Open db
connections keep locks on the db, presenting issues such as the SQL
Server Agent being unable to stop.
"""
conn_key = self._conn_key(db_key, db_name)
_, host, username, password, database, _ = self._get_access_info(
db_key, db_name)
cs = self.instance.get('connection_string', '')
if 'Trusted_Connection=yes' in cs and (username or password):
self.log.warning(
"Username and password are ignored when using Windows authentication"
)
cs += ';' if cs != '' else ''
try:
if self.get_connector() == 'adodbapi':
cs += self._conn_string_adodbapi(db_key, db_name=db_name)
# autocommit: true disables implicit transaction
rawconn = adodbapi.connect(cs, {
'timeout': self.timeout,
'autocommit': True
})
else:
cs += self._conn_string_odbc(db_key, db_name=db_name)
rawconn = pyodbc.connect(cs, timeout=self.timeout)
self.service_check_handler(AgentCheck.OK, host, database)
if conn_key not in self._conns:
self._conns[conn_key] = rawconn
else:
try:
# explicitly trying to avoid leaks...
self._conns[conn_key].close()
except Exception as e:
self.log.info("Could not close adodbapi db connection\n%s",
e)
self._conns[conn_key] = rawconn
except Exception as e:
cx = "{} - {}".format(host, database)
message = "Unable to connect to SQL Server for instance {}: {}".format(
cx, repr(e))
password = self.instance.get('password')
if password is not None:
message = message.replace(password, "*" * 6)
self.service_check_handler(AgentCheck.CRITICAL, host, database,
message)
raise_from(SQLConnectionError(message), None)
def open_db_connections(self, instance):
"""
We open the db connections explicitly, so we can ensure they are open
before we use them, and are closable, once we are finished. Open db
connections keep locks on the db, presenting issues such as the SQL
Server Agent being unable to stop.
"""
conn_key = self._conn_key(instance)
timeout = int(
instance.get('command_timeout', self.DEFAULT_COMMAND_TIMEOUT))
host = instance.get('host')
database = instance.get('database', self.DEFAULT_DATABASE)
service_check_tags = ['host:%s' % host, 'db:%s' % database]
try:
if self._get_connector(instance) == 'adodbapi':
cs = self._conn_string_adodbapi(instance=instance)
rawconn = adodbapi.connect(cs, timeout=timeout)
else:
cs = self._conn_string_odbc(instance=instance)
rawconn = pyodbc.connect(cs, timeout=timeout)
self.service_check(self.SERVICE_CHECK_NAME,
AgentCheck.OK,
tags=service_check_tags)
if conn_key not in self.connections:
self.connections[conn_key] = {
'conn': rawconn,
'timeout': timeout
}
else:
try:
# explicitly trying to avoid leaks...
self.connections[conn_key]['conn'].close()
except Exception as e:
self.log.info(
"Could not close adodbapi db connection\n{0}".format(
e))
self.connections[conn_key]['conn'] = rawconn
except Exception as e:
cx = "%s - %s" % (host, database)
message = "Unable to connect to SQL Server for instance %s." % cx
self.service_check(self.SERVICE_CHECK_NAME,
AgentCheck.CRITICAL,
tags=service_check_tags,
message=message)
password = instance.get('password')
tracebk = traceback.format_exc()
if password is not None:
tracebk = tracebk.replace(password, "*" * 6)
cxn_failure_exp = SQLConnectionError("%s \n %s" %
(message, tracebk))
raise cxn_failure_exp
def adodbapi_connection(server, database, username, password):
import adodbapi
connectors = ["Provider=SQLOLEDB"]
connectors.append("Data Source=%s" % server)
connectors.append("Initial Catalog=%s" % database)
connectors.append("User Id=%s" % username)
connectors.append("Password=%s" % password)
return adodbapi.connect(";".join(connectors))
def on_init(self):
super().on_init()
connstring = self.configuration['connstring']
self.conn = adodbapi.connect(connstring)
self.query = self.configuration['query']
if 'separator' in self.configuration:
self.separator = self.configuration['separator']
def setUp(self):
self.conn = adodbapi.connect(constr)
self.crsr = self.conn.cursor()
self.crsr.execute(
"select count(*) from sysobjects where xtype='P' and name LIKE 'ut[_]%' "
)
assert self.crsr.fetchone(
)[0] == 0, """Error when setting up testcases, old testcases in database.
def sqlserver_sql(self,sql):
db = adodbapi.connect('Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog='+self.db_user+';Data Source='+self.db_name+';Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;User Id = '+self.db_pw,300)
cursor = db.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
db.close()
return rows
def try_connection(constr):
import adodbapi
try:
s = adodbapi.connect(constr) #connect to server
s.close()
except adodbapi.DatabaseError as inst:
print((inst.args[0])) # should be the error message
return False
print(' (successful)')
return True
def _DbOpen():
'Return an open connection to the database'
#conStr = r'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=M:/Finance/camel/camel.mdb;'
#conn = win32com.client.Dispatch(r'ADODB.Connection')
src = 'M:/Finance/camel/camel.mdb'
src = '"M:/Finance/SREL PMS/camel.mdb"'
constr = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE={0};'.format(src)
conn = adodbapi.connect(constr)
#conn.Open(constr)
return conn
def get_con_resources(con_dets, default_dbs, db=None):
"""
When opening from scratch, e.g. clicking on Report Tables from Start, no db,
so must identify one, but when selecting dbe-db in dropdowns, there will be
a db.
Returns dict with con, cur, dbs, db.
"""
con_dets_access = con_dets.get(mg.DBE_MS_ACCESS)
if not con_dets_access:
raise my_exceptions.MissingConDets(mg.DBE_MS_ACCESS)
## get the (only) database and use it to get the connection details
if not db:
## use default if possible, or fall back to first
default_db_access = default_dbs.get(mg.DBE_MS_ACCESS) ## might be None
if default_db_access:
db = default_db_access
else:
db = con_dets_access.keys()[0]
if not con_dets_access.get(db):
raise Exception(f"No connections for MS Access database {db}")
con_dets_access_db = con_dets_access[db]
"""
DSN syntax - http://support.microsoft.com/kb/193332 and
http://www.codeproject.com/database/connectionstrings.asp?df=100&forumid=3917&exp=0&select=1598401
"""
## Connection keywords e.g. pwd, must be plain strings not unicode strings.
database = con_dets_access_db['database']
user = con_dets_access_db['user']
pwd = con_dets_access_db['pwd']
mdw = con_dets_access_db['mdw']
DSN = (f"PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE={database};"
f"USER ID={user};PASSWORD={pwd};"
f"Jet OLEDB:System Database={mdw};")
try:
con = adodbapi.connect(DSN)
except Exception as e:
raise Exception(
"Unable to connect to MS Access database using "
f"supplied database: {database}, user: {user}, "
f"pwd: {pwd}, or mdw: {mdw}.\nCaused by error: {b.ue(e)}")
cur = con.cursor() ## must return tuples not dics
cur.adoconn = con.adoConn ## (need to access from just the cursor)
if not has_tbls(cur, db):
raise Exception(
_("Selected database \"%s\" didn't have any tables.") % db)
con_resources = {
mg.DBE_CON: con,
mg.DBE_CUR: cur,
mg.DBE_DBS: [
db,
],
mg.DBE_DB: db
}
return con_resources
def __init__(self, conninfo, hooks, temp_dir = None):
self.hooks = hooks
self.tempdir = None
self.conn = None
try:
self.conn = adodbapi.connect(conninfo)
self.tempdir = tempdir.TempDir(temp_dir or "fe_tmp_swit", clear_location_if_existing = True) # "_swit" extension to exclude it from our McAfee
except:
self.cleanup()
raise
def main():
con_str = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};'.format(config.PATH_ACCDB)
conn = adodbapi.connect(con_str)
cur = conn.cursor()
cur.execute("select item_name from item")
for c in cur.fetchall():
print(c[0]) #=> `ringo`, `みかん
cur.close()
conn.close()
def create_connection(self):
provider = 'SQLOLEDB.1'
host = '127.0.0.1'
user = '******'
pwd = 'sql'
database = 'testdb'
connection_string = 'Provider=%s;Data Source=%s;Initial Catalog=%s;User ID=%s;Password=%s;'%(provider, host, database, user, pwd)
try:
return adodbapi.connect(connection_string)
except Exception as e:
print e
def get_stats(self):
host = self.config.get('hostname', 'localhost')
database = self.config.get('database')
# GRANT VIEW SERVER STATE TO username
username = self.config.get('username')
password = self.config.get('password')
conn_str = self._conn_string(host, username, password, database)
try:
conn = adodbapi.connect(conn_str)
except Exception:
self.exit(CRITICAL, message="Unable to connect to SQL Server for instance")
cursor = conn.cursor()
stats = {}
METRICS = [
('sqlserver.buffer.cache_hit_ratio', 'gauge', 'Buffer cache hit ratio'),
('sqlserver.buffer.page_life_expectancy', 'gauge', 'Page life expectancy'),
('sqlserver.stats.batch_requests', 'gauge', 'Batch Requests/sec'),
('sqlserver.stats.sql_compilations', 'gauge', 'SQL Compilations/sec'),
('sqlserver.stats.sql_recompilations', 'gauge', 'SQL Re-Compilations/sec'),
('sqlserver.stats.connections', 'gauge', 'User connections'),
('sqlserver.stats.lock_waits', 'gauge', 'Lock Waits/sec', '_Total'),
('sqlserver.access.page_splits', 'gauge', 'Page Splits/sec'),
('sqlserver.stats.procs_blocked', 'gauge', 'Processes Blocked'),
('sqlserver.buffer.checkpoint_pages', 'gauge', 'Checkpoint pages/sec')
]
for metric in METRICS:
# Normalize all rows to the same size for easy of use
if len(metric) == 3:
metric = metric + ('', None)
elif len(metric) == 4:
metric = metric + (None,)
mname, mtype, counter, instance_n, tag_by = metric
# For "ALL" instances, we run a separate method because we have
# to loop over multiple results and tag the metrics
try:
cursor.execute("""
select cntr_value
from sys.dm_os_performance_counters
where counter_name = ?
and instance_name = ?
""", (counter, instance_n))
(value,) = cursor.fetchone()
stats[counter] = value
except Exception:
stats[counter] = 0
return stats
def setUp(self):
try:
conn=win32com.client.Dispatch("ADODB.Connection")
except:
self.fail('SetUpError: Is MDAC installed?')
try:
conn.Open(adodbapitestconfig.connStrSQLServer)
except:
self.fail('SetUpError: Can not connect to the testdatabase, all other tests will fail...\nAdo error:%s' % conn.Errors(0))
self.conn=adodbapi.connect(adodbapitestconfig.connStrSQLServer)
self.engine = 'MSSQL'
def open_db_connections(self, instance, db_key, db_name=None):
"""
We open the db connections explicitly, so we can ensure they are open
before we use them, and are closable, once we are finished. Open db
connections keep locks on the db, presenting issues such as the SQL
Server Agent being unable to stop.
"""
conn_key = self._conn_key(instance, db_key, db_name)
timeout = int(instance.get('command_timeout', self.DEFAULT_COMMAND_TIMEOUT))
dsn, host, username, password, database, driver = self._get_access_info(instance, db_key, db_name)
custom_tags = instance.get("tags", [])
if custom_tags is None:
custom_tags = []
service_check_tags = ['host:{}'.format(host), 'db:{}'.format(database)]
service_check_tags.extend(custom_tags)
service_check_tags = list(set(service_check_tags))
cs = instance.get('connection_string', '')
cs += ';' if cs != '' else ''
try:
if self._get_connector(instance) == 'adodbapi':
cs += self._conn_string_adodbapi(db_key, instance=instance, db_name=db_name)
# autocommit: true disables implicit transaction
rawconn = adodbapi.connect(cs, {'timeout': timeout, 'autocommit': True})
else:
cs += self._conn_string_odbc(db_key, instance=instance, db_name=db_name)
rawconn = pyodbc.connect(cs, timeout=timeout)
self.service_check(self.SERVICE_CHECK_NAME, AgentCheck.OK, tags=service_check_tags)
if conn_key not in self.connections:
self.connections[conn_key] = {'conn': rawconn, 'timeout': timeout}
else:
try:
# explicitly trying to avoid leaks...
self.connections[conn_key]['conn'].close()
except Exception as e:
self.log.info("Could not close adodbapi db connection\n%s", e)
self.connections[conn_key]['conn'] = rawconn
except Exception:
cx = "{} - {}".format(host, database)
message = "Unable to connect to SQL Server for instance {}.".format(cx)
self.service_check(self.SERVICE_CHECK_NAME, AgentCheck.CRITICAL, tags=service_check_tags, message=message)
password = instance.get('password')
tracebk = traceback.format_exc()
if password is not None:
tracebk = tracebk.replace(password, "*" * 6)
cxn_failure_exp = SQLConnectionError("{} \n {}".format(message, tracebk))
raise cxn_failure_exp
def onClickedHenshuu(self):
rows = self.ui.tableWidget.currentRow()
# connect to the database
database = r"./data/Database1.mdb"
constr = 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=%s' % database
conn = adodbapi.connect(constr)
# create a cursor
cur = conn.cursor()
# extract all the data
tablename = "address"
sql = "select * from %s order by フリガナ" % tablename
cur.execute(sql)
# show the cursor
global ID
r = -1
for row in cur:
r += 1
if row.種別 == "取引先":
self.種別 = 0
elif row.種別 == "家族":
self.種別 = 1
else:
self.種別 = 2
if r == rows:
ID = row[0]
self.henshuu.ui.lineEdit_name.setText(row.氏名)
self.henshuu.ui.lineEdit_furigana.setText(row.フリガナ)
self.henshuu.ui.comboBox.setCurrentIndex(self.種別)
self.henshuu.ui.lineEdit_company.setText(row.会社名)
self.henshuu.ui.lineEdit_address.setText(row.住所)
self.henshuu.ui.lineEdit_phone.setText(row.電話番号)
self.henshuu.ui.lineEdit_mobile.setText(row.携帯)
self.henshuu.ui.lineEdit_Fax.setText(row.Fax)
self.henshuu.ui.lineEdit_Email.setText(row.Email)
try:
if r < rows:
ID2 = "{}".format(ID3)
except NameError:
myapp.error.show()
else:
self.henshuu.show()
finally:
self.SetEnabled_False()
cur.close()
conn.close()
def open_db_connections(self, instance, db_key, db_name=None):
"""
We open the db connections explicitly, so we can ensure they are open
before we use them, and are closable, once we are finished. Open db
connections keep locks on the db, presenting issues such as the SQL
Server Agent being unable to stop.
"""
conn_key = self._conn_key(instance, db_key, db_name)
timeout = int(instance.get('command_timeout',
self.DEFAULT_COMMAND_TIMEOUT))
dsn, host, username, password, database, driver = self._get_access_info(
instance, db_key, db_name)
service_check_tags = [
'host:%s' % host,
'db:%s' % database
]
try:
if self._get_connector(instance) == 'adodbapi':
cs = self._conn_string_adodbapi(db_key, instance=instance, db_name=db_name)
# autocommit: true disables implicit transaction
rawconn = adodbapi.connect(cs, {'timeout':timeout, 'autocommit':True})
else:
cs = self._conn_string_odbc(db_key, instance=instance, db_name=db_name)
rawconn = pyodbc.connect(cs, timeout=timeout)
self.service_check(self.SERVICE_CHECK_NAME, AgentCheck.OK,
tags=service_check_tags)
if conn_key not in self.connections:
self.connections[conn_key] = {'conn': rawconn, 'timeout': timeout}
else:
try:
# explicitly trying to avoid leaks...
self.connections[conn_key]['conn'].close()
except Exception as e:
self.log.info("Could not close adodbapi db connection\n{0}".format(e))
self.connections[conn_key]['conn'] = rawconn
except Exception as e:
cx = "%s - %s" % (host, database)
message = "Unable to connect to SQL Server for instance %s." % cx
self.service_check(self.SERVICE_CHECK_NAME, AgentCheck.CRITICAL,
tags=service_check_tags, message=message)
password = instance.get('password')
tracebk = traceback.format_exc()
if password is not None:
tracebk = tracebk.replace(password, "*" * 6)
cxn_failure_exp = SQLConnectionError("%s \n %s" % (message, tracebk))
raise cxn_failure_exp
def Execute(self, sql ): try: self.lock.acquire() ret = self.cur.execute(sql) self.lock.release() except: self.close() self.con = adodbapi.connect( cfg.connectionstring ) self.cur = self.con.cursor() ret = self.cur.execute(sql) self.lock.release() return ret
def connect2(self, database):
"""
Custom connect2 Method, to connect to a Microsoft SQL Server Compact DB.
:param database: Connection string to a Microsoft SQL Server Compact
:type database: String
:return: Returns a connection to the Microsoft SQL Server Compact specified in the connection string
:rtype: ADOdb connection object
"""
self._cxcn = adodbapi.connect(database)
def get_con_cur_for_db(host, user, pwd, db):
DSN = get_DSN(provider='SQLOLEDB', host=host, user=user, pwd=pwd, db=db)
try:
con = adodbapi.connect(DSN)
except Exception as e:
raise Exception("Unable to connect to MS SQL Server with "
f"database {db}; and supplied connection: "
f"host: {host}; user: {user}; pwd: {pwd}."
f"\nCaused by error: {b.ue(e)}")
cur = con.cursor()
cur.adoconn = con.adoConn ## (need to access from just the cursor)
return con, cur
def getconnectionDB():
'''
Create connection to a local database
'''
myhost = r".\SQL2014EXPRESS"
mydatabase = "MscMonteCarlo"
myuser = "******"
mypassword = "******"
connStr = """Provider=SQLOLEDB.1; User ID=%s; Password=%s;Initial Catalog=%s;Data Source= %s"""
myConnStr = connStr % (myuser, mypassword, mydatabase, myhost)
myConn = adodbapi.connect(myConnStr)
return myConn
def __init__(self):
dir =os.getcwd()
database = dir+os.sep+"datalar"+os.sep+"18022014.mdb"
#database = "D:\\aproje\\nenra\\datalar\\08022014.mdb"
constr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=%s" % database
self.tablename = "satdata"
# connect to the database
self.conn = adodbapi.connect(constr)
# create a cursor
self.cur = self.conn.cursor()
def create_db(cfg, db_name):
log.debug("Creating database: {db_name}".format(db_name=db_name))
conn = adodbapi.connect(cfg.db_connection)
cursor = conn.cursor()
cursor.execute("DROP DATABASE IF EXISTS {db_name}".format(db_name=db_name))
cursor.execute("CREATE DATABASE {db_name}".format(db_name=db_name))
query = """
GRANT ALL PRIVILEGES ON {db_name}.* to {db_user}@\'{db_host}\' IDENTIFIED BY \'{db_password}\'
""".format(db_name=db_name, db_user=cfg.db_user, db_host=cfg.db_host, db_password=cfg.db_password)
cursor.execute(query)
cursor.close()
conn.close()
def setUp(self):
try:
conn = win32com.client.Dispatch("ADODB.Connection")
except:
self.fail('SetUpError: Is MDAC installed?')
try:
conn.Open(adodbapitestconfig.connStrSQLServer)
except:
self.fail(
'SetUpError: Can not connect to the testdatabase, all other tests will fail...\nAdo error:%s'
% conn.Errors(0))
self.conn = adodbapi.connect(adodbapitestconfig.connStrSQLServer)
self.engine = 'MSSQL'
def execute_opens(connection, cfile_path):
ddf = connect(connection).cursor()
ddf.execute('exec xp_syncdb')
with open(cfile_path, mode='r', encoding='utf-8') as sql_file:
line_number = 0
for sql_line in sql_file:
ddf.execute(sql_line)
line_number += 1
if line_number % 100 == 0:
print(f'{line_number} rows executed')
ddf.connection.commit()
ddf.close()
def obter_pontos_de_venda():
c = adodbapi.connect(obter_ado_connstr(dados_conexao))
cursor = c.cursor()
cursor.execute(
"select id, nome "
"from dbo.ponto_venda "
"where ativo=1 order by nome"
)
rows = cursor.fetchall()
ret = [(x[0], x[1]) for x in rows]
c.close()
return ret
def getConnection(self, rc=0):
if self.conn is not None:
return self.conn, self.conn.cursor()
try:
conn = adodbapi.connect(self.connectionString(), autocommit=True)
self.conn = conn
except Exception as e:
if rc <= 5:
time.sleep(10 * rc**2)
conn, curs = getConnection(self, rc + 1)
else:
raise (e)
return conn, conn.cursor()
def connectToNewClarity(api='pyodbc'):
'''
Function: connectToNewClarity
Input:
api - which odbc api to use. we never used adodbapi with this database,
which is why the default here is pyodbc
Output: cnctn - connection to the new Clarity database.
Functionality: Creates a connection to the new Clarity reporting database
where "new" means after the changes rolled out in November,
2010
History:
7/11/11 - Created
7/13/11 - Removed whichdb input and decided to make two mtehods,
renaming this from connectToClarity and I will create
connectToOldClarity, too
'''
logging.debug('entering connectToNewClarity with api %s'%(api))
if api == 'pyodbc':
# make connection with pyodbc
logging.debug('pre pyoconnect')
try:
cnctn = pyoconnect('DSN=epclarity_rpt;DATABASE=Clarity;')
except Exception as myerr:
logging.error('myerr: %s'%(str(myerr)))
raise
logging.debug('post pyoconnect')
elif api == 'adodbapi':
# make connection with adodbapi. I don't think i've every been able
# to connect to New Clarity with this api, or maybe it's just on the
# VM I've had trouble...
logging.warning('Connecting to Clarity with adodbapi untested.')
try:
cnctn = connect('Data Source=epclarity_rpt;' + \
'Initial Catalog=Clarity;')
except Exception as myerr:
if not myerr:
logging.error('myerr is None')
logging.error('myerr: %s'%(str(myerr)))
raise
logging.debug('post connect with adodbapi')
else:
logging.warning('Unrecognized api, using pyodbc')
# make connection with pyodbc
cnctn = pyoconnect('DSN=epclarity_rpt;DATABASE=Clarity;')
# not sure why, but this part doesn't work with this driver
#'Trusted_Connection=true;')
return cnctn # return output
def create_repo(conninfo, hooks, temp_dir = None):
"""Creates the correct repository accessir based on certain schema properties.
This is the main entry point for accessing the DB. Use the result as a
context manager.
"""
with adodbapi.connect(conninfo) as conn:
has_tbl_identity = bool(select(conn, "select * from sys.tables where name='tbl_Identity'"))
if has_tbl_identity:
return Repository10(conninfo, hooks, temp_dir = temp_dir)
else:
raise NotImplementedError("accessing TFS 2013 has not yet been implemented.")
def __GetConnect123(self):
# 得到数据库连接信息，返回conn.cursor()
if not self.varDB:
raise (NameError, "没有设置数据库信息")
# self.conn = connect(server=self.varHost, user=self.varUser, password=self.varPassword, database=self.varDB)
self.conn = connect(
'Provider=SQLOLEDB.1;Data Source=%s;Initial Catalog=%s;UserID = %s;Password = %s;'
% (self.varHost, self.varDB, self.varUser, self.varPassword))
cur = self.conn.cursor() # 创建一个游标对象
if not cur:
raise (NameError, "连接数据库失败") # 将DBC信息赋值给cur
else:
return cur
def connect_winccoledbprovider(self):
print("Connecting to remote host '{host}' with provider '{provider}' and opening database '{database}'.".format(host=self.host, provider=self.provider_winccoledb, database=self.initial_database))
try:
self.conn_wincc = adodbapi.connect(self.conn_str_winccoledb, provider=self.provider_winccoledb, host=self.host, database=self.initial_database)
self.c_wincc = self.conn_wincc.cursor()
self.connection_established = True
#self.set_current_database()
self.connection_status_text = 'Connection to host {host} established.'.format(host=self.host)
print("Connected to database.")
except Exception as e:
print("Error when connecting to mssql host {host} with provider '{provider}' and opening database {database}. Quitting...".format(host=self.host, provider=self.provider_winccoledb, database=self.initial_database))
print(e)
self.connection_established = False
raise WinCCException(message='Connection to host {host} failed.'.format(host=self.host))
def get_dbs(host, user, pwd, default_dbs, db=None):
"""
Get dbs and the db to use. Exclude master.
NB need to use a separate connection here with db Initial Catalog
undefined.
"""
DSN = get_DSN(provider='SQLOLEDB', host=host, user=user, pwd=pwd, db='')
try:
con = adodbapi.connect(DSN)
except Exception:
raise Exception("Unable to connect to MS SQL Server with host: "
f"{host}; user: {user}; and pwd: {pwd}")
cur = con.cursor() ## must return tuples not dics
cur.adoconn = con.adoConn ## (need to access from just the cursor)
try: ## MS SQL Server 2000
cur.execute("SELECT name FROM master.dbo.sysdatabases ORDER BY name")
except Exception: ## SQL Server 2005
cur.execute("SELECT name FROM sys.databases ORDER BY name")
## only want dbs with at least one table.
all_dbs = [x[0] for x in cur.fetchall() if x[0] != 'master']
dbs = []
for db4list in all_dbs:
try:
con, cur = get_con_cur_for_db(host, user, pwd, db4list)
except Exception:
continue
if has_tbls(cur, db4list):
dbs.append(db4list)
if not dbs:
raise Exception(_("Unable to find any databases that have tables "
"and you have permission to access."))
dbs_lc = [x.lower() for x in dbs]
## Get db (default if possible otherwise first)
## NB db must be accessible from connection
if not db:
## Use default if possible, or fall back to first. NB may have no tables
default_db_mssql = default_dbs.get(mg.DBE_MS_SQL) ## might be None
if default_db_mssql:
if default_db_mssql.lower() in dbs_lc:
db = default_db_mssql
else:
db = dbs[0]
else:
if db.lower() not in dbs_lc:
raise Exception(
f'Database "{db}" not available from supplied connection')
cur.close()
con.close()
return dbs, db
def RunMultiUserTests(self):
self.RunSqlScript("..\..\Src\Cellar\Test\ut_FwCore2.sql")
#( Get a dummy connection to the database
connection = adodbapi.connect(self.DbConnect.ConnectStr)
crsr = connection.cursor()
self.RunTests()
#( Tear down the dummy connection
connection.rollback()
connection.close()
connection = None
crsr = None
def cursor(self):
from django.conf import settings
if self.connection is None:
if settings.DATABASE_NAME == '' or settings.DATABASE_USER == '':
from django.core.exceptions import ImproperlyConfigured
raise ImproperlyConfigured, "You need to specify both DATABASE_NAME and DATABASE_USER in your Django settings file."
if not settings.DATABASE_HOST:
settings.DATABASE_HOST = "127.0.0.1"
# TODO: Handle DATABASE_PORT.
conn_string = "PROVIDER=SQLOLEDB;DATA SOURCE=%s;UID=%s;PWD=%s;DATABASE=%s" % (settings.DATABASE_HOST, settings.DATABASE_USER, settings.DATABASE_PASSWORD, settings.DATABASE_NAME)
self.connection = Database.connect(conn_string)
cursor = self.connection.cursor()
if settings.DEBUG:
return util.CursorDebugWrapper(cursor, self)
return cursor
def connect(self):
"""Connect to mssql server using SQLOLEDB.1"""
try:
logging.info("Trying to connect to %s database %s", self.host, self.database)
# Python looses it's current working dir in the next instruction
# Reset after connect
curr_dir = os.getcwd()
self.conn = adodbapi.connect(self.conn_str, provider=self.provider, host=self.host, database=self.database)
os.chdir(curr_dir)
self.cursor = self.conn.cursor()
except (adodbapi.DatabaseError, adodbapi.InterfaceError) as e:
logging.error(str(e))
raise MsSQLException(message="Connection to host {host} failed.".format(host=self.host))