MySQL学习记录②

今天上来就是活的添加表的内容, 非常开心:

动态创建python代码:

import pymysql
while True:
    user = input("用户名:")
    if(user == "e"):
        break
    psw = input("密码:")
    mobile = input("手机号:")

    # 1\ 连接mysql
    conn = pymysql.connect(host="127.0.0.1", port=3306, user = 'root', password = 'root123', charset = 'utf8',db="unicom")
    # cursor为连接创建完毕后,我们需要一个收发指令
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    # 2\ 发送指令
    # cursor.execute("insert into admin(username,password,mobile) values('yuanye','qeq','17621882188')")

    # 使用占位符来对文字内内容进行修改, 这样就活起来了!!!
    sql = "insert into admin(username,password,mobile) values(%s,%s,%s)"
    cursor.execute(sql, [user,psw,mobile])
    conn.commit()

    # 3\ 关闭连接
    cursor.close()
    conn.close()

查询数据库表中信息:

import pymysql

#1\ 连接mysql
conn = pymysql.connect(host="127.0.0.1", port=3306, user = 'root', password = 'root123', charset = 'utf8',db="unicom")
# cursor为连接创建完毕后,我们需要一个收发指令
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2\ 发送指令
sql = "select * from admin"
cursor.execute(sql)
# 通过fetchall来获取返回值
fetch = cursor.fetchall()
#我发现返回回来的是 列表中套字典的方式来返回^_^
for i in fetch:
    print(i)


# 3\ 关闭连接
cursor.close()
conn.close()

删除数据库中信息:

import pymysql

#1\ 连接mysql
conn = pymysql.connect(host="127.0.0.1", port=3306, user = 'root', password = 'root123', charset = 'utf8',db="unicom")
# cursor为连接创建完毕后,我们需要一个收发指令
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

iddd=5

# 2\ 发送指令
sql = "delete from admin where id=%s"
cursor.execute(sql,iddd)
conn.commit()

# 3\ 关闭连接
cursor.close()
conn.close()

修改数据库中表的信息:

import pymysql

#1\ 连接mysql
conn = pymysql.connect(host="127.0.0.1", port=3306, user = 'root', password = 'root123', charset = 'utf8',db="unicom")
# cursor为连接创建完毕后,我们需要一个收发指令
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

mob="114514"

# 2\ 发送指令
sql = "update admin set mobile=%s where id >5"
cursor.execute(sql,mob)
conn.commit()

# 3\ 关闭连接
cursor.close()
conn.close()