利用python对mysql数据库进行建表操作:可以同一个py文

环境:windows, python3.6, mysql5.7

1.安装相关的库

      pip install pymysql

2.新建build_table.py文件,下为案例代码

import pymysql

#获取数据库配置
db = pymysql.connect(
    host='localhost',
    database='novellist',  #库名
    user='root',
    password='123456',
    port=5200,
    charset='utf8'
    )

#使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()

#使用execute()方法执行sql,如果表存在则删除
# cursor.execute("drop table if exists novel")

#执行sql处理语句创建表
sql_novel = """create table novel(
id bigint not null primary key auto_increment,
bookname varchar(355) not null,
novel_url varchar(355) not null,
cover_url varchar(355) not null,
key novel_website_id_index (website_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;"""


# cursor.execute("drop table if exists chapter")
sql_chapter="""
create table chapter(
id bigint not null primary key auto_increment,
novel_id bigint not null,
chapter_name varchar(255) not null,
chapter_url varchar(255) not null,
chapter_uptime varchar(255) not null,
chapter longtext,
key chapter_novel_id_index (novel_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""

# cursor.execute("drop table if exists website")
sql_website="""
create table website(
id bigint not null primary key auto_increment,
website_url varchar(255) not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""

#创建
cursor.execute(sql_novel)
cursor.execute(sql_chapter)
cursor.execute(sql_website)

#关闭数据库,无论对数据库如何操作,都要关闭数据库,防止出错
db.close()

3.运行

python build_table.py