python3.6中对Mysql的基本操作

连接：

import pymysql
connect = pymysql.connect(host='***.**.*.**',
                          user='jxz',
                          password='******',
                          db='jxz',
                          port=3306,
                          charset='utf8',
                          autocommit=True
                          )

cur = connect.cursor(pymysql.cursors.DictCursor)
    sql1 = "select * from information_schema.TABLES WHERE TABLE_NAME = 'Product_Management_zyb'"
    cur.execute(sql1)
    if len(cur.fetchall()) == 0:
        sql2 = "CREATE TABLE IF NOT EXISTS Product_Management_zyb( " \
              "ID int PRIMARY KEY NOT NULL AUTO_INCREMENT," \
              "name CHAR(10) NOT Null UNIQUE,price FLOAT(255,1) " \
              "NOT Null,count INT NOT NULL ,color CHAR(10) NOT NULL );"
        cur.execute(sql2)
    cur.execute(sql)
    res = cur.fetchall() 
    rowcount = cur.rowcount    #表中总共有多少数据
    rownumber = cur.rownumber    #当前游标所在位置

    cur.close()
    connect.close()

 

sql语句以及占位符的使用：

    sql1 = "select * from information_schema.TABLES WHERE TABLE_NAME = 'Product_Management_zyb'"
    sql2 = "CREATE TABLE IF NOT EXISTS Product_Management_zyb( " \
              "ID int PRIMARY KEY NOT NULL AUTO_INCREMENT," \
              "name CHAR(10) NOT Null UNIQUE,price FLOAT(255,1) " \
              "NOT Null,count INT NOT NULL ,color CHAR(10) NOT NULL );"
    sql3 = "insert into Product_Management_zyb (name,price,count,color) values ('%s','%s','%s','%s');" % (name,price,count,color)
    sql4 = "update Product_Management_zyb set price = '%s',count = '%s',color = '%s' where name = '%s'"% (price,count,color,name)