def _connection_factory(self): """Create a Gadfly DBI connection based on the DSN. Only local (filesystem-based) Gadfly connections are supported at this moment.""" conn_info = parseDSN(self.dsn) if conn_info['host'] != '' or conn_info['username'] != '' or \ conn_info['port'] != '' or conn_info['password'] != '': raise DatabaseAdapterError( "DSN for GadflyDA must be of the form " "dbi://dbname or dbi://dbname;dir=directory." ) connection = conn_info['dbname'] dir = os.path.join(getGadflyRoot(), conn_info['parameters'].get('dir', connection)) if not os.path.isdir(dir): raise DatabaseAdapterError('Not a directory ' + dir) if not os.path.exists(os.path.join(dir, connection + ".gfd")): db = gadfly.gadfly() db.startup(connection, dir) else: db = gadfly.gadfly(connection, dir) return db
def _connection_factory(self): """Create a Gadfly DBI connection based on the DSN. Only local (filesystem-based) Gadfly connections are supported at this moment.""" conn_info = parseDSN(self.dsn) if conn_info['host'] != '' or conn_info['username'] != '' or \ conn_info['port'] != '' or conn_info['password'] != '': raise DatabaseAdapterError( "DSN for GadflyDA must be of the form " "dbi://dbname or dbi://dbname;dir=directory.") connection = conn_info['dbname'] dir = os.path.join(getGadflyRoot(), conn_info['parameters'].get('dir', connection)) if not os.path.isdir(dir): raise DatabaseAdapterError('Not a directory ' + dir) if not os.path.exists(os.path.join(dir, connection + ".gfd")): db = gadfly.gadfly() db.startup(connection, dir) else: db = gadfly.gadfly(connection, dir) return db
def __init__(self, directory, filename): if os.path.isfile(os.path.join(directory, filename + ".gfd")): self.conn = gadfly.gadfly(filename, directory) self.crs = self.conn.cursor() else: self.conn = gadfly.gadfly() self.conn.startup(filename, directory) self.crs = self.conn.cursor() self.create_structures()
def connect(db, dbName): global DB_EXC dbDir = '%s_%s' % (db, dbName) if db == 'sqlite': try: import sqlite3 except IOError as e: try: from pysqlite2 import dbapi2 as sqlite3 except ImportError as e: return None DB_EXC = sqlite3 if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn = sqlite3.connect(os.path.join(dbDir, dbName)) elif db == 'mysql': try: import MySQLdb import _mysql_exceptions as DB_EXC except ImportError as e: return None try: cxn = MySQLdb.connect(db=dbName) except DB_EXC.OperationalError as e: cxn = MySQLdb.connect(user='******') try: cxn.query('DROP DATABASE %s' % dbName) except DB_EXC.OperationalError as e: pass cxn.query('CREATE DATABASE %s' % dbName) cxn.query("GRANT ALL ON %s.* to ''@'localhost'" % dbName) cxn.commit() cxn.close() cxn = MySQLdb.connect(db=dbName) elif db == 'gadfly': try: from gadfly import gadfly DB_EXC = gadfly except ImportError as e: return None try: cxn = gadfly(dbName, dbDir) except IOError as e: cxn = gadfly() if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn.startup(dbName, dbDir) else: return None return cxn
def connect(db): global DB_EXC dbDir = '%s_%s' % (db, DBNAME) if db == 'sqlite': try: import sqlite3 except ImportError: try: from pysqlite2 import dbapi2 as sqlite3 except ImportError: return None DB_EXC = sqlite3 if not os.path.isdir(dbDir): os.mkdir(dbDir) #cxn = sqlite3.connect(os.path.join(dbDir, DBNAME)) cxn = sqlite3.connect(":memory:") elif db == 'mysql': try: import MySQLdb import _mysql_exceptions as DB_EXC except ImportError: return None try: cxn = MySQLdb.connect(db=DBNAME) except DB_EXC.OperationalError: try: cxn = MySQLdb.connect(user=DBUSER) cxn.query('CREATE DATABASE %s' % DBNAME) cxn.commit() cxn.close() cxn = MySQLdb.connect(db=DBNAME) except DB_EXC.OperationalError: return None elif db == 'gadfly': try: from gadfly import gadfly DB_EXC = gadfly except ImportError: return None try: cxn = gadfly(DBNAME, dbDir) except IOError: cxn = gadfly() if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn.startup(DBNAME, dbDir) else: return None return cxn
def connect(db): global DB_EXC dbDir = '%s_%s' % (db, DBNAME) if db == 'sqlite': try: import sqlite3 except ImportError: try: from pysqlite2 import dbapi2 as sqlite3 except ImportError: return None DB_EXC = sqlite3 if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn = sqlite3.connect(os.path.join(dbDir, DBNAME)) elif db == 'mysql': try: import MySQLdb import _mysql_exceptions as DB_EXC except ImportError: return None try: cxn = MySQLdb.connect(db=DBNAME) except DB_EXC.OperationalError: try: cxn = MySQLdb.connect(user=DBUSER) cxn.query('CREATE DATABASE %s' % DBNAME) cxn.commit() cxn.close() cxn = MySQLdb.connect(db=DBNAME) except DB_EXC.OperationalError: return None elif db == 'gadfly': try: from gadfly import gadfly DB_EXC = gadfly except ImportError: return None try: cxn = gadfly(DBNAME, dbDir) except IOError: cxn = gadfly() if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn.startup(DBNAME, dbDir) else: return None return cxn
def open(self): connection=self.connection path=os.path dir=path.join(data_dir,connection) if not path.isdir(dir): raise self.database_error, 'invalid database error, ' + connection if not path.exists(path.join(dir,connection+".gfd")): db=gadfly.gadfly() db.startup(connection,dir) else: db=gadfly.gadfly(connection,dir) self.db=db self.opened=DateTime()
def getDatabase(self, filename): "Return the db and table name from <filename>" # assume the ini file is in the same directory as the script path = os.path.join(os.path.dirname(sys.argv[0]), filename) parser = ConfigParser.ConfigParser() parser.read(path) dbDir = parser.get('ConfigData', 'dbDir') dbName = parser.get('ConfigData', 'dbName') tableName = parser.get('ConfigData', 'tableName') try: db = gadfly.gadfly(dbName, dbDir) except IOError: db = gadfly.gadfly() db.startup(dbName, dbDir) return db, tableName
def __init__(self, ui, dbname="temp", dbdir=r"c:\temp\gadflydb"): SqlConnection.__init__(self, ui) #self.dbapi = gadfly #self._isTemporary = isTemporary self._mtime = 0.0 self._dbname = dbname self._dbdir = dbdir try: self._dbconn = gadfly.gadfly(dbname, dbdir) self._mtime = 0 self._status = self.CST_OPENED except gadfly.Error, e: self._dbconn = gadfly.gadfly() self._dbconn.startup(dbname, dbdir)
def __init__(self,ui,dbname="temp", dbdir=r"c:\temp\gadflydb"): SqlConnection.__init__(self,ui) #self.dbapi = gadfly #self._isTemporary = isTemporary self._mtime = 0.0 self._dbname = dbname self._dbdir=dbdir try: self._dbconn=gadfly.gadfly(dbname,dbdir) self._mtime = 0 self._status = self.CST_OPENED except gadfly.Error,e: self._dbconn = gadfly.gadfly() self._dbconn.startup(dbname,dbdir)
def __init__(self, wikiDocument, dataDir, tempDir, app=None): # app is unused in this gadfly implementation self.dbType = "original_gadfly" self.wikiDocument = wikiDocument self.dataDir = dataDir self.connWrap = None self.cachedWikiPageLinkTermDict = None # tempDir is ignored # Moved to DbStructure # # # Only if this is true, the database is called to commit. # # This is necessary for read-only databases # self.commitNeeded = False # dbName = self.wikiDocument.getWikiConfig().get("wiki_db", "db_filename", # u"").strip() # # if (dbName == u""): dbName = u"wikidb" try: conn = gadfly.gadfly(dbName, self.dataDir) except (IOError, OSError, ValueError), e: traceback.print_exc() raise DbReadAccessError(e)
def connect_db(database_name, database_location, create_if_does_not_exist=0, create_cursor_also=1): db = {} db['instance'] = None db['cursor'] = None try: # create a database instance db['instance'] = gadfly.gadfly(database_name, database_location) except: if create_if_does_not_exist: # create the database result = create_db(database_name, database_location, overwrite_existing_db=1, create_cursor_also=0) if result['status'] != 'success': return { 'status': 'error', 'message': "Can not connect and create a database at specified location", 'result': None } else: db = result['result'] else: return { 'status': 'error', 'message': "Can not connect to database specified", 'result': None } if create_cursor_also: result = create_cursor(db) if result['status'] != 'success': return { 'status': 'error', 'message': "Could not create cursor", 'result': db } else: db = result['result'] return { 'status': 'success', 'message': "Connected to database.", 'result': db }
def createWikiDB(wikiName, dataDir, overwrite=False): """ creates the initial db Warning: If overwrite is True, a previous file will be deleted! """ dbfile = join(dataDir, "wiki.sli") if (not exists(pathEnc(dbfile)) or overwrite): if (not exists(pathEnc(dataDir))): mkdir(pathEnc(dataDir)) else: if exists(pathEnc(dbfile)) and overwrite: unlink(pathEnc(dbfile)) # if (wikiDocument is not None): # dbName = self.wikiDocument.getWikiConfig().get("wiki_db", "db_filename", # u"").strip() # # if (dbName == u""): # dbName = u"wikidb" # else: dbName = u"wikidb" # create the database connection = gadfly.gadfly() connection.startup(dbName, dataDir) connwrap = ConnectWrap(connection) try: for tn in MAIN_TABLES: changeTableSchema(connwrap, tn, TABLE_DEFINITIONS[tn]) for key, value in ( ("formatver", str(VERSION_DB)), # Version of database format the data was written ("writecompatver", str(VERSION_WRITECOMPAT)), # Lowest format version which is write compatible ("readcompatver", str(VERSION_READCOMPAT)), # Lowest format version which is read compatible ("branchtag", "WikidPad"), # Tag of the WikidPad branch ("locale", "-") # Locale for cached wordnormcase column. '-': column invalid ): setSettingsValue(connwrap, key, value) # connwrap.executemany("insert or replace into settings(key, value) "+ # "values (?, ?)", ( # ("formatver", "0"), # Version of database format the data was written # ("writecompatver", "0"), # Lowest format version which is write compatible # ("readcompatver", "0"), # Lowest format version which is read compatible # ("branchtag", "WikidPad") # Tag of the WikidPad branch # ) ) rebuildIndices(connwrap) connwrap.commit() finally: # close the connection connwrap.close() else: raise WikiDBExistsException( _(u"database already exists at location: %s") % dataDir)
def reconnectDb(self): try: self.connWrap.close() conn = gadfly.gadfly("wikidb", self.dataDir) self.connWrap = DbStructure.ConnectWrap(conn) except (IOError, OSError, ValueError), e: traceback.print_exc() raise DbReadAccessError(e)
def startDB(self): import gadfly conn = gadfly.gadfly() conn.startup(self.DB_NAME, self.DB_DIR) cursor = conn.cursor() cursor.execute("create table x (x integer)") conn.commit() conn.close()
def startDB(self): import gadfly conn = gadfly.gadfly() conn.startup(self.DB_NAME, self.DB_DIR) # gadfly seems to want us to create something to get the db going cursor = conn.cursor() cursor.execute("create table x (x integer)") conn.commit() conn.close()
def createWikiDB(wikiName, dataDir, overwrite=False): """ creates the initial db Warning: If overwrite is True, a previous file will be deleted! """ dbfile = join(dataDir, "wiki.sli") if (not exists(pathEnc(dbfile)) or overwrite): if (not exists(pathEnc(dataDir))): mkdir(pathEnc(dataDir)) else: if exists(pathEnc(dbfile)) and overwrite: unlink(pathEnc(dbfile)) # create the database connection = gadfly.gadfly() connection.startup("wikidb", dataDir) connwrap = ConnectWrap(connection) try: for tn in MAIN_TABLES: changeTableSchema(connwrap, tn, TABLE_DEFINITIONS[tn]) for key, value in ( ("formatver", str(VERSION_DB) ), # Version of database format the data was written ("writecompatver", str(VERSION_WRITECOMPAT) ), # Lowest format version which is write compatible ("readcompatver", str(VERSION_READCOMPAT) ), # Lowest format version which is read compatible ("branchtag", "WikidPad"), # Tag of the WikidPad branch ( "locale", "-" ) # Locale for cached wordnormcase column. '-': column invalid ): setSettingsValue(connwrap, key, value) # connwrap.executemany("insert or replace into settings(key, value) "+ # "values (?, ?)", ( # ("formatver", "0"), # Version of database format the data was written # ("writecompatver", "0"), # Lowest format version which is write compatible # ("readcompatver", "0"), # Lowest format version which is read compatible # ("branchtag", "WikidPad") # Tag of the WikidPad branch # ) ) rebuildIndices(connwrap) connwrap.commit() finally: # close the connection connwrap.close() else: raise WikiDBExistsException( _(u"database already exists at location: %s") % dataDir)
def _gf_init(self): if self._type == 'auto': # set to use if exists if self.exists(): self._type = 'use' else: self._type = 'create' if self._type == 'create': self._set_dirs() self._gf_handle = gadfly() self._startup() self.create_gfplus_wrapper() print 'db opened with \'create\'' elif self._type == 'use': self._gf_handle = gadfly(self._db_name,self._dir_name) print 'db opened with \'use\'' else: raise Exception('usage argv[1] = create|use') self._get_cursor()
def createDB(self): """Close, clear, and re-create the database""" self.close() a = self.address from gadfly import gadfly g = gadfly() g.startup(self.address.db, self.address.dir) g.commit() g.close()
def __init__(self, connection): "Setup the database connection" self._system_tables=['__TABLE_NAMES__', '__INDEXCOLS__', '__COLUMNS__', '__INDICES__', '__DATADEFS__', 'DUAL'] # Not providing a db name is guaranteed to ruin our connection if not connection['databasename']: raise ValueError self._db = gadfly.gadfly( databasename=connection['databasename'] ,directory=connection['directory'] ) self._cursor=self._db.cursor() # This one is used in getRow self._tableName=''
def __init__(self, dbname="tmp", directory="/tmp", encoding="iso-8859-1"): """ The default values will create a database called tmp in your /tmp directory. Gadfly will create a number of files called dbname.* there. """ orm2.datasource.datasource_base.__init__(self) self._conn = gadfly() self._conn.startup(dbname, directory) self.encoding = encoding self._update_cursor = self.cursor()
def do_query(query): conn = gadfly.gadfly("db0", "DBS") cursor = conn.cursor() pwd = md5.new(query['pwd'][0]).hexdigest() q = query['query'][0] sel = ("""select user,month,year,product,qty,amount from purchases where pwd='%s' and month>=%s """ % (pwd, q)) cursor.execute(sel) rows = [] for x in cursor.fetchall(): rows.append(row % x) results = table % ('\n'.join(rows)) conn.close() return results
def connect(self, host=None, port=None, user='', password='', database='default'): gadfly = self.get_import() directory = os.path.dirname(database) database = os.path.basename(database) if database.endswith('.gfd'): database = database[:-4] try: g = gadfly.gadfly() g.startup(database, directory) return GadflyDB(g, self) except IOError: raise Error(sys.exc_info()[1])
def createDB(self): """Create Gadfly SQL database with ebuilds table""" self.connection = gadfly.gadfly() loc = os.path.expanduser('~/.abeni/bugz') self.connection.startup("bugzDB", loc) self.cursor = self.connection.cursor() cmd = "create table ebuilds (\ p VARCHAR, \ package VARCHAR, \ cat VARCHAR, \ bug VARCHAR, \ bzstatus VARCHAR, \ bzresolution VARCHAR, \ notes VARCHAR, \ mine INTEGER, \ abenistatus VARCHAR \ )" self.cursor.execute(cmd) self.connection.commit()
def retest(directory): print "*" * 30 print "*** reconnect test" from gadfly import gadfly connect = gadfly("test", directory) cursor = connect.cursor() for s in updates: print; print print s if s in trace_updates: cursor.EVAL_DUMP = 1 cursor.execute(s) cursor.EVAL_DUMP = 0 print cursor.pp() #print; print "CONNECTION DATA BEFORE COMMIT" #connect.DUMP_ALL() connect.commit() #print; print "CONNECTION DATA AFTER COMMIT" #connect.DUMP_ALL() connect.close() return connect
def runfile(file, dbname, dbdir): print "running sql commands ", dbname, dbdir from string import split, join from gadfly import gadfly connect = gadfly(dbname, dbdir) cursor = connect.cursor() data = file.read() from string import split, strip commands = split(data, ";") for c in commands: if not strip(c): continue print "COMMAND:" data = str(c) pdata = " " + join(split(c, "\n"), "\n ") print pdata print cursor.execute(data) print cursor.pp() print #connect.commit() connect.close()
def connect(self, sqlitepath=None): database = self.prefs.get("DATABASE").lower() if database == 'mysql' and HAS_MYSQL: dbname = self.prefs.get('DATA_NAME') server = self.prefs.get('MYSQL_HOST') serverport = self.prefs.get('MYSQL_PORT') username = self.prefs.get('MYSQL_USERNAME') password = self.prefs.get('MYSQL_PASSWORD') if serverport: self.db = MySQLdb.connect(db='', host=server, user=username, passwd=password, port=serverport) else: self.db = MySQLdb.connect(db='', host=server, user=username, passwd=password) elif database in ('sqlite', 'sqllite', 'pysqlite') and HAS_SQLITE: if not sqlitepath: dataname = self.prefs.get('DATA_NAME') datadir = self.prefs.get('DATA_DIR') dbname = self.prefs.get('SQLITE_DB') sqlitepath = os.path.join(datadir, dataname, dbname) #print os.path.abspath(sqlitepath) return sqlite.connect(sqlitepath, 774) elif database == 'gadfly' and HAS_GADFLY: return gadfly.gadfly() else: print "DATABASE must be set to sqlite or mysql in your config file" print "In addtion, you must install the appropriate database software" print "and Python module.\n" print "Please visit http://scratchy.sourceforge.net for more info" sys.exit(1)
def connect(prefs, sqlitepath=None): database = prefs.get('DATABASE').lower() if database == 'mysql': if not HAS_MYSQL: error("The MySQL python module is not installed") server = prefs.get('MYSQL_HOST') serverport = prefs.get('MYSQL_PORT') username = prefs.get('MYSQL_USERNAME') password = prefs.get('MYSQL_PASSWORD') dbname = prefs.get('DATA_NAME') if serverport: return MySQLdb.connect(db=dbname, host=server, user=username, passwd=password, port=serverport, cursorclass=MySQLdb.cursors.DictCursor) else: return MySQLdb.connect(db=dbname, host=server, user=username, passwd=password, cursorclass=MySQLdb.cursors.DictCursor) elif database in ('pysqlite', 'sqlite', 'sqllite'): if not HAS_PYSQLITE: error("The pysqlite module is not installed") if not sqlitepath: dataname = prefs.get('DATA_NAME') datadir = prefs.get('DATA_DIR') dbname = prefs.get('SQLITE_DB') sqlitepath = os.path.join(datadir, dataname, dbname) return sqlite.connect(sqlitepath, 774) elif database == 'gadfly': if not HAS_GADFLY: error("The gadfly module is not installed") dataname = prefs.get('DATA_NAME') datadir = prefs.get('DATA_DIR') return gadfly.gadfly(dataname, os.path.join(datadir, dataname)) else: error("Unknown DATABASE specified in config file. Currently, only mysql and pysqlite are supported")
"""Demonstration of the Remote View protocol for adding specially implemented Views in an application.""" from gadfly import gadfly # create the database g = gadfly() g.startup("dbtest", "dbtest") # assume directory "dbtest" exists # define a remote view class import gfintrospect class myTable(gfintrospect.RemoteView): """A remote view must define self.column_names to return a (fixed) list of string column names and self.listing() to return a possibly varying list of row values. If there is a single column the listing() list must return a list of values, but for multiple columns it must return a list of tuples with one entry for each column. The remote view implementation may optionally redefine __init__ also, please see gfintrospect.py """ # static: don't reconstruct internal structure for each query # for more interesting views static will generally be 0 static = 1 def __init__(self, column_names=None, rowlist=None): """do whatever needed for initialization"""
from gadfly import gadfly import os, shutil connect = gadfly() if os.path.exists('_finances'): shutil.rmtree('_finances') os.makedirs('_finances') ''' tran tran_date datetime tran_type_id integer cparty_id integer ccy_id integer tran_type id integer name varchar desc varchar cparty id integer name varchar type varchar ccy id integer name integer '''
pass cxn.query('CREATE DATABASE %s' % dbName) cxn.query("GRANT ALL ON %s.* to ''@'localhost'" % dbName) cxn.commit() cxn.close() cxn = MySQLdb.connect(db=dbName) elif db == 'gadfly': try: from gadfly import gadfly DB_EXC = gadfly except ImportError, e: return None try: cxn = gadfly(dbName, dbDir) except IOError, e: cxn = gadfly() if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn.startup(dbName, dbDir) else: return None return cxn def create(cur): try: cur.execute(''' CREATE TABLE users ( login VARCHAR(8),
#! /usr/bin/env python # -*- coding: Latin-1 -*- # Création et Alimentation d'une petite base de données import gadfly import os chemin = os.getcwd() # obtention du répertoire courant connex = gadfly.gadfly() connex.startup("musique", chemin) cur = connex.cursor() requete = "create table compositeurs (comp varchar, a_naiss integer,\ a_mort integer)" cur.execute(requete) requete = "create table oeuvres (comp varchar, titre varchar,\ duree integer, interpr varchar)" cur.execute(requete) print "Entrée des enregistrements, table des compositeurs :" while 1: nm = raw_input("Nom du compositeur (<Enter> pour terminer) : ") if nm =='': break an = raw_input("Année de naissance : ") am = raw_input("Année de mort : ") requete ="insert into compositeurs(comp, a_naiss, a_mort) values \ ('%s', %s, %s)" % (nm, an, am) cur.execute(requete) # Affichage des données entrées, pour vérification :
def test(directory): print "testing" from gadfly import gadfly connect = gadfly() connect.startup("test", directory) curs = connect.cursor() print print "TABLE CREATES" for x in table_creates: print x curs.execute(x) curs.execute("create table empty (nothing varchar)") C = """ CREATE TABLE work ( name VARCHAR, hours INTEGER, rate FLOAT) """ print C curs.execute(C) print C = """ CREATE TABLE accesses ( page VARCHAR, hits INTEGER, month INTEGER) """ print C curs.execute(C) print print "INSERTS" C = """ INSERT INTO work(name, hours, rate) VALUES (?, ?, ?) """ D = [ ("sam", 30, 40.2), ("norm", 45, 10.2), ("woody", 80, 5.4), ("diane", 3, 4.4), ("rebecca", 120, 12.9), ("cliff", 26, 200.00), ("carla", 9, 3.5), ] for x in D: print x curs.execute(C, D) C = "create unique index wname on work(name)" print "Unique index:", C curs.execute(C) print "trying bad insert into unique field" C = "insert into work(name, hours, rate) values ('sam', 0, 0)" import sys try: curs.execute(C) except: print "exception as expected %s(%s)" % (sys.exc_type, sys.exc_value) else: raise GadflyError, "unique index permits nonunique field" print print "testing introspection" itests = [ "select 10*4 from dual", "select * from __table_names__", #"select * from __datadefs__", # needs formatting "select * from __indices__", "select * from __columns__", "select * from __indexcols__", """ select i.index_name, is_unique, table_name, column_name from __indexcols__ c, __indices__ i where c.index_name = i.index_name""", ] for C in itests: print C print curs.execute(C) print curs.pp() print print "testing complex, neg literals in insert" curs.execute( "insert into work(name, hours, rate) values ('jo', -1, 3.1e-44-1e26j)") curs.execute("select * from work") print curs.pp() print "deleting jo" print curs.execute("delete from work where name='jo'") C = """ INSERT INTO accesses(page, month, hits) VALUES (?, ?, ?) """ D = [ ("index.html", 1, 2100), ("index.html", 2, 3300), ("index.html", 3, 1950), ("products.html", 1, 15), ("products.html", 2, 650), ("products.html", 3, 98), ("people.html", 1, 439), ("people.html", 2, 12), ("people.html", 3, 665), ] for x in D: print x curs.execute(C, D) for (table, stuff) in dpairs: ins = "insert into %s values (?, ?, ?)" % table if table != "frequents": for parameters in dataseq(stuff): print "singleinsert", table, parameters curs.execute(ins, parameters) else: print print "multiinsert", table parameters = dataseq(stuff) for p in parameters: print p print "multiinsert..." curs.execute(ins, parameters) print print print print "INDICES" for ci in indices: print ci curs.execute(ci) print print "QUERIES" for x in workqueries: print print print x curs.execute(x) print curs.pp() statement = """select name, hours from work""" curs.execute(statement) print "Hours worked this week" print for (name, hours) in curs.fetchall(): print "worker", name, "worked", hours, "hours" print print "end of work report" #return for x in queries: print print print x curs.execute(x) #for x in curs.commands: # print x all = curs.fetchall() if not all: print "empty!" else: print curs.pp() #for t in all: #print t #return print print "DYNAMIC QUERIES" for (x, y) in dynamic_queries: print print print x print "dynamic=", y curs.execute(x, y) #for x in curs.commands: # print x all = curs.fetchall() if not all: print "empty!" else: for t in all: print t print "repeat test" from time import time for x in repeats: print "repeating", x now = time() curs.execute(x) print time() - now, "first time" now = time() curs.execute(x) print time() - now, "second time" now = time() curs.execute(x) print time() - now, "third time" print "*** committing work" connect.commit() connect.close() print print return connect
def rollbacktest(directory): print "*" * 30 print "*** recovery test ***" print print print import sys from gadfly import gadfly print "*** connecting" connect = gadfly("test", directory) cursor = connect.cursor() connect.autocheckpoint = 0 print "*** executing updates to commit" for x in keep_updates: print x cursor.execute(x) connect.verbose = 1 print "*** COMMITTING OPERATIONS (connection set to verbose)" connect.commit() print "*** DUMP LOG" connect.dumplog() print print "*** RUNNING OPS TO ROLL BACK" preresults = [] for s in rollback_queries: print print print s try: cursor.execute(s) preresults.append(cursor.fetchall()) print cursor.pp() except: d = sys.exc_type print "exception", d preresults.append(d) print print "*** now updating with ops to rollback" for s in rollback_updates: print print print s cursor.execute(s) print print print "*** testing noncommitted results" for dummy in (1, 2): postresults = [] for s in rollback_queries: print s try: cursor.execute(s) postresults.append(cursor.fetchall()) print cursor.pp() except: d = sys.exc_type print "*** exception", d postresults.append(d) if preresults == postresults: print "*** same results as before uncommitted updates" else: print "*** differing results from before uncommitted updates" if dummy == 1: print print "*** ROLLING BACK!" connect.rollback() print print "*** EMULATING RECOVERY" for s in rollback_updates: print print print s cursor.execute(s) for dummy in (1, 2): postresults = [] for s in rollback_queries: print s try: cursor.execute(s) postresults.append(cursor.fetchall()) print cursor.pp() except: d = sys.exc_type print "*** exception", d postresults.append(d) if preresults == postresults: print "*** same results as before uncommitted updates" else: print "*** differing results from before uncommitted updates" if dummy == 1: print "*** RESTART: DUMPLOG" connect.dumplog() print "*** RESTARTING (RECOVER FROM LOG, DISCARD UNCOMMITTED)" connect.restart()
def writeOut(self): for table in self.data.tables: directory = self.data.tables[table]["directory"] directory = directory[:-1] path = self.rootDir + "/" + directory print(path) try: os.mkdir(path) except OSError: print("Directory Exists") print(directory, path) try: self.connection.startup(directory, path) self.cursor = self.connection.cursor() except AttributeError: self.connection = gadfly.gadfly() self.connection.startup(directory, path) self.cursor = self.connection.cursor() for x in self.data.tables[table]: if x != "directory": print(self.data.tables[table][x]) name = self.data.tables[table][x]["name"] name = name[:-1] # removes "\n" of string print(name) data = self.data.tables[table][x]["data"] headings = [data[0][:-1], data[1][:-1]] headings[0] = headings[0].split(", ") headings[1] = headings[1].split(", ") print(headings) data = data[2:] # gets rid of headings and field types field_string = "" for each in range(len(headings[0])): if each+1 == len(headings[0]): field_string = field_string + "%s %s" %(headings[0][each], headings[1][each]) else: field_string = field_string + "%s %s, " %(headings[0][each], headings[1][each]) sql_string = "create table %s (%s)" %(name, field_string) print(sql_string) self.cursor.execute(sql_string) field_string = "" for each in headings[0]: if each == headings[0][0]: field_string = field_string + each else: field_string = field_string + ", " + each print(field_string) for line in data: line = line[:-1] line = line.split(", ") print(line) data_string = "" for point in range(len(line)): if point == 0: data_string = data_string + "'%s'" % line[point] else: data_string = data_string + ", " + "'%s'" % line[point] print(data_string) sql_string = "insert into %s(%s) values (%s)" % (name, field_string, data_string) print(sql_string) self.connection.commit() self.connection = None print("Managed")
#!/usr/bin/python # test for Gadfly import os import gadfly import time DBdir = 'cameraDB' DBname = 'cameraDB' if os.path.exists(DBdir): print 'Database already exists. I will just open it' connection = gadfly.gadfly(DBname, DBdir) cursor = connection.cursor() cursor.execute("SELECT * FROM camera") for x in cursor.fetchall(): print x[0] + " " + x[2] + " " + x[1] cursor.execute("SELECT COUNT(DISTINCT cmake) FROM camera") for x in cursor.fetchall(): print "Distinct make: " + str(x[0]) cursor.execute("SELECT COUNT(*) FROM camera") for x in cursor.fetchall(): totalMake = x[0] cursor.execute("SELECT COUNT(cmake) FROM camera WHERE cmake = 'Unknown'") for x in cursor.fetchall(): unknownMake = x[0] print "Items: + " + str(totalMake) + " including " + str(unknownMake) + " unknown" else: print 'Database not present. I will stop here' connection.commit()
def connect(db, DBNAME=DBNAME): global DB_EXC dbDir = '%s_%s' % (db, DBNAME) printf(dbDir) if db == 'sqlite': try: import sqlite3 except ImportError: try: from pysqlite2 import dbapi2 as sqlite3 except ImportError: return None DB_EXC = sqlite3 if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn = sqlite3.connect(os.path.join(dbDir, DBNAME)) elif db == 'mysql': try: import MySQLdb import _mysql_exceptions as DB_EXC try: cxn = MySQLdb.connect(db=DBNAME) except DB_EXC.OperationalError: try: cxn = MySQLdb.connect(user=DBUSER) cxn.query('DROP DATABASE %s' % DBNAME) cxn.query('CREATE DATABASE %s' % DBNAME) cxn.commit() cxn.close() except DB_EXC.OperationalError: raise RuntimeError try: cxn = MySQLdb.connect("localhost",DBUSER,"",DBNAME) except DB_EXC.OperationalError: return None except ImportError: try: import mysql.connector import mysql.connector.errors as DB_EXC try: cxn = mysql.connector.Connect(**{ 'database': DBNAME, 'user': DBUSER, }) except DB_EXC.InterfaceError: return None except ImportError: return None elif db == 'gadfly': try: from gadfly import gadfly DB_EXC = gadfly except ImportError: return None try: cxn = gadfly(DBNAME, dbDir) except IOError: cxn = gadfly() if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn.startup(DBNAME, dbDir) else: return None print("asdasdds") return cxn
def __init__(self, dbpath, dbname="results"): "Creates interface to a new or existing database." import os, time, sys from ats.configuration import SYS_TYPE if dbpath == None: print("No database name supplied.") sys.exit(1) self.__dbname = dbname self.__dbpath = dbpath self.__connectionOpen = False self.__usingExistingDatabase = None "Error log" self.errors = [] try: self.__connection = gadfly.gadfly(self.__dbname, self.__dbpath) print("Selected database '%s'" % (self.__dbpath)) self.__usingExistingDatabase = True #If no database supplied, create one. except IOError: if (os.path.isdir(self.__dbpath) == False): os.mkdir(self.__dbpath) self.__connection = gadfly.gadfly() self.__connection.startup(self.__dbname, self.__dbpath) cursor = self.__connection.cursor() #Note - primary keys and foreign keys not supported by gadfly. #Using indexes to get same effect as primary keys when needed. cursor.execute("CREATE TABLE tests \ (name varchar, \ startDateTime integer, \ endDateTime integer, \ system varchar, \ path varchar, \ status varchar, \ np integer, \ mesh varchar)") cursor.execute( "CREATE UNIQUE INDEX tests_pkeys on tests(name, startDateTime, system)" ) cursor.execute("CREATE TABLE features \ (name varchar, \ mesh varchar, \ tier varchar )") cursor.execute( "CREATE UNIQUE INDEX features_pkeys on features(name, mesh)") cursor.execute("CREATE TABLE loggedFeatures \ (test varchar, \ feature varchar, \ mesh varchar)") self.__connection.commit() print("Database " + self.__dbpath + " created.") self.__usingExistingDatabase = False self.__connectionOpen = True
pass cxn.query('CREATE DATABASE %s' % dbname) cxn.query("GRANT ALL ON %s.* to ''@'localhost'" % dbname) cxn.commit() cxn.close() cxn = MySqldb.connect(db=dbname) elif db == 'gadfly': try: from gadfly import gadfly DB_EXC = gadfly except ImportError,e: return None try: cxn=gadfly(dbname,dbDir) except IOError,e: cxn=gadfly() if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn.startup(dbname,dbDir) else: return None return cxn def create(cur): try: cur.execute(''' CREATE TABLE users( login VARCHAR(8), uid INTEGER, prid INTEGER)
pass cxn.query('CREATE DATABASE %s' % dbName) #cxn.query("GRANT ALL ON %s.* to ''@'localhost'" % dbName) cxn.commit() cxn.close() cxn = MySQLdb.connect(db=dbName) elif db == 'gadfly': try: from gadfly import gadfly DB_EXC = gadfly except ImportError, e: return None try: cxn = gadfly(dbName, dbDir) except IOError, e: cxn = gadfly() if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn.startup(dbName, dbDir) else: return None return cxn def create(cur): try: cur.execute(''' CREATE TABLE users ( login VARCHAR(8), uid INTEGER,
# Populate the database for the SQL injection demo, # it creates a DBS directory in the current working dir. # Requires Gadfly on the python path, and a --allworkingmodules --oldstyle # pypy-c. # Passwords for the demo are just the reverse of user names. import md5 import sys, os import random os.mkdir("DBS") import gadfly conn = gadfly.gadfly() conn.startup("db0", "DBS") names = ['bob', 'jamie', 'david', 'monica', 'rose', 'anna'] def make_pwd(name): rname = list(name) rname.reverse() rname = ''.join(rname) return md5.new(rname).hexdigest() pwds = [make_pwd(name) for name in names] products = [('superglue', 10.0, 5), ('pink wallpaper', 25.0, 20), ('red wallpaper', 20.0, 20), ('gray wallpaper', 15.0, 20),
def ConnectDB(self): """Connect to database""" loc = os.path.expanduser('~/.abeni/bugz') self.connection = gadfly.gadfly("bugzDB", loc) self.cursor = self.connection.cursor()
def test(directory): print "testing" from gadfly import gadfly connect = gadfly() connect.startup("test", directory) curs = connect.cursor() print print "TABLE CREATES" for x in table_creates: print x curs.execute(x) curs.execute("create table empty (nothing varchar)") C = """ CREATE TABLE work ( name VARCHAR, hours INTEGER, rate FLOAT) """ print C curs.execute(C) print C = """ CREATE TABLE accesses ( page VARCHAR, hits INTEGER, month INTEGER) """ print C curs.execute(C) print print "INSERTS" C = """ INSERT INTO work(name, hours, rate) VALUES (?, ?, ?) """ D = [ ("sam", 30, 40.2), ("norm", 45, 10.2), ("woody", 80, 5.4), ("diane", 3, 4.4), ("rebecca", 120, 12.9), ("cliff", 26, 200.00), ("carla", 9, 3.5), ] for x in D: print x curs.execute(C, D) C = "create unique index wname on work(name)" print "Unique index:", C curs.execute(C) print "trying bad insert into unique field" C = "insert into work(name, hours, rate) values ('sam', 0, 0)" import sys try: curs.execute(C) except: print "exception as expected %s(%s)" %(sys.exc_type, sys.exc_value) else: raise "stop!", "unique index permits nonunique field" print; print "testing introspection" itests = ["select 10*4 from dual", "select * from __table_names__", #"select * from __datadefs__", # needs formatting "select * from __indices__", "select * from __columns__", "select * from __indexcols__", """ select i.index_name, is_unique, table_name, column_name from __indexcols__ c, __indices__ i where c.index_name = i.index_name""", ] for C in itests: print C print curs.execute(C) print curs.pp() print print "testing complex, neg literals in insert" curs.execute("insert into work(name, hours, rate) values ('jo', -1, 3.1e-44-1e26j)") curs.execute("select * from work") print curs.pp() print "deleting jo"; print curs.execute("delete from work where name='jo'") C = """ INSERT INTO accesses(page, month, hits) VALUES (?, ?, ?) """ D = [ ("index.html", 1, 2100), ("index.html", 2, 3300), ("index.html", 3, 1950), ("products.html", 1, 15), ("products.html", 2, 650), ("products.html", 3, 98), ("people.html", 1, 439), ("people.html", 2, 12), ("people.html", 3, 665), ] for x in D: print x curs.execute(C, D) for (table, stuff) in dpairs: ins = "insert into %s values (?, ?, ?)" % table if table!="frequents": for parameters in dataseq(stuff): print "singleinsert", table, parameters curs.execute(ins, parameters) else: print print "multiinsert", table parameters = dataseq(stuff) for p in parameters: print p print "multiinsert..." curs.execute(ins, parameters) print;print print print "INDICES" for ci in indices: print ci curs.execute(ci) print print "QUERIES" for x in workqueries: print;print print x curs.execute(x) print curs.pp() statement = """select name, hours from work""" curs.execute(statement) print "Hours worked this week" print for (name,hours) in curs.fetchall(): print "worker", name, "worked", hours, "hours" print print "end of work report" #return for x in queries: print; print print x curs.execute(x) #for x in curs.commands: # print x all = curs.fetchall() if not all: print "empty!" else: print curs.pp() #for t in all: #print t #return print print "DYNAMIC QUERIES" for (x,y) in dynamic_queries: print; print print x print "dynamic=", y curs.execute(x, y) #for x in curs.commands: # print x all = curs.fetchall() if not all: print "empty!" else: for t in all: print t print "repeat test" from time import time for x in repeats: print "repeating", x now = time() curs.execute(x) print time()-now, "first time" now = time() curs.execute(x) print time()-now, "second time" now = time() curs.execute(x) print time()-now, "third time" print "*** committing work" connect.commit() connect.close() print; print return connect
try: return self.__results[self.__col_map[key]] except: return notfound def __repr__(self): return self.__col_map.keys() def __str__(self): return str(self.__col_map.keys()) if __name__ == '__main__': import gadfly conn = gadfly.gadfly("phil", "../data/phil") #cursor = conn.cursor() cursor = DictCursor(conn) sql = "SELECT * FROM log" cursor.execute(sql) rs = cursor.fetchone() print "---------------------------------" print rs.get('timestamp') print rs['timestamp'] print "---------------------------------" rows = cursor.fetchmany(3) for row in rows:
def connect(db, DBNAME): global DB_EXC dbDir = '%s_%s' % (db, DBNAME) # 调用SQLite if db == 'sqlite': try: import sqlite3 except ImportError: try: from pysqlite2 import dbapi2 as sqlite3 except ImportError: return None DB_EXC = sqlite3 if not os.path.isdir(dbDir): os.makedirs(dbDir) cxn = sqlite3.connect(os.path.join(dbDir, DBNAME)) # 调用MySQL elif db == 'mysql': try: import MySQLdb import _mysql_exceptions as DB_EXC try: cxn = MySQLdb.connect(db=DBNAME) except DB_EXC.OperationalError: try: cxn = MySQLdb.connect(user=DBUSER, passwd='a12345678') cxn.query('create database %s' % DBNAME) cxn.commit() cxn.close() cxn = MySQLdb.connect(db=DBNAME) except DB_EXC.OperationalError: return None except ImportError: try: #import mysql.connector as my_sql #import mysql.connector.errors as DB_EXC import pymysql as my_sql import pymysql.err as DB_EXC try: cxn = my_sql.connect( **{ 'database': DBNAME, 'user': DBUSER, 'password': '******' }) except DB_EXC.InterfaceError: return None except ImportError: return None #调用Gadfly,暂时没有安装gadfly库 elif db == 'gadfly': try: from gadfly import gadfly DB_EXC = gadfly except ImportError: return None try: cxn = gadfly(DBNAME, dbDir) except IOError: cxn = gadfly() if not os.path.isdir(dbDir): os.makedirs(dbDir) cxn.startup(DBNAME, dbDir) else: return None return cxn
def connect(db): global DB_EXC dbDir = '%s_%s' % (db, DBNAME) if db == 'sqlite': try: import sqlite3 except ImportError: try: from pysqlite2 import dbapi2 as sqlite3 except ImportError: return None DB_EXC = sqlite3 if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn = sqlite3.connect(os.path.join(dbDir, DBNAME)) elif db == 'mysql': try: import pymysql # import _mysql_exceptions as DB_EXC # Python 3 无此函数 except ImportError: return None try: cxn = pymysql.connect(host=DBHOST, port=DBPORT,user=DBUSER, password = DBPASSWORD) except ConnectionError: return None '''查看数据库,检查是否有 test 库,有则直接连接,没有则创建库''' cur = cxn.cursor() cur.execute("show databases;") databases = str(cur.fetchall()) # cur.close() strdbname = "'" + DBNAME + "'" if not strdbname in databases: cxn.query('CREATE DATABASE %s' % DBNAME) cxn.commit() cxn.close() try: cxn = pymysql.connect(host=DBHOST, port=DBPORT, user=DBUSER, password=DBPASSWORD, db=DBNAME) except ConnectionError: return None elif db == 'gadfly': try: from gadfly import gadfly DB_EXC = gadfly except ImportError: return None try: cxn = gadfly(DBNAME, dbDir) except IOError: cxn = gadfly() if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn.startup(DBNAME, dbDir) else: return None return cxn
def __init__(self, StoreObject, root): self.connection = gadfly.gadfly() self.rootDir = root os.chdir(self.rootDir) self.data = StoreObject self.writeOut()
def connect(db, DBNAME): global DB_EXC dbDir = '%s_%s' % (db, DBNAME) if db == 'sqlite': try: import sqlite3 except ImportError: try: from pysqlite2 import dbapi2 as sqlite3 except ImportError: return None DB_EXC = sqlite3 if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn = sqlite3.connect(os.path.join(dbDir, DBNAME)) elif db == 'mysql': try: import MySQLdb import _mysql_exceptions as DB_EXC try: cxn = MySQLdb.connect(db=dbname) except DB_EXC.OperationalError: try: cxn = MySQLdb.connect(user=DBUSER) cxn.query('CREATE DATABASE %s' % DBNAME) cxn.commit() cxn.close() cxn = MySQLdb.connect(db=DBNAME) except DB_EXC.OperationalError: return None except ImportError: try: import mysql.connector import mysql.connector.errors as DB_EXC try: cxn = mysql.connector.Connect(**{ 'database': DBNAME, 'user': DBUSER, 'password': PASSWORD, }) except DB_EXC.InterfaceError: return None except ImportError: return None elif db == 'gadfly': try: from gadfly import gadfly DB_EXC = gadfly except ImportError: return None try: cxn = gadfly(DBNAME, dbDir) except IOError: cxn = gadfly() if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn.startup(DBNAME, dbDir) else: return None return cxn
def _db_init(self): self._db_handle = gadfly() self._set_dirs() self._startup() self._get_cursor()