python操作mysql
python操作数据库
- #mysql驱动程序安装
1.Mysql-python:也就是MySQLdb.C语言操作mysql数据库的简单封装.遵循Python DB API v2,只支持python2,不支持python3
2.mysqlclient:是MySQL-python的另一个分支,升级版,修复了一些bug,支持python3.
3.pymysql:纯python编写的驱动.python兼容性好,效率较低.
4.MySQL-Connector/python版:MySQL官方推出的纯python开发的连接MySQL驱动.兼容性好,效率较差.
#使用mysqlclient
安装 pip install mysqlclient
windows安装报错的话,去 https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient 下载对应版本的包安装即可
MySQL-python, a Python database API 2.0 interface for the MySQL database Consider mysqlclient, a Python 3 compatible fork of MySQL-python. MySQL_python‑1.2.5‑cp27‑none‑win32.whl MySQL_python‑1.2.5‑cp27‑none‑win_amd64.whl Mysqlclient, a fork of the MySQL-python interface for the MySQL database. mysqlclient‑1.3.13‑cp27‑cp27m‑win32.whl mysqlclient‑1.3.13‑cp27‑cp27m‑win_amd64.whl mysqlclient‑1.3.13‑cp34‑cp34m‑win32.whl mysqlclient‑1.3.13‑cp34‑cp34m‑win_amd64.whl mysqlclient‑1.3.13‑cp35‑cp35m‑win32.whl mysqlclient‑1.3.13‑cp35‑cp35m‑win_amd64.whl mysqlclient‑1.3.13‑cp36‑cp36m‑win32.whl mysqlclient‑1.3.13‑cp36‑cp36m‑win_amd64.whl mysqlclient‑1.3.13‑cp37‑cp37m‑win32.whl mysqlclient‑1.3.13‑cp37‑cp37m‑win_amd64.whl
python db api 规范下cursor对象常用接口:
1.description
2.rowcount
3.close
4.execute
5.fetchall\fetchmany\fetchone
from django.template.loader import render_to_string from django.http import HttpResponse from django.shortcuts import render,redirect,reverse from datetime import datetime from django.db import connection # def index(request): # cursor = connection.cursor() # # cursor.execute("insert into book(id,name,author) values(null,'三国演义','罗贯中')") # cursor.execute('select * from book') # # rows = cursor.fetchall() # row = cursor.fetchone() # print(row) # return render(request,'index.html') def get_cursor(): return connection.cursor() def index(request): cursor = get_cursor() cursor.execute('select * from book') rows=cursor.fetchall() print(rows) #((id,name,author),(id,name,author),...) return render(request,'index.html',context={'rows':rows}) def add_book(request): if request.method == 'GET': return render(request,'add_book.html') else: name = request.POST.get('name') author = request.POST.get('author') cursor = get_cursor() cursor.execute("insert into book(id,name,author) values(null,'%s','%s')" %(name,author)) return redirect(reverse('index')) def detail(request,book_id): cursor = get_cursor() cursor.execute("select * from book where id=%s" %book_id) book = cursor.fetchone() print(book) return render(request,'detail.html',context={'book':book}) def delete(request): if request.method == "POST": book_id = request.POST.get('name') cursor = get_cursor() cursor.execute("delete from book where id=%s" %book_id) return HttpResponse('删除成功')
from django.urls import path from . import views urlpatterns = [ path('', views.index,name='index'), path('add_book/',views.add_book,name='add_book'), path('detail/<int:book_id>',views.detail,name='detail'), path('delete/',views.delete,name='delete'), ]
*{ margin: 0; padding: 0; } .nav{ overflow: hidden; height: 65px; background-color: aqua; } .nav li{ float: left; margin: 0 30px; list-style: none; line-height: 65px; } .nav li a{ color: black; text-decoration: none; } .nav li a:hover{ color: blue; }
{% load static %}
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<link href="{% static 'base.css' %}" rel="stylesheet">
</head>
<body>
<div>
<ul class="nav">
<li><a href="/">首页</a></li>
<li><a href="{% url 'add_book' %}">发布图书</a> </li>
</ul>
</div>
<div class="content">
{% block content %}
{% endblock %}
</div>
</body>
</html>
{% extends 'base.html' %}
{% block content %}
"这是首页"
<table>
<thead>
<tr>
<td>序号</td>
<td>书名</td>
<td>作者</td>
</tr>
</thead>
<tbody>
{% for row in rows %}
<tr>
<td>{{ forloop.counter }}</td>
<td><a href="{% url 'detail' book_id=row.0 %}" >{{ row.1 }}</a></td>
<td>{{ row.2 }}</td>
</tr>
{% endfor %}
</tbody>
</table>
{% endblock %}
{% extends 'base.html' %}
{% block content %}
add_book...
<form method="post" action="{% url 'add_book' %}">
<table>
<tbody>
<tr>
<td>书名:</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>作者:</td>
<td><input type="text" name="author"></td>
</tr>
<tr>
<td></td>
<td><input type="submit" value="提交"></td>
</tr>
</tbody>
</table>
</form>
{% endblock %}
{% extends 'base.html' %}
{% block content %}
<p>书名:{{ book.1 }}</p>
<p>作者:{{ book.2 }}</p>
<form method="post" action="{% url 'delete' %}">
<input type="hidden" name="name" value={{ book.0 }}>
<input type="submit" value="删除">
</form>
{% endblock %}
{% url 'index' book_id=book.1 %}
{% static 'base.css' %}
return redirect(reverse('index'))
