python是个什么东西---python---PyQt4自备SQLite连接与查询
# coding:UTF-8import osimport sysfrom PyQt4.QtCore import *from PyQt4.QtGui import *from PyQt4.QtSql import *from ExcelTool import ExcelHandlerdef insertData(filenames): query = QSqlQuery() for filename in filenames: eh = ExcelHandler() results = eh.read_xls(filename) for result in results: for lines in result: sql = "INSERT INTO pseas VALUES (" for ii in range(len(lines)): sql = sql + "'" + lines.replace("\n","") + "'" if ii == len(lines) - 1: sql = sql + ")" else: sql = sql + "," query.exec_(sql) print sql QApplication.processEvents() query = QSqlQuery() sql = u"delete FROM pseas where jxName='XXX'" query.exec_(sql) print sql QApplication.processEvents() query.clear() query.finish() def createData(filenames): print "Dropping tables..." query = QSqlQuery() query.exec_("DROP TABLE version") QApplication.processEvents() print "Creating tables..."# query.exec_("""CREATE TABLE pseas (bsid VARCHAR(255) PRIMARY KEY NOT NULL,page VARCHAR(255),jxnum VARCHAR(255), jxname VARCHAR(255), jgnum VARCHAR(255), jgjpname VARCHAR(255), jgenname VARCHAR(255), ljid VARCHAR(255), ljnum VARCHAR(255),lj12num VARCHAR(255),ljjpname VARCHAR(255),ljenname VARCHAR(255),ljcount VARCHAR(255))""") query.exec_("""CREATE TABLE version (vsid INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, vsnum VARCHAR(255) ,vsdate VARCHAR(255))""") QApplication.processEvents() # print "Creating data..."# query.exec_("""INSERT INTO version(vsnum,vsdate) VALUES('1','20100802')""") # query.exec_("""INSERT INTO version(vsnum,vsdate) VALUES('2','20100802')""")# query.exec_("""INSERT INTO version(vsnum,vsdate) VALUES('3','20100802')""")# query.exec_("""INSERT INTO version(vsnum,vsdate) VALUES('1','20100803')""")# query.exec_("""INSERT INTO version(vsnum,vsdate) VALUES('2','20100803')""")# query.exec_("""INSERT INTO version(vsnum,vsdate) VALUES('3','20100803')""")# query.exec_("""INSERT INTO version(vsnum,vsdate) VALUES('4','20100803')""")# query.exec_("""INSERT INTO version(vsnum,vsdate) VALUES('1','20100804')""")# query.exec_("""INSERT INTO version(vsnum,vsdate) VALUES('1','20100805')""")# query.exec_("""INSERT INTO version(vsnum,vsdate) VALUES('1','20100806')""")# QApplication.processEvents()# query.exec_("""INSERT INTO version VALUES('1','1','20100802')""") # query.exec_("""INSERT INTO version VALUES('2','2','20100802')""")# query.exec_("""INSERT INTO version VALUES('3','3','20100802')""")# query.exec_("""INSERT INTO version VALUES('4','1','20100803')""")# query.exec_("""INSERT INTO version VALUES('5','2','20100803')""")# query.exec_("""INSERT INTO version VALUES('6','3','20100803')""")# query.exec_("""INSERT INTO version VALUES('7','4','20100803')""")# query.exec_("""INSERT INTO version VALUES('8','1','20100804')""")# query.exec_("""INSERT INTO version VALUES('9','1','20100805')""")# query.exec_("""INSERT INTO version VALUES('10','1','20100806')""")# QApplication.processEvents() print "Populating tables..." vsid = '0' vsnum='0' vsdate = '20100813' query.exec_("select max(vsid),vsnum,vsdate from version where vsdate='"+vsdate+"'") while query.next(): vsid = unicode(query.value(0).toString()) vsnum = unicode(query.value(1).toString()) vsdate = unicode(query.value(2).toString()) if vsid=='': print "vsid = ''" if vsnum=='': print "vsnum = ''" if vsdate == "": print "vsdate = ''"# insertData(filenames)# eh = ExcelHandler()# result = eh.read_xls(filename)# for lines in result:# sql = "INSERT INTO pseas VALUES ("# for ii in range(len(lines)):# sql = sql + "'" + lines + "'"# if ii == len(lines)-1:# sql = sql + ")"# else:# sql = sql + ","# query.exec_(sql)# print sql# QApplication.processEvents()def main(): app = QApplication(sys.argv) filename = os.path.join(os.path.dirname(__file__), "pseas.db") create = not QFile.exists(filename) db = QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName(filename) if not db.open(): QMessageBox.warning(None, "Asset Manager", QString("Database Error: %1").arg(db.lastError().text())) sys.exit(1) app.setOverrideCursor(QCursor(Qt.WaitCursor)) app.processEvents() if create: files = createData(files) else: files = ['./datafiles/bbb.xls']# createData(files) insertData(files) app.exec_() del db sys.exit(1)main()
页:
[1]