python-learning-第二季-数据库编程
https://www.bjsxt.com/down/8468.html
代码实现：
#coding:utf-8 #导入模块 import sqlite3 #创建connect连接 con = sqlite3.connect('./sqlitedb/demo.db') print(con) #查看连接是否成功生成,<sqlite3.Connection object at 0x10ae6b3b0> #创建游标对象 cur = con.cursor() #编写创建表的sql语句 sql = '''create table t_person( pno INTEGER primary key autoincrement, pname VARCHAR not null, age INTEGER )''' try: #执行说sql语句 cur.execute(sql) print('创建表成功') except Exception as e: print(e) finally: #关闭游标 cur.close() #关闭连接 con.close()
返回：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py <sqlite3.Connection object at 0x106ded3b0> 创建表成功
操作sqlite3数据库插入数据
#coding:utf-8 #导入模块 import sqlite3 #创建connect连接 con = sqlite3.connect('./sqlitedb/demo.db') print(con) #查看连接是否成功生成,<sqlite3.Connection object at 0x10ae6b3b0> #创建游标对象 cur = con.cursor() #pno是自增长的，可不赋值 #（?,?）表示占位符，在执行时会传入 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()
返回：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py <sqlite3.Connection object at 0x10c6ac3b0> 插入成功 Process finished with exit code 0
插入多条数据：
#coding:utf-8 #导入模块 import sqlite3 #创建connect连接 con = sqlite3.connect('./sqlitedb/demo.db') print(con) #查看连接是否成功生成,<sqlite3.Connection object at 0x10ae6b3b0> #创建游标对象 cur = con.cursor() #pno是自增长的，可不赋值 #（?,?）表示占位符，在执行时会传入 sql = 'insert into t_person(pname, age) values (?,?)' try: #执行插入多条sql语句 cur.executemany(sql, [('小李', 18), ('小明', 22), ('小雨', 32)]) #提交事务 con.commit() print('插入多条数据成功') except Exception as e: print(e) #回滚 con.rollback() print('插入多条数据失败') finally: #关闭游标 cur.close() #关闭连接 con.close()
返回：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py <sqlite3.Connection object at 0x1019fd3b0> 插入多条数据成功 Process finished with exit code 0
操作sqlite3数据库查询数据
#coding:utf-8 #导入模块 import sqlite3 #创建connect连接 con = sqlite3.connect('./sqlitedb/demo.db') print(con) #查看连接是否成功生成,<sqlite3.Connection object at 0x10ae6b3b0> #创建游标对象 cur = con.cursor() sql = 'select * from t_person' try: #执行查询所有sql内容 cur.execute(sql) #获取所有数据 person_all = cur.fetchall() #返回的是数组 #遍历输出 for p in person_all: print(p) except Exception as e: print(e) print('查询失败') finally: #关闭游标 cur.close() #关闭连接 con.close()
返回：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py <sqlite3.Connection object at 0x105f903b0> (1, '张三', 23) (2, '小李', 18) (3, '小明', 22) (4, '小雨', 32) Process finished with exit code 0
查询一条：
#coding:utf-8 #导入模块 import sqlite3 #创建connect连接 con = sqlite3.connect('./sqlitedb/demo.db') print(con) #查看连接是否成功生成,<sqlite3.Connection object at 0x10ae6b3b0> #创建游标对象 cur = con.cursor() sql = 'select * from t_person' try: #执行查询所有数据sql语句 cur.execute(sql) #获取一条数据 person = cur.fetchone() print(person) except Exception as e: print(e) print('查询失败') finally: #关闭游标 cur.close() #关闭连接 con.close()
返回：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py <sqlite3.Connection object at 0x1092793b0> (1, '张三', 23) Process finished with exit code 0
修改数据：
#coding:utf-8 #导入模块 import sqlite3 #创建connect连接 con = sqlite3.connect('./sqlitedb/demo.db') print(con) #查看连接是否成功生成,<sqlite3.Connection object at 0x10ae6b3b0> #创建游标对象 cur = con.cursor() sql = 'update t_person set pname=? where pno=?' try: #执行修改sql语句 cur.execute(sql,('张四',1)) #提交事务 con.commit() print('修改成功') except Exception as e: print(e) #回滚 con.rollback() print('修改失败') finally: #关闭游标 cur.close() #关闭连接 con.close()
返回：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py <sqlite3.Connection object at 0x10d4bb3b0> 修改成功 Process finished with exit code 0
查询可见修改成功：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py <sqlite3.Connection object at 0x10ab113b0> (1, '张四', 23) Process finished with exit code 0
删除数据：
#coding:utf-8 #导入模块 import sqlite3 #创建connect连接 con = sqlite3.connect('./sqlitedb/demo.db') print(con) #查看连接是否成功生成,<sqlite3.Connection object at 0x10ae6b3b0> #创建游标对象 cur = con.cursor() sql = 'delete from t_person where pno=?' try: #执行删除sql语句 cur.execute(sql,(1,)) #元组仅有一个参数，必需有面加逗号，保证参数类型为元组 #提交事务 con.commit() print('删除成功') except Exception as e: print(e) #回滚 con.rollback() print('删除失败') finally: #关闭游标 cur.close() #关闭连接 con.close()
返回：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py <sqlite3.Connection object at 0x101ebb3b0> 删除成功 Process finished with exit code 0
此时查看第一条数据为：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py <sqlite3.Connection object at 0x101e623b0> (2, '小李', 18) Process finished with exit code 0
MySQL数据库的下载
我已经安装好了，这里省略，去官网下载即可，端口默认3306
安装对应的模块PyMySQL
以为之前的deeplearning中的可用在这，发现不行，所以就重新下载了：
(venv) (base) userdeMBP:python3 user$ pip install PyMySQL Collecting PyMySQL Using cached https://files.pythonhosted.org/packages/ed/39/15045ae46f2a123019aa968dfcba0396c161c20f855f11dea6796bcaae95/PyMySQL-0.9.3-py2.py3-none-any.whl Installing collected packages: PyMySQL Successfully installed PyMySQL-0.9.3
我已经下载好了
操作MySQL数据库创建表
先是创建了数据库python_db:
首先先连接：
#导入pymysql import pymysql #创建连接 con = pymysql.connect(host='localhost', user='root', password='user78', database='python_db', port=3306) print(con)
成功连接：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py <pymysql.connections.Connection object at 0x103d3f6a0> Process finished with exit code 0
#导入pymysql import pymysql #创建连接 con = pymysql.connect(host='localhost', user='root', password='user78', database='python_db', port=3306) #创建游标对象 cur = con.cursor() #编写创建表的sql，score总共三位，小数点占一位 sql = ''' create table t_student( sno int primary key auto_increment, sname varchar(30) not null, age int(2), score float(3,1) ) ''' try: #执行创建表的sql cur.execute(sql) print('创建成功') except Exception as e: print(e) print('创建失败') finally: #关闭游标 cur.close() #关闭连接 con.close()
返回：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py 创建成功 Process finished with exit code 0
查看成功创建：
插入数据：
#导入pymysql import pymysql #创建连接 con = pymysql.connect(host='localhost', user='root', password='user78', database='python_db', port=3306) #创建游标对象 cur = con.cursor() #插入数据 sql = 'insert into t_student(sname, age, score) values(%s, %s, %s)' try: #执行创建表的sql cur.execute(sql, ('王一', 18, 60)) #提交事务 con.commit() print('插入成功') except Exception as e: print(e) con.rollback() print('插入失败') finally: #关闭游标 cur.close() #关闭连接 con.close()
返回：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py 插入成功 Process finished with exit code 0
查看：
插入多条数据：
#导入pymysql import pymysql #创建连接 con = pymysql.connect(host='localhost', user='root', password='user78', database='python_db', port=3306) #创建游标对象 cur = con.cursor() #插入数据 sql = 'insert into t_student(sname, age, score) values(%s, %s, %s)' temp = [('王二', 19, 70), ('王三', 20, 80)] try: #执行创建表的sql cur.executemany(sql, temp) #提交事务 con.commit() print('插入成功') except Exception as e: print(e) con.rollback() print('插入失败') finally: #关闭游标 cur.close() #关闭连接 con.close()
返回：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py 插入成功 Process finished with exit code 0
查看：
查询数据
#导入pymysql import pymysql #创建连接 con = pymysql.connect(host='localhost', user='root', password='user78', database='python_db', port=3306) #创建游标对象 cur = con.cursor() #查询多个数据 sql = 'select * from t_student where age >= 19' try: #执行创建表的sql cur.execute(sql) students = cur.fetchall() print(students) print('查询成功') except Exception as e: print(e) print('查询失败') finally: #关闭游标 cur.close() #关闭连接 con.close()
返回：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py ((2, '王二', 19, 70.0), (3, '王三', 20, 80.0)) 查询成功 Process finished with exit code 0
查询：
#导入pymysql import pymysql #创建连接 con = pymysql.connect(host='localhost', user='root', password='user78', database='python_db', port=3306) #创建游标对象 cur = con.cursor() #查询多个数据 sql = 'select * from t_student where age >= 19' try: #执行创建表的sql cur.execute(sql) students = cur.fetchall() for student in students: print(student[0], end = ' ,') print(student[1], end=' ,') print(student[2], end=' ,') print(student[3]) except Exception as e: print(e) print('查询失败') finally: #关闭游标 cur.close() #关闭连接 con.close()
返回：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py 2 ,王二 ,19 ,70.0 3 ,王三 ,20 ,80.0 Process finished with exit code 0
查询一个数据：
#导入pymysql import pymysql #创建连接 con = pymysql.connect(host='localhost', user='root', password='user78', database='python_db', port=3306) #创建游标对象 cur = con.cursor() #查询一个数据 sql = 'select * from t_student where age >= 19' try: #执行创建表的sql cur.execute(sql) student = cur.fetchone() print(student) except Exception as e: print(e) print('查询失败') finally: #关闭游标 cur.close() #关闭连接 con.close()
返回：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py (2, '王二', 19, 70.0) Process finished with exit code 0
更新数据：
#导入pymysql
import pymysql
#创建连接
con = pymysql.connect(host='localhost', user='root', password='user78', database='python_db', port=3306)
#创建游标对象
cur = con.cursor()
#查询一个数据
sql = 'update t_student set sname=%s where sno =%s'
try:
#执行创建表的sql
cur.execute(sql, ('王五', 1))
#提交事务
con.commit()
print('更新成功')
except Exception as e:
print(e)
con.rollback()
print('更新失败')
finally:
#关闭游标
cur.close()
#关闭连接
con.close()
返回：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py 更新成功 Process finished with exit code 0
查看：
删除数据：
#导入pymysql
import pymysql
#创建连接
con = pymysql.connect(host='localhost', user='root', password='user78', database='python_db', port=3306)
#创建游标对象
cur = con.cursor()
#查询一个数据
sql = 'delete from t_student where sname =%s'
try:
#执行创建表的sql
cur.execute(sql, ('王五',))
#提交事务
con.commit()
print('删除成功')
except Exception as e:
print(e)
con.rollback()
print('删除失败')
finally:
#关闭游标
cur.close()
#关闭连接
con.close()
返回：
/Users/user/PycharmProjects/python3/venv/bin/python /Users/user/PycharmProjects/python3/test.py 更新成功 Process finished with exit code 0
查看：
