Python之MySQL数据库增删改查操作

最新推荐文章于 2022-08-31 10:09:24 发布

福海鑫森

最新推荐文章于 2022-08-31 10:09:24 发布

阅读量4.4k

点赞数

分类专栏： docker 文章标签： python mysql 数据库

本文链接：https://blog.csdn.net/chen3888015/article/details/7037204

版权

docker 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

Python之连接数据库

pmy1.py

# -*-coding:utf-8-*-

import pymysql

# 打开数据库连接

conn = pymysql.connect(host='sh-cdsmk.sql.tencentcdb.com',port=61122,user='root',passwd='1ste2yra@!55',db='by1')

# 使用cursor()方法创建一个游标对象
cursor = conn.cursor()

# 使用execute()方法执行SQL查询
cursor.execute('SELECT VERSION()')

# 使用fetchone()方法获取单条数据
data = cursor.fetchone()

# 打印
print('database version: %s' % data)

# 关闭数据库连接
conn.close()

Python之DDL，数据表的创建

# -*-coding:utf-8-*-

import pymysql

# 打开数据库连接
conn = pymysql.connect(host='sh-cdsmk.sql.tencentcdb.com',port=61122,user='root',passwd='1ste2yra@!55',db='by1')
# 使用cursor()方法创建一个游标对象
cursor = conn.cursor()
# 使用cursor()方法创建一个游标对象cursor
cursor = conn.cursor() # 游标对象用于执行查询和获取结果

# 使用execute()方法执行SQL，如果表存在则将其删除
cursor.execute('DROP TABLE IF EXISTS EMPLOYEE112')

# 使用预处理语句创建表
sql = """CREATE TABLE `employee112` (
`first_name` varchar(255) DEFAULT NULL COMMENT '姓',
`last_name` varchar(255) DEFAULT NULL COMMENT '名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`sex` varchar(255) DEFAULT NULL COMMENT '性别',
`income` varchar(255) DEFAULT NULL COMMENT '收入'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""

# 执行SQL语句
cursor.execute(sql)

# 关闭数据库连接
conn.close()

Python之插入操作

import pymysql

def get_conn():
   conn = pymysql.connect(host='sh-cdsmk.sql.tencentcdb.com',port=61122,user='root',passwd='1ste2yra@!55',db='by1')
    return conn

def insert(sql):
    conn = get_conn()
    cur = conn.cursor()
    result = cur.execute(sql)
    print(result)
    conn.commit()
    cur.close()
    conn.close()

if __name__ == '__main__':
    sql = 'INSERT INTO employee112 VALUES(\'yao\',\'ming\',12,\'男\',5000)';
    insert(sql)

在写sql语句的时候，需要避免直接写sql语句，而是采用占位符的方式来，防止sql的注入。

sql占位符形式实现

import pymysql

def get_conn():
     conn = pymysql.connect(host='sh-cdsmk.sql.tencentcdb.com',port=61122,user='root',passwd='1ste2yra@!55',db='by1')
    return conn

def insert(sql, args):
    conn = get_conn()
    cur = conn.cursor()
    result = cur.execute(sql, args)
    print(result)
    conn.commit()
    cur.close()
    conn.close()



if __name__ == '__main__':
    sql2 = 'INSERT INTO employee112 VALUES(%s,%s,%s,%s,%s);'
    insert(sql2, ('邓', '春芳', 20,'男',1000))

下面代码均以占位符的形式实现sql语句。

插入多条语句实现

import pymysql

def get_conn():
   conn = pymysql.connect(host='sh-cdsmk.sql.tencentcdb.com',port=61122,user='root',passwd='1ste2yra@!55',db='by1')
    return conn
 

def insert_many(sql, args):
    conn = get_conn()
    cur = conn.cursor()
    result = cur.executemany(query=sql, args=args)
    print(result)
    conn.commit()
    cur.close()
    conn.close()


if __name__ == '__main__':
    sql3 = 'insert into employee112 VALUES (%s,%s,%s,%s,%s)'
    args = [('周', '结论', 30, '男', 1000), ('毛', '卡里', 130, '男', 1002), ('邓', '西里', 60, '女', 2002)]
    insert_many(sql=sql3,args=args)

Python之更新操作

import pymysql

def get_conn():
    conn = pymysql.connect(host='sh-cdsmk.sql.tencentcdb.com',port=61122,user='root',passwd='1ste2yra@!55',db='by1')
    return conn

def update(sql,args):
    conn = get_conn()
    cur = conn.cursor()
    result = cur.execute(sql,args)
    print(result)
    conn.commit()
    cur.close()
    conn.close()




if __name__ == '__main__':
    sql = 'UPDATE employee112 SET income=%s WHERE last_name = %s;'
    args = (5000, '西里')
    update(sql, args)

Python之删除操作

import pymysql


  def get_conn():
 conn = pymysql.connect(host='sh-cdsmk.sql.tencentcdb.com',port=61122,user='root',passwd='1ste2yra@!55',db='by1') return conn


 def delete(sql,args):
    conn = get_conn()
    cur = conn.cursor()
    result = cur.execute(sql,args)
    print(result)
    conn.commit()
    cur.close()
    conn.close()



if __name__ == '__main__':

    sql = 'DELETE FROM employee112 WHERE last_name = %s;'
    args = ('恩来',) # 单个元素的tuple写法
    delete(sql,args)

Python之查询操作

import pymysql

 def get_conn():
 conn = pymysql.connect(host='sh-cdsmk.sql.tencentcdb.com',port=61122,user='root',passwd='1ste2yra@!55',db='by1') return conn



def query(sql,args):
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(sql,args)
    results = cur.fetchall()
    print(type(results))  # 返回 tuple元组类型
    for row in results:
        print(row)
        firstName = row[0]
        lastName = row[1]
        age1 = row[2]
        sex1 = row[3]
        income = row[4]
        print('姓:' +firstName + ',名:' +lastName + ',年纪:' + str(age1)+',性别:'+sex1+',收入:'+str(income))
        pass

    conn.commit()
    cur.close()
    conn.close()


if __name__ == '__main__':
    sql = 'SELECT  * FROM employee112;'
    query(sql,None)