Python数据库相关操作

#-*- coding:utf-8 -*-
#mysql的一些常用函数
#Author：hanjuan

import yaml
import pymysql

class DB():
    def __init__(self):
        print('Connect Database...')
        self.conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',password='000000',db='djangoproject')

    def clear(self,table_name):
        print('Clear '+table_name+'...')
        clear_sql = 'truncate '+table_name+';'
        with self.conn.cursor() as cursor:
            cursor.execute('set foreign_key_checks=0;')#清除外键
            cursor.execute(clear_sql)
        self.conn.commit()


    def insert(self,table_name,table_data):
        print('Insert Data...')
        for key in table_data:
            table_data[key]="'"+str(table_data[key])+"'"

        key = ','.join(table_data.keys())
        value = ','.join(table_data.values())
        insert_sql='insert into '+table_name+'('+key+')'+'values '+'('+value+')'
        with self.conn.cursor() as cursor:
            cursor.execute(insert_sql)
        self.conn.commit()


    def close(self):
        print('Close Database...')
        self.conn.close()

    #初始化数据
    def init_data(self,datas):
        print('Init Data...')
        for table,data in datas.items():
            self.clear(table)
            for d in data:
                self.insert(table,d)
        self.close()


#sql语句很长，分行写法

def search_seat_print(cursor):

    play_id = input("请输入要查询的场次ID，例如：场次1：")
    #此处的sql语句仅为示例，数据和表都是虚构的。
    search_sql = '''SELECT
                        oi,
                        osi,
                        f1,
                        f2,
                        cinema_id,
                        seat_name                       
                    FROM
                        cinema AS rc
                    INNER JOIN 
                    (
                            SELECT
                                ticket.order_id AS oi,
                                ticket.split_id AS osi,
                                ticket.Flag1 AS f1,
                                ticket.Flag2 AS f2,
                                t_cinema.cinema_id AS ci,
                                seat_name
                            FROM
                                ticket
                            INNER JOIN t_cinema
                            WHERE
                                ticket.split_id = t_cinema.split_id
                            AND play_id = '{0}'
                            ORDER BY
                                t_cinema=.order_id ASC
                        ) AS od -- ORDER BY seat_name desc
                    WHERE
                        rc.cinema_id = od.ci'''.format(play_id)  #注意{0}为替换符，执行的时候会用play_id提换掉{0}，如果有多个需要提换的字段，就将提换字段增加1，例如{1}，在format(play_id,xinzeng). 就会在执行的sql中用xinzeng，提换掉{1}.
    cursor.execute(search_sql)
    result = cursor.fetchall()
    # print("result的类型",type(result))
    # print("座位如下：=========：", result)
    return result

if __name__=='__main__':
    db=DB()
    # db.clear('auth_group')
    # group_data={'id':2,'name':'ailsa2'}
    # db.insert('auth_group',group_data)
    path= r'/Users/ailsa/Desktop/pyproject/djangoproject/testdata/data.yaml'
    f = open(path,'r')
    datas= yaml.load(f)
    db.init_data(datas)