柚子快报邀请码778899分享：【Python基础】MySQL
文章目录
@[toc]创建数据库创建数据表数据插入数据查询数据更新
个人主页：丷从心·
系列专栏：Python基础
学习指南：Python学习指南
创建数据库
import pymysql
def create_database():
db = pymysql.connect(host='localhost', user='root', password='root')
cursor = db.cursor()
sql = """
create database if not exists python_basics charset=utf8;
"""
try:
cursor.execute(sql)
except Exception as e:
print(f'数据库创建失败: {e}')
else:
print('数据库创建成功...')
if __name__ == '__main__':
create_database()
pymysql.connect(host='localhost', user='root', password='root')用于连接数据库
host用于指定数据库IP地址，localhost表示本地user用于指定数据库用户名password用于指定数据库密码 db.cursor()返回一个游标对象，用于执行数据库操作cursor.execute(sql)用于执行一条SQL语句
创建数据表
import pymysql
def create_table():
db = pymysql.connect(host='localhost', user='root', password='root', db='python_basics')
cursor = db.cursor()
sql = """
create table if not exists book_info
(
id int primary key auto_increment,
name varchar(10) not null,
pub_date datetime,
read_count int,
comment_count int,
is_delete bit
);
"""
try:
cursor.execute(sql)
print('数据表创建成功...')
except Exception as e:
print(f'数据表创建失败: {e}')
finally:
cursor.close()
db.close()
if __name__ == '__main__':
create_table()
数据插入
import pymysql
def insert_data():
with pymysql.connect(host='localhost', user='root', password='root', db='python_basics') as db:
cursor = db.cursor()
sql = """
insert into book_info (id, name, pub_date, read_count, comment_count, is_delete) values (%s, %s, %s, %s, %s, %s);
"""
try:
cursor.executemany(sql, [(0, '射雕英雄传', '1980-5-1', 12, 34, 0),
(0, '天龙八部', '1986-7-24', 36, 40, 0),
(0, '笑傲江湖', '1995-12-24', 20, 80, 0),
(0, '雪山飞狐', '1987-11-11', 58, 24, 0)])
db.commit()
print('数据插入成功...')
except Exception as e:
print(f'数据插入失败: {e}')
db.rollback()
if __name__ == '__main__':
insert_data()
db.commit()用于事务提交db.rollback()用于在数据插入失败时进行事务回滚
数据查询
import pymysql
def query_data():
db = pymysql.connect(host='localhost', user='root', password='root', db='python_basics')
cursor = db.cursor()
sql = """
select * from book_info;
"""
try:
cursor.execute(sql)
result = cursor.fetchall()
# result = cursor.fetchone()
# result = cursor.fetchmany(4)
for book in result:
book_info = dict()
book_info['id'] = book[0]
book_info['name'] = book[1]
book_info['pub_date'] = book[2]
book_info['read_count'] = book[3]
book_info['comment_count'] = book[4]
book_info['is_delete'] = book[5]
print(book_info)
except Exception as e:
print(f'查询失败: {e}')
finally:
cursor.close()
db.close()
if __name__ == '__main__':
query_data()
{'id': 1, 'name': '射雕英雄传', 'pub_date': datetime.datetime(1980, 5, 1, 0, 0), 'read_count': 12, 'comment_count': 34, 'is_delete': b'\x00'}
{'id': 2, 'name': '天龙八部', 'pub_date': datetime.datetime(1986, 7, 24, 0, 0), 'read_count': 36, 'comment_count': 40, 'is_delete': b'\x00'}
{'id': 3, 'name': '笑傲江湖', 'pub_date': datetime.datetime(1995, 12, 24, 0, 0), 'read_count': 20, 'comment_count': 80, 'is_delete': b'\x00'}
{'id': 4, 'name': '雪山飞狐', 'pub_date': datetime.datetime(1987, 11, 11, 0, 0), 'read_count': 58, 'comment_count': 24, 'is_delete': b'\x00'}
cursor.fetchall()用于获取所有查询结果cursor.fetchone()用于获取一条查询结果cursor.fetchmany(4)用于获取四条查询结果
数据更新
import pymysql
def update_data():
db = pymysql.connect(host='localhost', user='root', password='root', db='python_basics')
cursor = db.cursor()
sql = """
update book_info set read_count = read_count + 1 where id = 1;
"""
try:
cursor.execute(sql)
db.commit()
print('数据更新成功...')
except Exception as e:
print(f'数据更新失败: {e}')
db.rollback()
finally:
cursor.close()
db.close()
if __name__ == '__main__':
update_data()
柚子快报邀请码778899分享：【Python基础】MySQL
文章来源
发表评论