Python+PyQt 数据库基本操作

浏览数：36 / 时间：2015年06月12日

Sqlite:

使用Python的sqlite3：

需要注意下commit方式与qt稍有不同

import sqlite3

class DBManager():
    def __init__(self):
        self.db = sqlite3.connect("core.db")
        self.query = self.db.cursor()  
        ...
        self._initTable()
        ...

    ## Create table["webpower"] if not exists
    def _initTable(self):
        self.query.execute("create table if not exists webpower (id integer primary key,name TEXT,state integer)")
        self.db.commit()

    ...

使用QtSql:

 1 from PyQt4.QtSql import QSqlQuery,QSqlDatabase
 2 
 3 class DBManager():
 4     def __init__(self):
 5         self.db = QSqlDatabase.addDatabase("QSQLITE") #select database type
 6         self.db.setDatabaseName("core.db") # set database name
 7         self.db.open()  #connect to or create database  
 8         self.query = QSqlQuery() #sql handler
 9         ...    
10     
11     ...

Mysql:

from PyQt4.QtSql import QSqlQuery,QSqlDatabase

class DBManager():
    def __init__(self):
        self.db = QSqlDatabase.addDatabase("QMYSQL")  #select database type
        self.db.setHostName("localhost")  #set address
        self.db.setUserName("root"); #set user name
        self.db.setPassword("root"); #set user pwd
        self._trytoConnect() #check connection
        self.query = QSqlQuery()

        ## create database
        self.query.exec_("CREATE DATABASE test1")

        ## Choose database
        self.db.setDatabaseName("test1")
        self._trytoConnect()
        #also can be :self.query.exec_("USE test1")

    ## Connect and check connection state
    def _trytoConnect(self):
        if (self.db.open()):     
            print "Success"
        else:
            print "Failed to connect to mysql"

    ...

常用SQL：

--create table
create table if not exists test2 (id integer primary key,time real,powers real);

--clear table
delete from test2;

--insert
insert into t1 values(1,0.1,0.2);

--select
select * from test1

--delete table
drop table test4