Python创建插入数据库MySQL

 

首先要在控制台创建好数据库 mysql -u root -p 

 创建数据库

查看数据库

 

 

 -------------------更新分割线（上面为新增。。。太久没用都忘了SQL基本命令了）---------------

 

 1 def insert_sql():

　　　　db = pymysql.connect(host = "localhost",
                     user = "root",
                     passwd = "root",
                     db = "rail_freightdb",
                     charset = "utf8")

 3     cursor = db.cursor()
 4     cursor.execute("DROP TABLE IF EXISTS south_sheet1")
 5     sql = """CREATE TABLE south_sheet1(
 6               id INT NOT NULL AUTO_INCREMENT,
 7               area CHAR(10),
 8               destination CHAR(20),
 9               distance INT(10),
10               price INT(10),
11               rate FLOAT(10),
12               office CHAR(10),
13               PRIMARY KEY (id)
14     );
15     """
16     cursor.execute(sql)
17 
18     area, go_to, distance, price, rate ,office = get_all_url()
19 
20     sql =   'INSERT INTO south_sheet1(area,destination,distance,price,rate,office)' \
21             'VALUE ("%s","%s","%d","%d","%2f","%s")' % \
22              (area,go_to,int(distance),int(price),float(rate),office)
23     cursor.execute(sql)
24     db.commit()
25     db.close()