Hello, the script below should take the strings in the list newData and put the contents into two different tables of the Firebird example database "employee.fdb". Strings starting with "C" belong to the "customer" table and the insert statement returns the newly created cust_no (new in firebird 2.0). Strings starting with "O" belong to table "sales" and their content is combined with the last new cust_no to form a new record.
In the present form, with all "transaction()" and "commit()" calls commented out, I get the DatabaseError "insOrder deadlock Unable to execute query". Same error if I uncomment the calls labeled "Transaction use 1". This probably shouldn't work because the documentation says "When using transactions you must start the transaction before you create your query." - but it would be the sensible way to do the inserts. If I uncomment instead the calls labeled "Transaction use 2", then I get another error message from the second call to insCustomer: "insCustomer/exec The cursor identified in a FETCH or CLOSE statement is not open. Unable to close statement" In all the cases no new record gets into the database. What is wrong? Of course, in this very small example I could first put all the "C" records in, collect the new cust_no values in a list and process them together with the "O" records. But what if this is no tiny example list, but a huge file? Thanks for any shove in the right direction, Sibylle import sys from PyQt4.QtCore import * from PyQt4.QtSql import * newData = ['C;Moritz;Germany', 'O;V08A0001;141;520.10', 'C;Capitalism Kill & Destroy Ltd.;England', 'O;V08A0002;6;7200', 'C;Camorra Corp.;Italy', 'O;V08A0003;121;240', 'C;Adam & Eve;Austria', 'O;V08A0004;141;3500', 'C;Toblerone Inc.;Switzerland', 'O;V08A0005;141;689.50'] class DatabaseError(Exception): pass def employeeConn(user='sysdba', passwd='masterkey'): db = QSqlDatabase.addDatabase('QIBASE') db.setHostName('localhost') db.setConnectOptions('ISC_DPB_LC_CTYPE=ISO8859_1') db.setDatabaseName('Employee') db.setUserName(user) db.setPassword(passwd) ok = db.open() msg = (db.lastError().text() if not ok else '') return (ok, msg) def insCustomer(custdata, query): (idchar, custname, custcountry) = custdata.split(';') custno_var = QVariant() query.bindValue(':customer', QVariant(custname)) query.bindValue(':country', QVariant(custcountry)) if not query.exec_(): raise DatabaseError, 'insCustomer/exec %s' % query.lastError().text() if query.first(): custno_var = query.value(0) else: raise DatabaseError, 'insCustomer/first %s' % query.lastError().text() return custno_var def insOrder(custno_var, orderdata, query): (idchar, orderno, salesrep, total) = orderdata.split(';') query.bindValue(':po_number', QVariant(orderno)) query.bindValue(':cust_no', custno_var) query.bindValue(':sales_rep', QVariant(salesrep)) query.bindValue(':total_value', QVariant(total)) if not query.exec_(): raise DatabaseError, 'insOrder %s' % query.lastError().text() def main(args): app = QCoreApplication(args) (ok, msg) = employeeConn() if not ok: print msg sys.exit(1) try: # Transaction use 2 # QSqlDatabase.database().transaction() custQuery = QSqlQuery() custQuery.prepare('INSERT INTO CUSTOMER (customer, country) ' 'VALUES (:customer, :country) RETURNING cust_no') orderQuery = QSqlQuery() orderQuery.prepare('INSERT INTO SALES (po_number, cust_no, sales_rep, ' 'total_value) VALUES (:po_number, :cust_no, ' ':sales_rep, ':total_value)') new_custno = QVariant() for data in newData: # Transaction use 1 # QSqlDatabase.database().transaction() if data.startswith('C'): new_custno = insCustomer(data, custQuery) elif data.startswith('O'): insOrder(new_custno, data, orderQuery) # Transaction use 1 # QSqlDatabase.database().commit() print data # Transaction use 2 # QSqlDatabase.database().commit() except DatabaseError, e: print e finally: QSqlDatabase.database().close() print 'Ready.' if __name__ == '__main__': main(sys.argv) -- Dr. Sibylle Koczian _______________________________________________ PyQt mailing list PyQt@riverbankcomputing.com http://www.riverbankcomputing.com/mailman/listinfo/pyqt