Python对数据库进行操作

Python对数据库进行操作

#!/usr/bin/env python
# @Time    : 2018/3/28 22:08
# @Author  : KiritoLiu
# @Contact : kiritoliuyhsky@gmail.com
# @Site    : 
# @File    : Python数据库操作.py
# @Software: PyCharm
#导入pymysql
import pymysql
#获取数据库连接
db = pymysql.connect(host="localhost",user="root",password="123456",db="stu",charset="utf8")
#创建游标对象
cursor = db.cursor()
#查询数据库,定义SQL语句
sql = "select * from stu where class= '%s'" % ("Python02")      #使用数据库语句进行数据库操作
try:        #防止在数据库操作时错误导致后续无法程序运行做出出错处理
    #执行数据库操作
    cursor.execute(sql)
    print("结果条数：",cursor.rowcount)
    if cursor.rowcount == 0:    #查询结果为0，即判断数据库查询条件出了问题
        print("SQL查询错误")
    #解析结果
    '''     
    #使用fetchone()   
    while True:
        data = cursor.fetchone()
        if data == None:        #最后无数据取出返回值为None
            break;
        print(data)
    '''
    #使用fetchall（）
    alist = cursor.fetchall()
    for vo in alist:
        print(vo)
    #fetchone()与fetchall()的用法一样，但是在输出数据的时候不一样
    #fetchone()是一条一条的取出数据库的内容，用while循环进行输出
    #fetchall()是一次性全部取出数据库的内容，用for循环进行输出
except Exception as err:
    print("SQL执行错误！错误：", err)
#关闭数据库
db.close()

数据库文件stu_stu.sql

-- MySQL dump 10.13  Distrib 5.7.12, for Win64 (x86_64)
--
-- Host: localhost    Database: stu
-- ------------------------------------------------------
-- Server version	5.7.17-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `stu`
--

DROP TABLE IF EXISTS `stu`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `stu` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `stu_no` int(3) NOT NULL,
  `name` varchar(20) NOT NULL,
  `sex` int(11) NOT NULL,
  `class` varchar(10) NOT NULL,
  `tel` varchar(11) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stu_no_UNIQUE` (`stu_no`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `stu`
--

LOCK TABLES `stu` WRITE;
/*!40000 ALTER TABLE `stu` DISABLE KEYS */;
INSERT INTO `stu` VALUES (1,1,'张三',1,'Python01','12345678910','1999-01-01'),(2,2,'李四',1,'Python01','18866668888','1996-12-06'),(3,3,'王五',1,'Python02','12345665410','1996-11-27'),(4,4,'赵六',2,'Python02','12332233210','1997-10-24'),(5,5,'qq01',2,'Python03','13322223322','1990-01-31'),(6,6,'qq02',1,'Python03','12288886666','1992-02-20'),(7,7,'qq03',2,'Python03','13579264801','2000-10-30'),(8,8,'uu01',1,'Python01','18898084886','1998-08-08'),(9,9,'uu02',2,'Python02','12022000022','1994-04-01'),(10,10,'aa',2,'Python02','18465489942','2005-05-05'),(11,11,'bb',1,'Python03','19264664234','1995-05-15');
/*!40000 ALTER TABLE `stu` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-03-29 14:43:33

stu表的属性

stu表的数据