python運算元據庫，批量插入資料庫資料

hello_fancy發表於2017-11-06
Python資料庫
#coding=utf8
from pymysql import connect,cursors
from pymysql.err import  OperationalError
import sys,time


class DataBase():   #類名和模組名一致，robot匯入時不用寫類名@@@@@
    def __init__(self,mysql_name):
        try:
            self.conn = connect(host='123.。。。',
                                port=3306,
                                user='mys。。。',
                                password='。。。',
                                db=mysql_name,
                                charset ='utf8mb4',
                                cursorclass = cursors.DictCursor
                                )

        except OperationalError as e:
            print e


    #查詢資料庫
    def select(self,table_name):

        #sql語句之間應該有空格
        sql = 'select * from '+table_name+';'
       cursor = self.conn.cursor()   #建立遊標

        cursor.execute(sql) #執行sql
        self.conn.commit() #提交
        #將查詢的資料列印出來
        results = cursor.fetchall()
        for row in results:
            print row
    #批量插入資料
    def insert_inspection_list(self,table_name):
        for i in range(1,100):

            id = str(i)
            inspection_num = 'NJ'+ str(100000+i)
            car_id =  i+1
            
            create_time = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())
            update_time = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())

            #@@@@@字串用雙引號加單引號"''",三種sql語句
            sql1 = 'insert into car (id,inspection_num,car_id, create_uid) values 
('+id+',"' + inspection_num + '",' + car_id + ',238);           
           
           sql2 = 'insert into car (id,inspection_num,car_id,create_uid，create_time,
update_time)values({},{},{},{},{},{})'.format(id,inspection_num,car_id,238,create_time,
update_time)
           sql3 = 'insert into car (id,inspection_num,car_id,create_uid，create_time,
update_time)values (%s,%s,%d,%s,%s,%s)' % (id, inspection_num, car_id, 238, create_time,
 update_time)
            print sql
            cursor = self.conn.cursor()

            cursor.execute(sql)
            self.conn.commit()


    # 關閉資料庫
    def close(self):
        self.conn.close()
if  __name__ == '__main__':
    tb = DataBase('manager')
    tb.insert_inspection_list('inspection_list')

 
 
 
 
 
 
 

相關文章