Python 链接MySQL数据库

Source
版权声明:本文为博主原创文章,未经博主允许禁止转载! https://blog.csdn.net/az9996/article/details/85094917

参考资料:http://www.runoob.com/python3/python3-mysql.html
这里使用的是库是pymysql

Python链接数据库时,mysql中要有要连接的数据库,否则会无法连接。

import pymysql

#import Movie 

host = 'localhost'	#主机名
user = 'root'		#用户名
password = '' 	#密码
database = 'douban2' 	#数据库名

#建表
def create_table():
    db=pymysql.connect(host,user,password,database)
    cursor=db.cursor()
    cursor.execute("drop table if exists contents")
    sql="""
         create table contents(
            movie_id char(60) not null,
            movie_title char(200),
            movie_director char(200),
            movie_screenwriter char(200),
            movie_character varchar(1000),
            movie_type char(200),
            movie_country char(200)
                ) 
            """
    cursor.execute(sql)
    db.commit()
    db.close()

#建表
def create_table_comment():
    db=pymysql.connect(host,user,password,database)
    cursor=db.cursor()
    cursor.execute("drop table if exists comment")
    sql="""
         create table comment(
            movie_id char(60) not null,
            movie_comment text
                ) 
            """
    cursor.execute(sql)
    db.commit()
    db.close()

#插入数据
def insert_database(id,title,director,screenwriter,mcharacter,mtype,country):
    db=pymysql.connect(host,user,password,database)
    cursor=db.cursor()
    sql=' insert into contents values("%s","%s","%s","%s","%s","%s","%s")'\
        %(id,title,director,screenwriter,mcharacter,mtype,country)

    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.rollback()

    db.close()

#插入数据
def insert_comment(id,comment):
    db=pymysql.connect(host,user,password,database)
    cursor=db.cursor()
    sql=' insert into comment values("%s","%s")'\
        %(id,comment)

    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.rollback()

    db.close()

#查询数据
def select_database(id):
    db = pymysql.connect(host, user, password, database)
    cursor = db.cursor()

    sql='select movie_id from contents ' \
        'where movie_id="%s"'%id

    try:
        cursor.execute(sql)
        results=cursor.fetchall()
        for row in results:
            m_id=row[0]
            print(("movie_id=%s")%m_id + 'ok!' + '\n')
    except:
        print("Error:unable to fetch"+ id)
    db.close()