Python:数据库基础操作

import pymysql
import sys

mysql_connect = pymysql.connect(host='192.168.10.100',user='lsy',passwd='(LsY0415)',port=3306,charset='utf8')
cursor = mysql_connect.cursor()     # 创建数据库游标.

# 把创建库和表的操作写到变量中;
fish_database = '''create database if not exists fish;'''
fish_table = '''create table if not exists FishType(
ID int not null primary key,
Type varchar(1000) not null,
Price varchar(1000));'''

# 创建库和表使用上放的变量.
cursor.execute(fish_database)
cursor.execute("use fish;")
cursor.execute(fish_table)

# 插入数据.
cursor.execute("insert into FishType (ID, Type, Price) values (1, 'Squid', '1~10$')")
cursor.execute("insert into FishType (ID, Type, Price) values (2, 'Carp', '10~40$')")

# 查询数据.
cursor.execute("select * from FishType")
print(cursor.fetchall())        # 打印出查询结果

# 删除数据.
cursor.execute("delete from FishType where ID=2")
cursor.execute("select * from FishType")
print(cursor.fetchall())        # 打印出查询结果

# 更新数据.
cursor.execute("update FishType set Price='5~8$' where ID=1")
cursor.execute("select * from FishType")
print(cursor.fetchall())        # 打印出查询结果

mysql_connect.commit()            # 
mysql_connect.close()                # 关闭mysql客户端连接
sys.exit()                                    # 结束程序运行