Menu
▾
▴
[Pythoncard-cvs] PythonCard/samples/dbBrowser pysqlite2Browse.py,NONE,1.1 dbLogin.py,1.11,1.12 dbLogin.rsrc.py,1.15,1.16 readme.txt,1.14,1.15
|
From: Alex T. <ale...@us...> - 2005-05-17 20:34:04
|
Update of /cvsroot/pythoncard/PythonCard/samples/dbBrowser In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv16079 Modified Files: dbLogin.py dbLogin.rsrc.py readme.txt Added Files: pysqlite2Browse.py Log Message: Added support for the new version of pySQLite (pysqlite2) Updated the readme.txt (for this and for last year's addition of CSV file support This includes a utility to convert CSV files to pysqlite2 databases, so is an example of how to use pysqlite2 (since the docs are so meagre). --- NEW FILE: pysqlite2Browse.py --- #!/usr/bin/python __version__="$Revision $"[11:-2] __date__="$Date $" __author__="Andy Todd <an...@ha...>" """ Module Name: pysqlite2Browse Description: Plug in for PythonCard application dbBrowse to provide pysqlite2 specific functionality Constant/configuration values are currently maintained in the source code. If we are to optimise this application they should be split into seperate configuration files (as per PythonCard/Webware style guidelines) The structure of this module should be replicated for different RDBMS so that they can be interchanged by dbBrowse - hopefully. """ # AGT 2005-05-17 # see http://www.pysqlite.org # see http://www.hwaci.com/sw/sqlite/faq.html from pysqlite2 import dbapi2 as sqlite import os class browse: # Connection should be a dictionary with at least two keys, # 'databasename' and 'directory' # This is wildly different to other database modules def __init__(self, connection): "Setup the database connection" self._system_tables=[] # Not providing a db name is guaranteed to ruin our connection if not connection['databasename']: raise ValueError filename = os.path.join(connection['directory'], connection['databasename']) # AGT 2005-05-15 single parameter, no mode on connect self._db = sqlite.connect(filename) self._cursor=self._db.cursor() # This one is used in getRow self._tableName='' def getTables(self): "Return a list of all of the non-system tables in <database>" ##stmt = "SELECT table_name FROM __table_names__" stmt = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;" self._cursor.execute(stmt) # I'm using a list comprehension here instead of a for loop, # either will do but I think this is more concise (unlike this comment) return [ x[0] for x in self._cursor.fetchall() if x[0] not in self._system_tables ] def getColumns(self, tableName): "Get the definition of the columns in tableName" stmt = "select * from " + tableName self._cursor.execute(stmt) #row = self._cursor.fetchone() columnDefs = [] # AGT 2005-05-15 description is a list of item, not a dict for column in self._cursor.description: columnName = column[0] dataType, nullable, key, default = "varchar", "", "", "" # Dodgy default, but if works for me precision = 255 columnDefs.append((columnName, dataType, precision, nullable, key, default)) return columnDefs def getQueryString(self, tableName): "Return a SQL statement which queries all of the columns in tableName" tableStructure=self.getColumns(tableName) # Construct and return the string stmt='SELECT ' for columnList in tableStructure: stmt+=columnList[0]+', ' stmt=stmt[:-2]+' FROM '+tableName return stmt def getRow(self, tableName): "Get a row from tableName" # When we upgrade to 2.2 this will be a great candidate for a # generator/iterator. In the meantime we use self._tableName to keep # track of what we are doing if tableName!=self._tableName: self._tableName=tableName self._cursor.execute(self.getQueryString(tableName)) try: result = self._cursor.fetchone() except: print "AGT argh" result=[] return result def getRows(self, tableName): "Get all of the rows from tableName" if tableName!=self._tableName: self._tableName=tableName self._cursor.execute(self.getQueryString(tableName)) try: result=self._cursor.fetchall() except: result=[] return result if __name__ == '__main__': # We are in an interactive session so run our test routines # Connect to the database ##connection={ 'databasename':'andy' ## ,'directory':'E:\Gadfly'} print os.getcwd() connection={'databasename':'calflora.db', 'directory':'.'} dbHolder = browse(connection) # Return all of our table names into user_tables user_tables = dbHolder.getTables() # Print out the structure of each table and its first row print "--------------------------------------------------" for table in user_tables: print "table:", table print dbHolder.getQueryString(table) print dbHolder.getRow(table) print "--------------------------------------------------" Index: dbLogin.py =================================================================== RCS file: /cvsroot/pythoncard/PythonCard/samples/dbBrowser/dbLogin.py,v retrieving revision 1.11 retrieving revision 1.12 diff -C2 -d -r1.11 -r1.12 *** dbLogin.py 4 Sep 2004 11:46:39 -0000 1.11 --- dbLogin.py 17 May 2005 20:33:53 -0000 1.12 *************** *** 18,21 **** --- 18,22 ---- ,'MetaKit': 'metakitBrowse' ,'PySQLite': 'pysqliteBrowse' + ,'PySQLite2': 'pysqlite2Browse' ,'PostgreSQL': 'postgreBrowse' ,'CSV': 'csvBrowse' } *************** *** 42,46 **** self.parent.connection={'databasename':self.components["txtUsername"].text, 'directory':self.components["txtPassword"].text} ! elif self.components.choice.stringSelection == 'PySQLite': self.parent.connection={'databasename':self.components["txtUsername"].text, 'directory':self.components["txtPassword"].text} --- 43,47 ---- self.parent.connection={'databasename':self.components["txtUsername"].text, 'directory':self.components["txtPassword"].text} ! elif self.components.choice.stringSelection == 'PySQLite' or self.components.choice.stringSelection == 'PySQLite2': self.parent.connection={'databasename':self.components["txtUsername"].text, 'directory':self.components["txtPassword"].text} *************** *** 89,93 **** def on_choice_select(self, event): "When we select 'Gadfly' change the available widgets'" ! if self.components.choice.stringSelection in ('Gadfly', 'MetaKit', 'PySQLite'): self.components.lblUsername.text = 'DB Name' self.components.lblPassword.text = 'DB Directory' --- 90,94 ---- def on_choice_select(self, event): "When we select 'Gadfly' change the available widgets'" ! if self.components.choice.stringSelection in ('Gadfly', 'MetaKit', 'PySQLite', 'PySQLite2'): self.components.lblUsername.text = 'DB Name' self.components.lblPassword.text = 'DB Directory' Index: readme.txt =================================================================== RCS file: /cvsroot/pythoncard/PythonCard/samples/dbBrowser/readme.txt,v retrieving revision 1.14 retrieving revision 1.15 diff -C2 -d -r1.14 -r1.15 *** readme.txt 6 Jul 2003 14:46:43 -0000 1.14 --- readme.txt 17 May 2005 20:33:53 -0000 1.15 *************** *** 1,9 **** Module : dbBrowser Date : 2nd July, 2003 ! Author : Andy Todd <an...@ha...> dbBrowser is a client for viewing data from relational databases. Once you connect to a database, select a table and the rows of data from it are available for you to view. ! The current version of dbBrowser works with MySQL, Gadfly, SQLite, Metakit, PostgreSQL and Oracle. The code is stable but thi is still an alpha release so if you are not already using any of these databases it is probably best to give this a miss until it is more stable. The current distribution includes an alternative version of dbBrowser (called dbBrowser2) which uses a wxGrid to display the results of your queries rather than the row at a time of dbBrowser. --- 1,10 ---- Module : dbBrowser Date : 2nd July, 2003 ! Author : Andy Todd <an...@ha...> ! Minor additions: May 17, 2005 Alex Tweedly <al...@tw...> dbBrowser is a client for viewing data from relational databases. Once you connect to a database, select a table and the rows of data from it are available for you to view. ! The current version of dbBrowser works with MySQL, Gadfly, SQLite, Metakit, PySQLite, PySQLite2, PostgreSQL and Oracle. The code is stable but thi is still an alpha release so if you are not already using any of these databases it is probably best to give this a miss until it is more stable. The current distribution includes an alternative version of dbBrowser (called dbBrowser2) which uses a wxGrid to display the results of your queries rather than the row at a time of dbBrowser. *************** *** 33,37 **** This has only been tested with release candidate 1 of this module. ! Future versions will use different databases. I'm planning to include support for ODBC and possibly csv files. If you would like to add support for another RDBMS please send me an e-mail or post a notice to the mailing list (pyt...@li...). Observations --- 34,43 ---- This has only been tested with release candidate 1 of this module. ! To access PySQLite databases you will need pysqlite installed, either version 1 or 2, get them from: ! http://www.pysqlite.org/ ! ! If you have pysqlite2 installed but don't have any database tables or data, a utility is included with this sample, to create a db from any CSV file. From the scripts directory run "python pysqlite2_from_csv.py". ! ! Future versions will use different databases. I'm planning to include support for ODBC. If you would like to add support for another RDBMS please send me an e-mail or post a notice to the mailing list (pyt...@li...). Observations *************** *** 50,53 **** --- 56,61 ---- Change Notices -------------- + 17.05.2005 - Added PySQLite2 support, including script to create a test database from a csv file. + ??.09.2004 - Added CSV support, including a testfile.csv test sample. 02.07.2003 - Added PostgreSQL support thanks to Jon Dyte 04.05.2003 - Added dbBrowser2 Index: dbLogin.rsrc.py =================================================================== RCS file: /cvsroot/pythoncard/PythonCard/samples/dbBrowser/dbLogin.rsrc.py,v retrieving revision 1.15 retrieving revision 1.16 diff -C2 -d -r1.15 -r1.16 *** dbLogin.rsrc.py 4 Sep 2004 11:46:39 -0000 1.15 --- dbLogin.rsrc.py 17 May 2005 20:33:53 -0000 1.16 *************** *** 15,19 **** 'name':'choice', 'position':(110, 0), ! 'items':['MySQL', 'Oracle', 'Gadfly', 'MetaKit', 'PySQLite', 'PostgreSQL', 'CSV'], 'stringSelection':'MySQL', }, --- 15,19 ---- 'name':'choice', 'position':(110, 0), ! 'items':['MySQL', 'Oracle', 'Gadfly', 'MetaKit', 'PySQLite', 'PySQLite2', 'PostgreSQL', 'CSV'], 'stringSelection':'MySQL', }, |
