数据的增删改查

 # 1.建立连接
    connect_obj = pymysql.connect(
            host='localhost',
            user='root',
            password='yuting123456',
            database='school',
            port=3306,
            charset='utf8',
            autocommit=True)
    # 2.获取游标
    with connect_obj.cursor() as cursor_obj:
        # 1)创建数据库
        # base_name = input('需要创建的数据的名字(必须是英文):')
        # sqlstr = "create database if not exists %s; " % base_name
        # result = cursor_obj.execute(sqlstr)
        # print(result)

        # 2)删除数据库
        # base_name = input('需要创建的数据的名字(必须是英文):')
        # cursor_obj.execute('drop database if exists %s;' % base_name)

        # 3)使用数据库
        cursor_obj.execute('use python1;')

        # 4)创建表
        # cursor_obj.execute("""
        # create table if  not exists tb_person
        # (
        #     pid int not null,
        #     pname varchar(20) not null,
        #     page int default 0,
        #     gender bit default 1,
        #     PRIMARY key(pid)
        # );
        # """)

        # 5)删除表
        # cursor_obj.execute('drop table if exists tb_person;')

        # 6)增删改
        # cursor_obj.execute("""
        # insert into tb_person
        # (pid, pname, page, gender)
        # values
        # (13, '张三1', 28, 1),
        # (14, '李四1', 20, 0);
        # """)
        # cursor_obj.execute('delete from tb_person where pid=4;')
        cursor_obj.execute('update tb_person set pname="小明" where pname like "%三%";')

        # 7)查: 通过游标对象执行查询语句后，查询结果是保存在游标对象中
        result = cursor_obj.execute('select pname,pid,page from tb_person;')
        print(result)

        # 游标对象.fetchall() - 获取查询结果，
        # 结果是个元祖，元祖中的元素是一个小元祖代表的是每一条记录；小元祖中的元素是每一条记录中每个字段对应的值
        # print(cursor_obj.fetchall())
        # 通过游标获取结果的时候，取一个就少一个
        all_person = cursor_obj.fetchall()
        for p in all_person:
            print('姓名:', p[0])
            print('id:', p[1])
            print('年龄:', p[2])

        print(cursor_obj.fetchone())   # None
        print(cursor_obj.fetchall())   # ()



    # 关闭连接
    connect_obj.close()

对象存储

import pymysql


class Student:
    def __init__(self, name, age=0, gender=1, addr='', tel=''):
        self.name = name
        self.age = age
        self.gender = gender
        self.addr = addr
        self.tel = tel

class Dog:
    def __init__(self, name, color):
        self.name = name
        self.color = color


all_students = [
    Student('小明', 20, 1, '四川成都', '18723674034'),
    Student('憨猜猜', 30, 1, '四川德阳', '13723674034'),
    Student('张忌', 25, 1, '重庆', '16672839483'),
    Student('刘亦菲', 22, 0, '湖南长沙', '13423849322')
]


# 插入对象的时候按照对象的类型自动选择表，如果对应的表不存在就自动创建表
# 表的规则的： Student类的对象 tb_Student表   Dog类对象 tb_Dog表中
def inser_object_database(obj, cursor):
    # 1.获取对象对应的类型名
    class_name = obj.__class__.__name__
    table_name = 'tb_'+class_name

    # 获取所有的字段，以逗号隔开
    keys = ','.join(obj.__dict__.keys())   #  obj.__dict__.keys() : [name, age, gender...]
    # keys = name,age,gender,...

    # 获取所有的值，以逗号隔开
    values = ''
    for item in obj.__dict__.values():
        if isinstance(item, str):
            values += '"%s"' % item
        else:
            values += str(item)
        values += ','

    values = values[:-1]
    print(values)


    #2. 插入数据
    # insert into 表名 (字段1,字段2,字段3..) value (值1,值2,值3..);
    # insert into tb_Student (Stuid,name,age..) value (1,'小明',18..);
    insert_sql = 'insert into %s (%sid,%s) values (null,%s);' % (table_name,table_name[3:6], keys, values)
    print(insert_sql)
    try:
        cursor.execute(insert_sql)
    except pymysql.err.ProgrammingError:
        field = ''   # 字段 字段类型, 字段 字段类型
        for key in obj.__dict__:
            field += key+' '

            value = obj.__dict__[key]
            if isinstance(value, str):
                field += 'text'
            else:
                field += 'float'

            field += ','

        field = field[:-1]

        #  创建表再插入
        create_sql = 'create table if not exists %s(%sid int not null auto_increment,%s, primary key(%sid));' % (table_name, table_name[3:6],field,table_name[3:6])
        print(create_sql)
        cursor.execute(create_sql)
        cursor.execute(insert_sql)


def main():
    # 建立连接
    connect_obj = pymysql.connect(host='localhost',
                    user='root',
                    password='yuting123456',
                    charset='utf8',
                    autocommit=True,
                    port=3306)

    # 获取游标
    # cursor(游标类型) - 游标类型默认是None, 表示查询的时候结果是以元祖的形式返回;
    #                   pymysql.cursors.DictCursor， 表示查询的时候结果是以字典的形式返回;
    with connect_obj.cursor(pymysql.cursors.SSDictCursor) as cursor_obj:
        # 创建数据库
        cursor_obj.execute('create database if not exists python2 default charset utf8;')
        cursor_obj.execute('use python2;')
        # 插入数据
        # inser_object_database(Dog('大黄', 'yellow'), cursor_obj)
        # inser_object_database(Student('红红', 18),cursor_obj)

        # 获取数据
        cursor_obj.execute('select * from tb_Student;')
        re = cursor_obj.fetchall()
        for studict in re:
            del_key = 'Stuid'
            del studict[del_key]
            stu = Student(**studict)   # Student(Stuid=1,name='小明',age=20,gender=1,addr=...)
            print(stu.name)


    connect_obj.close()