#coding:utf-8

import random

import string

import MySQLdb

def gen_charint(filename, width =4, num=5):

    f = open(filename, 'wb')

    charint = string.digits + string.letters

    for i in range(num):

        verify = [random.choice(charint) for j in range(width)]

        verify = ''.join(verify) + '\n'

        f.write(verify)

    f.close()

def store_mysql(filepath):

    conn = MySQLdb.connect(

            host="localhost",

            port=3306,

            user='root',

            passwd='root',

            db='test',

            charset='utf8'

        )

    cursor = conn.cursor()

    # 判断表是否存在

    cursor.execute('show tables in test')

    tables = cursor.fetchall()

    findtables = False

    for table in tables:

        if 'coupon' in table:

            findtables = True

    # 下面的sql语句中的符号为反引号

    # 由于刚开始设置code的字段长度偏小，报错后，调整这里的长度不起作用，查看数据库并未相应改变，需要手动更改数据库该字段长度

    if not findtables:

        cursor.execute('''

        CREATE TABLE `coupon24`(

        `id` INT NOT NULL AUTO_INCREMENT,

        `coupon` VARCHAR(66) NOT NULL,

        PRIMARY KEY(`id`));

        ''')

    f = open(filepath, 'rb')

    for line in f.readlines():

        code = line.split()

        cursor.execute('insert into coupon24 (coupon) VALUES (%s)', code)

    conn.commit()

    cursor.close()

    conn.close()

if __name__ == '__main__':

    filename = 'result24.txt'

    width = 4

    num = 11

    gen_charint(filename, width, num)

    store_mysql(filename)