python3

• 首先导入模块import pymysql.cursors
• 链接数据库
• 获取游标

connection = pymysql.connect(host='127.0.0.1',
                             user='root',
                             password='896641606',
                             db = 'newtable',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor()

• 创建表TABLEONE此处要大写 不知为何?不需要引号

sql = """CREATE TABLE IF NOT EXISTS TABLEONE (
         `id` int(11) NOT NULL AUTO_INCREMENT,
         `title` varchar(255) COLLATE utf8_bin NOT NULL,
         `vote_count` int(255) COLLATE utf8_bin NOT NULL,
         `answer_count` int(255) COLLATE utf8_bin NOT NULL,
         `scan_count` int(255) COLLATE utf8_bin NOT NULL,
         PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1 ;"""

creatResult = cursor.execute(sql)

• 插入数据
• 需要注意的是虽然创建表的时候定义的 三列 vote_count answer_count scan_count 是int 类型 但是拼接 sql 语句的时候仍然要用 %s 否则插入不报错,但是不成功.

sql = "INSERT INTO `TABLEONE` (`title`, vote_count, answer_count, scan_count) VALUES (%s, %s, %s, %s)"
cursor.execute(sql, ('webmaster@python.org', -1,1,30))
connection.commit()

源文档地址: http://pymysql.readthedocs.io/en/latest/user/examples.html