创建数据库实例

import pymysql

db= pymysql.connect(host="localhost",user="root",password="1234",charset="utf8")

cursor=db.cursor()

cursor.execute("create database 数据库名 default charset=utf8;")

db= pymysql.connect(host="localhost",user="root",password="000000",charset="utf8",db="数据库名")

cursor=db.cursor()

cursor.execute('''

create table 表名(

    SNO varchar(20),

    SNAME char(5) ,

    SAGE int(5),

    SSEX char(5),

    SACADEMY char(10),

    SGRADE char(10),

    SCLASS char(5),

    primary key(SNO)

    )default charset=utf8;

''')

cursor.execute('''

insert into 表名 values('2016081111','张三','20','男','软件工程学院','2016','3'),('2016061111','王杰','21','男','网络工程学院','2016','3'),

    ('2016071113','周顺','19','男','大气科学学院','2016','3'),('2017081180','李伟','20','男','软件工程学院','2017','2'),('2016081201','王丽','20','女','软件工程学院','2016','5');

db.commit()

cursor.execute('select * from 表名')

for i in cursor.fetchall():

    print(i)

cursor.close()

db.close()

功能搭建

import pymysql

DB=None

class Student():

    def select(self):

        cursor=DB.cursor()

        try:

            SQL="select * from student"

            cursor.execute(SQL)

            for i in cursor.fetchall():

                print(i)

        except Exception as e:

            print("报错",e)

        finally:

            cursor.close()

    #查询

    def Cha(self):

        try:

            cursor = DB.cursor()

            a=input("请输入学号:")

            cursor.execute("select * from student where SNO=%s"%a)

            if cursor.fetchall():

                SQL = "select * from student where SNO=%s"%a

                cursor.execute(SQL)

                for i in cursor.fetchall():

                    print(i)

            else:

                print("\n查询失败\n")

        except Exception as e:

            print("报错",e)

        finally:

            cursor.close()

    #添加

    def add(self):

        cursor = DB.cursor()

        a = input("输入添加的学号:")

        b = input("输入添加的姓名:")

        c = input("输入添加的年龄:")

        d = input("输入添加的性别:")

        e = input("输入添加的院系:")

        f = input("输入添加的年级:")

        g = input("输入添加的班级:")

        try:

            SQL = "insert into student values ('%s','%s','%s','%s','%s','%s','%s');" % (a, b, c, d, e, f, g)

            cursor.execute(SQL)

            DB.commit()

            print("\n添加成功\n")

        except Exception as e:

            print("报错",e)

        finally:

            cursor.close()

     #修改

    def modify(self):

        cursor = DB.cursor()

        a = input("输入您要修改的学号:")

        b = input("姓名修改为:")

        c = input("年龄修改为:")

        d = input("性别修改为:")

        e = input("学院修改为:")

        f = input("年级修改为:")

        g = input("班级修改为:")

        try:

            SQL = "update student set SNAME='%s',SAGE='%s',SSEX='%s',SACADEMY='%s',SGRADE='%s',SCLASS='%s' where SNO='%s';" % (b,c,d,e,f,g,a)

            cursor.execute(SQL)

            DB.commit()

            print("\n修改成功\n")

            for i in cursor.fetchall():

                print(i)

        except Exception as e:

            print("报错",e)

        finally:

            cursor.close()

     #删除

    def drop(self):

        try:

            cursor = DB.cursor()

            a=input("输入您要删除的学生学号:")

            cursor.execute("select * from student where SNO=%s"%a)

            if cursor.fetchall():

                SQL="delete from student where SNO=%s" % a

                cursor.execute(SQL)

                DB.commit()

                print("\n删除成功\n")

                for i in cursor.fetchall():

                    print(i)

            else:

                print("\n删除失败,可能没有该学号\n")

        except Exception as e:

            print("报错",e)

        finally:

            cursor.close()

def main():

    global DB

    student=Student()

    DB=pymysql.connect(host="localhost",user="root",password="1234",charset="utf8",db="zzz")

    choose=input("请选择您的操作\n"

          "1.查询所有学生的信息\n"

          "2.按学号查询学生的信息\n"

          "3.添加学生信息\n"

          "4.修改学生信息\n"

          "5.删除学生信息\n"

          "6.退出\n")

    while choose != "6":

        if choose == "1":

            student.select()

        elif choose == "2":

            student.Cha()

        if choose == "3":

            student.add()

        if choose == "4":

            student.modify()

        if choose == "5":

            student.drop()

        choose = input("请选择您的操作\n"

                      "1.查询所有学生的信息\n"

                      "2.按学号查询学生的信息\n"

                      "3.添加学生信息\n"

                      "4.修改学生信息\n"

                      "5.删除学生信息\n"

                      "6.退出\n")

    else:

        print("\n您已退出学生信息表\n")

main()

巴特西

Python之创建数据库及功能示例样本

创建数据库实例

功能搭建

最新文章

热门文章