85.mysql数据库的使用例子

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)

    # 注意: 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 operate_table(connect):
"""增删改"""
# 1.增
# sql_str = '''
# insert into tb_student
# (stuname, stusex, stuage, setutel)
# values
# ('张三', 1, 30, '17823736452'),
# ('stu1', 0, 28, '16728729739');
# '''
print('=========插入学生==========')
sql_str = 'insert into tb_student (stuname, stusex, stuage, setutel) 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 create_table(connect):
"""创建表"""
with connect.cursor() as cursor:
# 1.=========创建学生表=========
try:
sql_str = '''
create table tb_student
(
stuid int auto_increment,
stuname varchar(10) not null,
stuage int,
stusex bit default 1,
setutel varchar(11),
primary key (stuid)
);
'''
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():
# 1.建立连接
con = pymysql.connect(
host='localhost',
user='root',
password='yuting123456',
port=3306,
charset='utf8',
autocommit=True
)

# 2.切换数据库
with con.cursor() as cursor:
    cursor.execute('use pyschool;')

# 3.创建表
create_table(con)

# 4.操作表
# operate_table(con)

# 5.查询数据
query_table(con)

if name == 'main':
main()