def __init__(self, db_path):
drv = [
x for x in pypyodbc.drivers()
if x.startswith('Microsoft Access Driver')
]
connection_string = 'Driver={};DBQ={}'.format(drv[0], db_path)
self.conn = pypyodbc.connect(connection_string, ansi=True)
self.cur = self.conn.cursor()
self.setupCategoryIds()
super(WebQueryEngineODBCWithDriver, self).__init__(
'Driver=%s;%s;' % (self.driver, connection),
username, password, database, server)
def WebQueryEngineODBCClassFactory(name, driver):
"""Create a new class for ODBC connection"""
def __init__(self, connection, username, password, database, server):
"""Late constructor for derived class"""
WebQueryEngineODBCWithDriver.__init__(self,
connection, username, password, database, server)
newclass = type(name, (WebQueryEngineODBCWithDriver, ),
{
'__init__': __init__,
'description': 'ODBC with driver %s (pypyodbc)' % driver,
'descriptor': 'odbc-driver-%s' % driver,
'driver': driver
}
)
return newclass
engine_classes = [WebQueryEngineODBC, WebQueryEngineODBCDSN]
try:
odbc_drivers = pypyodbc.drivers()
except:
odbc_drivers = []
for driver_count in range(len(odbc_drivers)):
new_engine_class = WebQueryEngineODBCClassFactory(
'WebQueryEngineODBCWithDriver_%d' % driver_count,
odbc_drivers[driver_count])
engine_classes.append(new_engine_class)
def listarDrivers():
drivers_list = pypyodbc.drivers()
for driver in drivers_list:
print(driver)
#!/usr/bin/python
import sqlite3
import pypyodbc
import pymysql
# LIST OF INSTALLED DATA SOURCES (DSNs)
print(pypyodbc.dataSources())
# LIST OF INSTALLED DRIVERS
print(pypyodbc.drivers())
#------------------------------FILE LEVEL DATABASES-------------------------------#
##############################
## SQLITE DB CONNECTION
##############################
db = sqlite3.connect('C:\\Path\\To\\Database\\File.db')
cur = db.cursor()
cur.execute("SELECT * FROM tablename")
cur.close()
db.close()
############################
## ACCESS DB CONNECTION
############################
# (USING DSN)
databasename = 'C:\\Path\\To\\Database\\File.accdb'
constr = "DSN=MS Access Database;DBQ={0};".format(databasename)
def get_mdb_driver():
mdb_driver = [
d for d in pypyodbc.drivers() if 'Microsoft Access Driver (*.mdb' in d
]
return mdb_driver[0]
super(WebQueryEngineODBCWithDriver, self).__init__(
'Driver=%s;%s;' % (self.driver, connection),
username, password, database, server)
def WebQueryEngineODBCClassFactory(name, driver):
"""Create a new class for ODBC connection"""
def __init__(self, connection, username, password, database, server):
"""Late constructor for derived class"""
WebQueryEngineODBCWithDriver.__init__(self,
connection, username, password, database, server)
newclass = type(name, (WebQueryEngineODBCWithDriver, ),
{
'__init__': __init__,
'description': 'ODBC with driver %s' % driver,
'descriptor': 'odbc-driver-%s' % driver,
'driver': driver
}
)
return newclass
engine_classes = [WebQueryEngineODBC, WebQueryEngineODBCDSN]
try:
odbc_drivers = pypyodbc.drivers()
except:
odbc_drivers = []
for driver_count in range(len(odbc_drivers)):
new_engine_class = WebQueryEngineODBCClassFactory(
'WebQueryEngineODBCWithDriver_%d' % driver_count,
odbc_drivers[driver_count])
engine_classes.append(new_engine_class)
#!/usr/bin/python
import sqlite3
import pypyodbc
import pymysql
# LIST OF INSTALLED DATA SOURCES (DSNs)
print(pypyodbc.dataSources())
# LIST OF INSTALLED DRIVERS
print(pypyodbc.drivers())
# ------------------------------FILE LEVEL DATABASES-------------------------------#
##############################
## SQLITE DB CONNECTION
##############################
db = sqlite3.connect("C:\\Path\\To\\Database\\File.db")
cur = db.cursor()
cur.execute("SELECT * FROM tablename")
cur.close()
db.close()
############################
## ACCESS DB CONNECTION
############################
# (USING DSN)
def getTeams():
teams = []
drivers = pypyodbc.drivers()
print(drivers)
if APP.config['TEST_DB']:
# In test mode, use the sqlite database
teams = Team.query.all()
for team in teams:
team.sortScores()
else:
# In production mode, get the data from the Access database
# Create the database connection
print(APP.config['DB_FILE'])
conn = pypyodbc.connect(
r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + r"Dbq=" +
APP.config['DB_FILE'] + ";")
cur = conn.cursor()
# Get the data from the database
cur.execute('''
SELECT TeamNumber, TeamName, Affiliation,
Trial1Score, Trial2Score, Trial3Score,
Trial4Score, Trial5Score, Trial6Score, Trial7Score,
ToRound4, ToRound5,
ToRound6, ToRound7,
Trial1PenaltyCount, Trial2PenaltyCount,
Trial3PenaltyCount, Trial4PenaltyCount,
Trial5PenaltyCount, Trial6PenaltyCount,
Trial7PenaltyCount
FROM ScoringSummaryQuery
''')
# Build the list of Team objects
print(cur.fetchall)
for row in cur.fetchall():
# Build the team object
team = Team(
number=row[0],
name=row[1],
affiliation=row[2],
round1=row[3],
round2=row[4],
round3=row[5],
round4=row[6],
round5=row[7],
round6=row[8],
round7=row[9],
advanceTo4=row[10],
advanceTo5=row[11],
advanceTo6=row[12],
advanceTo7=row[13],
round1Penalties=row[14],
round2Penalties=row[15],
round3Penalties=row[16],
round4Penalties=row[17],
round5Penalties=row[18],
round6Penalties=row[19],
round7Penalties=row[20],
)
# Add the current team to the list of all teams
teams.append(team)
# Close the database connection
print(teams[1].number, teams[1].bestScore, teams[1].round1)
cur.close()
conn.close()
return teams
