python中数据库基本操作

1.导入 pymysql包，用于支持mysql数据库操作

      所有的mysql语句都是在游标对象.execute('sql语句')中执行

2.和数据库建立连接

3.切换数据库

4.操作表

5.查询

import pymysql


def query_table(connect):
    # 注意：执行查询的sql语句，查询结果保存在游标对象中的
    # 游标对象.fetchall()
    sql_str = 'select * from tb_student;'
    with connect.cursor(pymysql.cursors.DictCursor) as cursor:
        result = cursor.execute(sql_str)
        print(result, cursor)
        print(cursor.fetchall())

        # 注意:cursor中的查询结果，取一个就少一个
        # 1.游标对象.fetchall()  -   获取当前查询的所有结果
        # all_result = cursor.fetchall()
        # print('查询结果的个数：',len(all_result))
        # for dic in all_result:
        #     print(dic['stuname'])
        #
        # all_result2 = cursor.fetchall()
        # print(all_result2)

        # 2.游标对象.fetchone() -   获取当前查询中的一条数据
        print(cursor.fetchone())
        print(cursor.fetchone())

        # 3.游标对象.fetchmany(size)    - 获取当前查询中指定条数的数据
        print(cursor.fetchmany(2))


def opreate_table(connect):
    """ 增删改 """
    # 1.增
    # sql_str = '''
    #     insert into  tb_student (stuname,stusex,stuage,stutel)
    #     values
    #     ('张三',1,30,'17823736452');
    # '''
    print('=========插入学生============')
    sql_str = 'insert into  tb_student (stuname,stusex,stuage,stutel)values %s;'
    str2 = ''
    while True:
        name = input('请输入名字：')
        sex = int(input('请输入性别（0/1）：'))
        age = int(input('请输入年龄：'))
        tel = input('请输入电话号码：')
        value = input('是否继续添加(y/n):')
        str2 += "('%s',%d,%d,'%s')," % (name, sex, age, tel)
        if value == 'n':
            print(str2[:-1])
            sql_str = sql_str % str2[:-1]
            print(sql_str)
            break

    with connect.cursor() as cursor:
        cursor.execute(sql_str)


def use_ddl(connect):
    with connect.cursor() as cursor:
        # 1.============== 创建学生表=====================
        try:
            sql_str = '''
            create table tb_student
            (
                stuid int auto_increment primary key,
                stuname varchar(10) not null,
                stuage int,
                stusex bit default 1,
                stutel varchar(11)
            );
            '''
            cursor.execute(sql_str)
        except:
            pass

        # # 自定制表
        # table_name = input('输入表名：')
        # pre = table_name[:3]
        # cnames = []
        # while True:
        #     cname = input('请输入字段名(q-退出):')
        #     if cname == 'q':
        #         break
        #     cnames.append(pre + cname + ' text,')
        #
        # str1 = '''
        #     create table if not exists tb_%s
        #     (
        #         %sid int auto_increment,
        #         %s
        #         primary key (%sid)
        #     );
        # '''
        # sql_str = str1 % (
        #     table_name,
        #     table_name[:3],
        #     ' '.join(cnames),
        #     table_name[:3]
        # )
        # print(sql_str)
        # cursor.execute(sql_str)


def main():
    con = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        port=3306,
        charset='utf8',
        autocommit=True
    )
    # 2.切换数据库
    with con.cursor() as cursor:
        cursor.execute('use pyschool;')

    # 3.创建表
    use_ddl(con)

    # 4.操作表
    # opreate_table(con)

    # 5.查询
    query_table(con)


if __name__ == '__main__':
    main()