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

 查看:

 

posted @ 2019-07-16 19:48  慢行厚积  阅读(307)  评论(0编辑  收藏  举报