施坤的博客
0%

python操作sqlite和mysql

发表于 分类于 阅读次数： Valine：

说明

本机环境，python3.7 ,win10 64

操作sqlite

  • 新建一个空的后缀名文件sample.db

  • 代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
import time
import sqlite3

#打开数据库
def open_db():
    #创建SQLite数据库
    con=sqlite3.connect(r"D:\project\pythonSql\sample.db")
    #创建表book:包含3列，id(主键，学号),name,tel
    # con.execute("create table if not exists book(id primary key,name,tel)")
    con.execute("create table if not exists book(id INTEGER PRIMARY KEY, name TEXT, tel TEXT, timer TEXT);")

    #创建游标对象
    cur=con.cursor()
    # 涉及到修改，删除等入库的操作必须用con.commit()提交事务最终完成
    # cur 主要是进行查询
    return con,cur

#查询全部信息
def show_all_db():
    print("******通讯录现有数据******")
    cur_1=open_db()[1]
    cur_1.execute("select id,name,tel from book")
    for row in cur_1:
        print(row)
        print(row[0])



#向数据库中添加内容
def add_db(name,tel):
    print("******数据添加功能******")
    cur_1=open_db()
    # cur_1[1].execute("insert into book(id,name,tel) values(?,?,?)",(id,name,tel))
    cur_1[1].execute("insert into book(name,tel) values(?,?)",(name,tel))
    cur_1[0].commit()
    print("******数据添加成功******")

#删除数据库中的内容
def delete_db(del_id):
    print("******数据删除功能******")
    cur_1=open_db()
    cur_1[1].execute("delete from book where id="+del_id)
    cur_1[0].commit()
    print("******数据删除成功******")
    show_all_db()
    #关闭游标对象
    cur_1[1].close()

#修改数据库中的内容
def alter_db(id,name,tel):
    print("******数据修改功能******")
    cur_1=open_db()
    #更新数据使用 SQL 语句中的 update
    cur_1[1].execute("update book set name = ? ,tel = ? where id ="+id,(name,tel))
    #游标事务提交
    cur_1[0].commit()
    show_all_db()
    cur_1[1].close()

#查询数据
def query_data(id):

    print("******数据查询功能******")
    cur_1=open_db()
    cur_1[1].execute("select id,name,tel from book where id ="+id)
    print("******查询结果如下******")
    for row in cur_1[1]:
        print(row)
    cur_1[1].close()



if __name__=="__main__":
    # add_db("你好", "185111")
    # show_all_db()
    # query_data("1")
    # alter_db("1", "我好", "hehe")
    # query_data("1")
    # delete_db("1")
    show_all_db()

  • 使用工具打开sqlitespy打开sqlite文件

image-20221209174201531

操作mysql

  • 本地搭建的mysql环境，省略
  • 使用HeidiSQL，连接数据库，创建表

image-20221209174414824

  • 设置自增主键

image-20221209174525798

  • 操作代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
import pymysql

dbinfo = {
    "host":"127.0.0.1",
    "user":"root",
    "password":"123456",
    "port":3306
}

class DB:
    def __init__(self,dbinfo):
        # self.db 只要修改表，必须用self.db.commit()进行事务提交，如果报错可以用self.db.rollback()进行回滚
        self.db = pymysql.connect(cursorclass = pymysql.cursors.DictCursor,
                                  **dbinfo)
        # self.cursor 用来查询
        self.cursor = self.db.cursor()

    def select(self,sql):
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        return result

    def execute(self,sql):

        self.cursor.execute(sql)
        self.db.commit()

        #     self.db.rollback()
        # try:
        #     self.cursor.execute(sql)
        #     self.db.commit()
        # except:
        #     self.db.rollback()

    def close(self):
        self.cursor.close()
        self.db.close()

if __name__ == '__main__':
    db = DB(dbinfo)
    #查询
    sql = "SELECT * from test.class"
    result = db.select(sql)
    print(result)

    #新增
    # sql2 = "INSERT into test.class(name) VALUES ('五年一班')"
    # db.execute(sql2)
    # sql = "SELECT * from test.class"
    # result = db.select(sql)
    # print(result)

    # 修改
    # sql2 = "update test.class set name='综艺一般' where id=6"
    # db.execute(sql2)
    # sql = "SELECT * from test.class"
    # result = db.select(sql)
    # print(result)

    # # 删除
    # sql2 = "delete from test.class where id=5"
    # db.execute(sql2)
    # sql = "SELECT * from test.class"
    # result = db.select(sql)
    # print(result)

Django mysql

  • django安装pip install Django

  • 创建项目及应用

1
2
3
4
5
# 创建应用
D:\project>django-admin startproject pysql
D:\project>cd pysql
# 创建应用下的一个项目
D:\project\mysite> python manage.py startapp myapi
  • django 配置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# pysql\pysql\setting.py
import pymysql
pymysql.version_info = (1, 4, 13, "final", 0) #指定版本
pymysql.install_as_MySQLdb()



INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'myapi' # 注册应用
]


// 设置默认数据库为mysql
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'test',  # 数据库名字
        'USER': 'root',  # 帐号
        'PASSWORD': '123456',  # 密码
        'HOST': '127.0.0.1',  # IP
        'PORT': '3306',  # 端口
    }
}
  • 设置model中的表字段
1
2
3
4
5
6
7
8
9
10
11
12
#pysql\myapi\model.py
from django.db import models


class class1(models.Model):
    id = models.IntegerField().primary_key
    name = models.CharField(max_length=128, unique=True)
    tel = models.CharField(max_length=256)
    c_time = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return self.name
  • 让model中的表结构在数据库中生成
1
2
python manage.py makemigrations # 让model生效
python manage.py migrate # 生成表
  • 编写views中代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
# pysql/myapi/views.py
import json

from django.core.exceptions import ObjectDoesNotExist
from django.http import JsonResponse
from django.views.decorators.csrf import csrf_exempt

from myapi.models import *

def query_all(request):
    res = Class1.objects.all()
    resp = []
    for i in res:
        resp.append({"id": i.id, "name": i.name, "tel": i.tel})
    return JsonResponse({"code": 1, "msg": "success", "data": resp})

@csrf_exempt
def delete(request):
    data = json.loads(request.body)
    id = data.get("id")
    if not id:
        return JsonResponse({"code": -1, "msg": "id must be fill"})
    try:
        entry = Class1.objects.get(id=id)
        if not entry:
            return JsonResponse({"code": -1, "msg": "no effect row"})
        entry.delete()
        return JsonResponse({"code": 1, "msg": "success"})

    except ObjectDoesNotExist:
        return JsonResponse({"code": -1, "msg": "no effect row"})

@csrf_exempt
def update(request):
    data = json.loads(request.body)
    name = data.get('name').strip()
    tel = data.get('tel').strip()
    ids = data.get("id")
    if not name and not ids:
        return JsonResponse({"code": -1, "msg": "error"})
    try:
        entry = Class1.objects.get(id=ids)
        if not entry:
            return JsonResponse({"code": -1, "msg": "no effect row"})
        entry.tel = tel
        entry.name = name
        entry.save()

        return JsonResponse({"code": 1, "msg": "success"})

    except ObjectDoesNotExist:
        return JsonResponse({"code": -1, "msg": "no effect row"})


def get_class(request, id):
    try:
        # entry = Class1.objects.filter(id=id)
        entry = Class1.objects.get(id=id)
        if not entry:
            result = {'code': -1, 'msg': 'data is null'}
            return JsonResponse(result)
        return JsonResponse({"code": 1, "msg": "success", "data": {"name": entry.name, "tel": entry.tel}})

    except ObjectDoesNotExist:
        result = {'code': -1, 'msg': 'no effect row'}
        return JsonResponse(result)

@csrf_exempt
def add(request):
    data = json.loads(request.body)
    name = data.get("name")
    tel = data.get("tel")
    if not name or not tel:
        res = {'code': -1, 'msg': 'name,tel must be fill'}
        return JsonResponse(res)
    Class1(name=name, tel=tel).save()
    res = {'code': 1, 'msg': 'success'}
    return JsonResponse(res)


  • 把views中的对外接口引用到url中
1
2
3
4
5
6
7
8
9
10
11
12
13
# pysql/myapi/urls.py

from django.conf.urls import url
from django.urls import path
from myapi import views

urlpatterns = [
    url(r'^query_all/', views.query_all),
    url(r'^delete/', views.delete),
    url(r'^update/', views.update),
    url(r'^add/', views.add),
    path('get_class/<int:id>/', views.get_class)
]

把myapi应用的路由url引用到pysql项目中

1
2
3
4
5
6
7
8
9
10
11
12
13
#pysql/pysql/urls.py
from django.conf.urls import url
from django.urls import path

from myapi import views

urlpatterns = [
    url(r'^query_all/', views.query_all),
    url(r'^delete/', views.delete),
    url(r'^update/', views.update),
    url(r'^add/', views.add),
    path('get_class/<int:id>/', views.get_class)
]
  • 运行Django
1
2
3
4
5
6
7
8
9
10
11
D:\project\pysql>python manage.py runserver
Watching for file changes with StatReloader
Performing system checks...

System check identified no issues (0 silenced).
December 09, 2022 - 19:15:56
Django version 3.1.3, using settings 'pysql.settings'
Starting development server at http://127.0.0.1:8000/
Quit the server with CTRL-BREAK.


  • 模拟前端访问后台接口代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

import requests

resp = requests.post("http://127.0.0.1:8000/myapi/add/", json={"name": "admin1", "tel": "123456"})
print(resp.text)
resp1 = requests.post("http://127.0.0.1:8000/myapi/update/", json={"id": 1,"name": "test", "tel": "7999"})
print(resp1.text)
resp2= requests.post("http://127.0.0.1:8000/myapi/update/", json={"id": 1,"name": "test", "tel": "1111"})
print(resp2.text)
resp3 = requests.post("http://127.0.0.1:8000/myapi/delete/", json={"id": 1})
print(resp3.text)

resp4 = requests.get("http://127.0.0.1:8000/myapi/get_class/1/")
print(resp4.text)

resp4 = requests.get("http://127.0.0.1:8000/myapi/query_all")
print(resp4.text)

  • 得到结果为：
1
2
D:\app\Python37\python.exe D:/project/pysql/test1.py
{"code": 1, "msg": "success", "data": [{"id": 5, "name": "admin1", "tel": "123456"}]}

Django sqlite

  • 由于接入mysql，配置已经改的差不多，只要修改，setting.py的默认数据库引用
1
2
3
4
5
6
7
8
# pysql/pysql/setting.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    }
}
  • 在pysql文件夹下新建一个空文件db.sqlite3
  • 让model的结构迁移到sqlite3数据库中
1
2
python manage.py makemigrations # 让model生效
python manage.py migrate # 生成表
  • 再次运行django
1
D:\project\pysql>python manage.py runserver
  • 模拟前端访问后台接口代码
1
2
3
4
import requests

resp = requests.post("http://127.0.0.1:8000/myapi/add/", json={"name": "admin1", "tel": "123456"})
print(resp.text)

image-20221209200159234