Python操作数据库

Python操作数据库

import pymysql

# 1.连结MySQL
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',password='123456',charset='utf8',db='unicom')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2.发送指令
# sql="insert into admin(username,password,mobile) values(%s,%s,%s)"
# cursor.execute(sql,['小胡','123456','15874589658'])

# sql="insert into admin(username,password,mobile) values(%(n1)s,%(n2)s,%(n3)s)"
# cursor.execute(sql,{'n1':'小胡','n2':'123456','n3':'15878987485'})

conn.commit()
# 3.关闭
cursor.close()
conn.close()

动态创建数据库

import pymysql

while True:
    user = input("用户名:")
    if user.upper() == 'Q':
        break
    pwd = input("密码:")
    mobile = input("手机号:")
    # 1.连结MySQL
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', charset='utf8', db='unicom')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    # 2.发送指令(千万不要用字符串格式化去做SQL的拼接，会有安全隐患出现SQL注入)
    sql = "insert into admin(username,password,mobile) values(%s,%s,%s)"
    cursor.execute(sql, [user, pwd, mobile])

    conn.commit()
    # 3.关闭
    cursor.close()
    conn.close()

查询数据

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2.发送指令
cursor.execute("select* from admin where id > %s",[2,])
data_list=cursor.fetchall()		#得到列表里面套字典
for row_dict in data_list:
    print(row_dict)
    
#res=cursor.fetchone()		获取符合条件的第一条数据
#print(res)
# 3.关闭
cursor.close()
conn.close()

删除数据

import pymysql
# 1.连结MySQL
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2.发送指令
cursor.execute('delete from admin where id=%s',[3,])
conn.commit()
# 3.关闭
cursor.close()
conn.close()

修改数据

import pymysql
# 1.连结MySQL
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2.发送指令
cursor.execute('update admin set mobile=%s where id=%s',['78541527895',4])
conn.commit()
# 3.关闭
cursor.close()
conn.close()

强调:

• 在进行新增、删除、修改时,一定要记得commit,不然数据库不变