pymysql简单操作数据库

1、通过 pip 安装 pymysql

pip install pymysql  

2、测试连接

mysql结构

3、打开数据库连接 ,获取游标

import pymysql

db = pymysql.connect(host='localhost',user='root',password='root',db='gaia',port=3306)

cur = db.cursor()  

4、查询操作

from mysql import *

sql='select * from t_comment'
try:
    cur.execute(sql)

    results  = cur.fetchall()
    print('id','user_id','comment','create_time')

    for result in results:
        id = result[0]
        user_id = result[1]
        comment = result[2]
        create_time = result[3]

        print(id,user_id,comment,create_time)

except Exception as e:
    raise e
finally:
    cur.close()

4、插入操作

from mysql import *

sql_insert ="""insert into t_comment(user_id,comment) values('liu','1234')"""

try:  
    cur.execute(sql_insert)  
    #提交  
    db.commit()  
except Exception as e:  
    #错误回滚  
    db.rollback()   
finally:  
    db.close()  
    

5、更新操作

from mysql import *
## excute执行SQL语句的时候，必须使用参数化的方式，否则必然产生SQL注入漏洞
sql_update ="update t_comment set user_id = '%s' where id = %d" 


try:
    cur.execute(sql_update % ("xiongda",3))
    db.commit()
except Exception as e:
    db.rollback()
finally:
    db.close()

6、删除操作

from mysql import *

delete_mysql = 'delete from t_comment  where id=%d'

try:
    cur.execute(delete_mysql % (2))
    db.commit()
except Exception as e:
    db.rollback()
finally:
    db.close()

ps:更多详情参考:https://pypi.org/project/PyMySQL/