参考资料: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()