安装
如何MySql数据库,咱不说了,不会的自己百度。但是安装好了MySql还要安装python的MySql 库。通过下面命令安装:
pip install mysql
提示安装成功即可
- 验证下是否成功
class DBTest(object):
def __init__(self):
self.db = mysqldb.connect("localhost", "root", "123456", "test", charset='utf8')
self.cursor = self.db.cursor()
def get_version(self):
# 使用cursor()方法获取操作游标
# 使用execute方法执行SQL语句
self.cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取一条数据
data = self.cursor.fetchone()
print("Database version : %s " % data)
def __del__(self):
# 关闭数据库连接
self.db.close()
if __name__ == "__main__":
db_test = DBTest()
db_test.get_version()
返回下面说明成功了
atabase version : 5.5.62
基本操作
我在之前代码基础上,实现了数据库的基本操作:
表结构如下:
这需要了解数据库的基本语句,
- 增
def insert_data(self):
insert_sql = "INSERT INTO douban_commits (id, commits_text, commits_score, commits_use_count," \
" commits_user_name, commits_date) VALUES (%s,%s,%s,%s,%s, %s)" % \
("'sfs'", "'sdfs'", "'sdsdf'", "'sdsdf'", "'fsfdf'", "'7987'")
try:
self.cursor.execute(insert_sql)
self.db.commit()
except:
print("insert error!")
- 删
def delete_data(self):
delete_sql = "delete from douban_commits where id like 'sfs' "
try:
self.cursor.execute(delete_sql)
self.db.commit()
except:
print("delete error")
- 查
def select_data(self):
select_sql = "select * from douban_commits"
try:
self.cursor.execute(select_sql)
results = self.cursor.fetchall()
for row in results:
print(row)
except:
print("insert error!")
- 改
def update_data(self):
update_sql = "update douban_commits set commits_text = 'test' where id like '111s'"
try:
self.cursor.execute(update_sql)
self.db.commit()
except:
print("update error")
总的代码如下:
lass DBTest(object):
def __init__(self):
self.db = mysqldb.connect("localhost", "root", "123456", "test", charset='utf8')
self.cursor = self.db.cursor()
def get_version(self):
# 使用cursor()方法获取操作游标
# 使用execute方法执行SQL语句
self.cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取一条数据
data = self.cursor.fetchone()
print("Database version : %s " % data)
def insert_data(self):
insert_sql = "INSERT INTO douban_commits (id, commits_text, commits_score, commits_use_count," \
" commits_user_name, commits_date) VALUES (%s,%s,%s,%s,%s, %s)" % \
("'sfs'", "'sdfs'", "'sdsdf'", "'sdsdf'", "'fsfdf'", "'7987'")
try:
self.cursor.execute(insert_sql)
self.db.commit()
except:
print("insert error!")
def select_data(self):
select_sql = "select * from douban_commits"
try:
self.cursor.execute(select_sql)
results = self.cursor.fetchall()
for row in results:
print(row)
except:
print("insert error!")
def delete_data(self):
delete_sql = "delete from douban_commits where id like 'sfs' "
try:
self.cursor.execute(delete_sql)
self.db.commit()
except:
print("delete error")
def update_data(self):
update_sql = "update douban_commits set commits_text = 'test' where id like '111s'"
try:
self.cursor.execute(update_sql)
self.db.commit()
except:
print("update error")
def __del__(self):
# 关闭数据库连接
self.db.close()
if __name__ == "__main__":
db_test = DBTest()
db_test.update_data()
如何插入中文字符
- 到安装目录下,找到my.ini文件
- 设置编码为utf8
分别设置下面的参数:
default-character-set=utf8
character-set-server=utf8
- 创建table时,设置编码格式为utf8
每个字段都要设置编码格式
- 设置整张表的编码格式:
期待您的关注
注意:
update_sql = “update douban_commits set commits_text = ‘test’ where id like ‘111s’”,
拿这条语句来说,like ‘111s’ 而不是 like 111s,不然误以为是关键字,导致报错。