今天的主题是操作数据库。
那首先呢,就是要安装一个数据库。
https://www.mysql.com/ –> 这里是 Mysql 的官网。
Python并没有内置的Mysql工具。 不过python出了名的开源库多,
其中一个佼佼者则是pymysql。
开源库,我一般采取 pip3 install xxx来安装。
以下这段代码就是要获取连接对象 conn。
我这里基本上是默认设置, 另外要注意的就是要设置字符集 charset。
不然的话会出现某些字符不能存进去的情况。
conn = pymysql.connect(host = '127.0.0.1', port = 3306, user = 'root', passwd = None, charset='utf8mb4')
这段就是获取光标对象cur, 并选择使用的数据库
cur = conn.cursor()
cur.execute(‘USE spider’)
3. 简单的尝试
我这次先简单尝试下数据库的操作。
代码如下:
import pymysql
def saveNovel_inf(author, type, ranking):
cur.execute('insert into novel (author, type, ranking) values("'+author+'", "'+type+'", "'+ranking+'")')
cur.connection.commit()
print(cur.fetchone())
# list用append, str用+, join的用法是每个字符用X.join的X拼接起来
def updateNovel_infByAuthor(author, newAuthor = None, type = None, ranking = None):
sql = 'update novel set'
if newAuthor == None:
newAuthor = author
if newAuthor != None:
sql = sql + ' author = "'+newAuthor+'" '
if type != None:
sql = sql + ' type = "'+type+'"'
if ranking != None:
sql = sql + ' ranking = "'+ranking+'"'
sql = sql + ' where author = "'+author+'"'
print(sql)
cur.execute(sql)
cur.connection.commit()
def searchNovel_infByAuthor(author):
cur.execute('select * from novel where author = "'+author+'"')
result = cur.fetchone()
# "***"+result[0]+"***", %(%d, %s, %s)
print('%d %s %s' %(result[0], result[1], result[2]))
def delectNovel_infByAuthor(author):
cur.execute('delect from novel where author = "'+author+'"')
cur.connection.commit()
# cur.close()
if __name__ == '__main__':
author = '海贼王'
type = '搞笑'
ranking = '55'
conn = pymysql.connect(host = '127.0.0.1', port = 3306, user = 'root', passwd = None, charset='utf8mb4')
global cur
cur = conn.cursor()
try:
cur.execute('use spider')
searchNovel_infByAuthor(author)
finally:
cur.close()
conn.close()
连接对象(conn)和光标对象(cur)用完记得关掉啊。
虽然pymysql不大,这次用的只是pymysql中的一些皮毛,
想更深入了解的,就去查看官方文档吧。