python进阶-02-数据库

1 sqllite

sqlite为python内置数据库

建表

首先创建一个.db文件

# coding=utf-8
# 导入模块
import sqlite3

# 创建连接
con = sqlite3.connect("d:/dat/sqlite3/demo.db")
# 创建游标对象
cur = con.cursor()
# sql
sql = '''create table t_person(
            pno integer primary_key auto_increment,
            pname varchar not null,
            age integer)
'''
try:
    # 执行sql
    cur.execute(sql)
    print('创建表成功')
except Exception as e:
    print(e)
    print('创建表失败')
finally:
    # 关闭连接
    cur.close()
    con.close()

插入一条数据

# coding=utf-8
# 导入模块
import sqlite3

# 创建连接
con = sqlite3.connect("d:/dat/sqlite3/demo.db")
# 创建游标对象
cur = con.cursor()
# sql
sql = 'insert into t_person(pname,age) values (?,?)'
try:
    # 执行sql,参数类型是元组
    cur.execute(sql, ('张三', 23))
    # 提交事务
    con.commit()
    print('执行成功')
except Exception as e:
    print(e)
    # 回滚
    con.rollback()
    print('执行失败')
finally:
    # 关闭连接
    cur.close()
    con.close()

插入多条

...
cur.executemany(sql, [('李四',18), ('王五', 29)])
...

查询

sql = 'select * from t_person'
try:
    cur.execute(sql)
    person_all = cur.fetchall()
    # 查询一条数据
    # person = cur.fetchone()
    # 遍历
    for p in person_all:
        print(p)

修改

...
# sql
sql = 'update t_person set pname=? where pno=?'
try:
    cur.execute(sql, ('李四',1))
    con.commit()
...

删除

...
# sql
sql = 'delete from t_person where pno=?'
try:
    cur.execute(sql, (1,))
    con.commit()
...

2 mysql

下载安装

community->installer
Setup Type:Custom->选择mysql server->下一步即可

安装pymysql模块

命令行执行:

pip install PyMySQL

卸载:

pip uninstall PyMySQL

或者可以在官网下载.whl文件,执行:

pip install ....whl

创建表

# coding=utf-8
import pymysql

# 创建连接
con = pymysql.connect(host="localhost", user="root", password="root", port=3306, database="testdb")
# 创建游标对象
cur = con.cursor()
# 编写sql
sql = """
    create table t_student(
      sno int primary key auto_increment,
      sname varchar(30) not null,
      age int(2),
      score float(3.1)
    )
"""
try:
    cur.execute(sql)
    print("执行成功")
except Exception as e:
    print(e)
    print("执行失败")
finally:
    # 关闭连接
    cur.close()
    con.close()

插入数据

# coding=utf-8
import pymysql

# 创建连接
con = pymysql.connect(host="localhost", user="root", password="root", port=3306, database="testdb")
# 创建游标对象
cur = con.cursor()
# 编写sql
sql = """
    insert into t_student (sname,age,score) values (%s,%s,%s)
"""
try:
    # 执行
    # 插入一条
    # cur.execute(sql, ("张三", 18, 100.0))
    # 插入多条
    cur.executemany(sql, [("李四", 19, 99.9), ("王五", 20, 99.8)])
    # 提交事务
    con.commit()
    print("执行成功")
except Exception as e:
    print(e)
    # 回滚
    con.rollback()
    print("执行失败")
finally:
    # 关闭连接
    cur.close()
    con.close()

查询

# coding=utf-8
import pymysql

# 创建连接
con = pymysql.connect(host="localhost", user="root", password="root", port=3306, database="testdb")
# 创建游标对象
cur = con.cursor()
# 编写sql
sql = """
    select * from t_student
"""
try:
    # 执行
    cur.execute(sql)
    # 返回结果集,列表套元组
    student_list = cur.fetchall()
    # 返回一个结果
    # stu = cur.fetchone()
    # 遍历
    for student in student_list:
        print(student)
    print("执行成功")
except Exception as e:
    print(e)
    print("执行失败")
finally:
    # 关闭连接
    cur.close()
    con.close()

修改

# coding=utf-8
import pymysql

# 创建连接
con = pymysql.connect(host="localhost", user="root", password="root", port=3306, database="testdb")
# 创建游标对象
cur = con.cursor()
# 编写sql
sql = """
    update t_student set score=%s where sno=%s
"""
try:
    # 执行
    cur.execute(sql, (80.0, 1))
    # 提交事务
    con.commit()
    print("执行成功")
except Exception as e:
    print(e)
    # 回滚
    con.rollback()
    print("执行失败")
finally:
    # 关闭连接
    cur.close()
    con.close()

删除

# coding=utf-8
import pymysql

# 创建连接
con = pymysql.connect(host="localhost", user="root", password="root", port=3306, database="testdb")
# 创建游标对象
cur = con.cursor()
# 编写sql
sql = """
    delete from t_student where sno=%s
"""
try:
    # 执行
    cur.execute(sql, (1,))
    # 提交事务
    con.commit()
    print("执行成功")
except Exception as e:
    print(e)
    # 回滚
    con.rollback()
    print("执行失败")
finally:
    # 关闭连接
    cur.close()
    con.close()