import MySQLdb

try:

    conn = MySQLdb.connect(

        host="127.0.0.1",

        port=3306,

        user="root",

        passwd="",

       # db="juntest",#也可以在这一步显示数据库名

        charset="utf8")

    cur = conn.cursor()

    cur.execute('CREATE DATABASE IF NOT EXISTS juntestDBnew DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')  #没有这个表，就创建

    conn.close()

    print u"创建数据库juntestDBnew成功! "

except MySQLdb.Error, e:

    print "Mysql Error %d: %s" % (e.args[0], e.args[1])

#coding=utf-8

import MySQLdb

try:

    conn = MySQLdb.connect(

        host="127.0.0.1",

        port=3306,

        user="root",

        passwd="",

       # db="juntest",另外写一种建表的方法

        charset="utf8")

    conn.select_db('juntestDBnew')#选择一个数据库

    cur = conn.cursor()

    cur.execute("drop table if exists emp_info2;")

    cur.execute("drop table if exists salary2;")

    cur.execute('''create table emp_info2(

id int not null auto_increment,

name varchar(30) not null,

sex char(4) default null,

dept varchar(10),

mobile varchar(11) not null unique,

birthday date default "0000-00-00",

primary key(id)

)engine=innodb character set utf8 comment 'employer info';

 ''')

    cur.execute('''create table salary2(

id int not null auto_increment,

emp_id int not null,

salary int not null,

primary key(id)

)engine=innodb character set utf8 comment 'employer salary info';

   ''')

    cur.close()

    conn.close()

    print u"创建数据表成功! "

except MySQLdb.Error, e:

     print "Mysql Error %d: %s" % (e.args[0], e.args[1])

#coding=utf-8

import MySQLdb

import random

try:

    conn = MySQLdb.connect(

        host="127.0.0.1",

        port=3306,

        user="root",

        passwd="",

        db="juntestDBnew",#另外写一种建表的方法

        charset="utf8"

    )

    cur=conn.cursor()

    num=random.randint(10,300)

    sql="insert into emp_info2 values(%s,%s,%s,%s,%s,%s)"#这种写法自增字段和隐藏字段都要写出来

    #cur.execute(sql, (4, 'jun1', 'm', 'jun', '12345', '2017-4-30'));

    for i in  range(1,10):

        cur.execute('delete from emp_info2 where id='+str(i));

        #第一种插入

        cur.execute(sql, (i, 'jun'+str(i), 'm', 'jun', ''+str(random.randint(1,1000)), '2017-4-'+str(random.randint(1,30))));

        print u"插入第(%s)条数据成功"%i

    cur.close()

    conn.commit()

    conn.close()

except MySQLdb.Error, e:

     print "Mysql Error %d: %s" % (e.args[0], e.args[1])