torndb 学习笔记

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @author zhuoshenghua

import torndb

class DBTorndb():
    ''' torndb 链接数据库相关 '''
    def __init__(self,host='localhost', port='3306', dbname='test', user='root', password='******', charset='utf8'):
        self.host = host
        self.port = port
        self.dbname = dbname
        self.user = user
        self.pwd = password
        self.charset = charset
        self.conn = torndb.Connection(self.host+':'+self.port,self.dbname,self.user,self.pwd)

    def queryOpra(self):
        ''' 查询 【query: 得到多行记录，单行为字典】'''
        sql = 'SELECT * FROM test'
        qryRes = self.conn.query(sql)
        print(qryRes)

    def getOpra(self):
        ''' 查询  【get： 得到单行记录】'''
        sql = 'SELECT * FROM test WHERE  id= %s'
        id = 1
        getRes = self.conn.get(sql,id)
        print(getRes)

    def insertManyOpra(self):
        ''' 插入 【insertmany：参数支持列表或元组】 '''
        #插入单行记录
        sql = " INSERT INTO test (id,name) VALUES (%s,%s) "
        self.conn.insertmany(sql,[[8,'李一']])
        self.queryOpra()
        #插入多行记录
        self.conn.insertmany(sql,[[9,'王二'],[10,'张三']])
        self.conn.insertmany(sql,[(11,'李四'),(12,'七七')])
        self.queryOpra()
        self.conn.execute(sql,1,'张三')

    def insertOpra(self):
        ''' 插入 【insert的参数不支持列表或元组，如果想插入列表或元组的话可以用insertmany】'''
        sql = "INSERT INTO test (id,name) VALUES (%s,%s)"
        self.conn.insert(sql,7,'张三')
        self.queryOpra()

    def updateOpra(self):
        '''更新 '''
        sql = " update test SET name=%s WHERE id=%s"
        self.conn.update(sql,'赵六',2)
        self.queryOpra()

    def executeOpra(self):
        ''' excute '''
        # sql = " INSERT INTO test (id,name) VALUES (%s,%s) "
        # self.conn.execute(sql,1,'张三')
        # sql = 'SELECT * FROM test'
        # self.conn.execute(sql)
        # sql = " update test SET name=%s WHERE id=%s"
        # self.conn.execute(sql,'零一',1)
        # sql = " delete FROM test WHERE id=%s"
        # self.conn.execute(sql,7)
        # sql = 'CREATE DATABASE IF NOT EXISTS my_db DEFAULT CHARSET utf8 COLLATE utf8_general_ci'
        # self.conn.execute(sql)
        # sql = 'CREATE TABLE test (name VARCHAR(20) NOT NULL) DEFAULT CHARSET utf8'
        # self.conn.execute(sql)
        sql = 'CREATE TABLE IF NOT EXISTS user (id int primary key auto_increment,name varchar(18),description varchar(100));'
        self.conn.execute(sql)
        # self.queryOpra()



if __name__ == '__main__':
    tdb = DBTorndb()
    # tdb.queryOpra()
    # tdb.getOpra()
    # tdb.insertOpra()
    # tdb.updateOpra()
    # tdb.insertManyOpra()
    tdb.executeOpra()