Python操作Mysql数据库

Python操作MySQL

pip install pymysql

import pymysql

(1) 链接MySQL数据库

​ db = pymysql.connect(主机名,用户名,密码,数据库名称)

(2) 设置字符集

​ db.set_charset('utf8')

(3) 创建游标对象

​ cursor = db.cursor()

(4) 准备SQL语句

(5) 执行SQL语句

​ cursor.execute(sql)

(6) 处理结果集

​ cursor.fetchall() 获取所有数据

​ cursor.fetchone() 获取一条

​ cursor.fetchmany(size) 获取size条

(7) 当前SQL语句执行受影响的行数

​ cursor.rowcount()

(8) 关闭数据库链接

​ db.close()

拼凑成正常SQL语句的办法

sql = 'select id from type where typename ="{}"'.format(typeName)

sql = 'select id from type where typename ="%s"'%(typeName)

sql = 'select id from type where typename ="'+typeName+'"'

sql = 'select id from type where typename =\''+typeName+'\''

1.python创建数据库表

import pymysql

db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='1likePython', db='TESTDB', charset='utf8')

cursor = db.cursor()

cursor.execute('DROP TABLE IF EXISTS Maoyan')

'''
sql = """CREATE TABLE BOOK(
          id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增 id',
          book_name VARCHAR(1024) NOT NULL COMMENT '小说名',
          author VARCHAR(1024) NOT NULL COMMENT '小说作者',
          book_type VARCHAR(1024) NULL NULL COMMENT '小说类型',
          book_state VARCHAR(1024) DEFAULT NULL COMMENT '小说状态',
          book_update VARCHAR(1024) DEFAULT NULL COMMENT '小说更新',
          book_time VARCHAR(1024) DEFAULT NULL COMMENT '更新时间',
          new_href VARCHAR(1024) DEFAULT NULL COMMENT '最新一章',
          book_intro VARCHAR(1024) DEFAULT NULL COMMENT '小说简介',
          createtime DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间'
          )"""
'''
'''
sql = """CREATE TABLE MUSIC(
          top VARCHAR(1024) DEFAULT NULL COMMENT '排名',
          music_name VARCHAR(1024) NOT NULL COMMENT '音乐名',
          songer VARCHAR(1024) NOT NULL COMMENT '歌手',
          createtime DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间'
          )"""
'''
sql = """CREATE TABLE Maoyan(
          top VARCHAR(1024) DEFAULT NULL COMMENT '榜单排名',
          title VARCHAR(1024) NOT NULL COMMENT '电影名',
          star VARCHAR(1024) NOT NULL COMMENT '影星',
          releasetime VARCHAR(1024) DEFAULT NULL COMMENT '上映时间',
          createtime DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间'
          )"""

cursor.execute(sql)

db.close()