python连接数据库查询DEMO

最新推荐文章于 2024-09-12 16:46:50 发布

我怀念的是_无话不说

最新推荐文章于 2024-09-12 16:46:50 发布

阅读量631

点赞数

分类专栏： bigData 文章标签： python mysql

本文链接：https://blog.csdn.net/qiang0066/article/details/79567470

版权

bigData 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

#!/usr/bin/python3
# -*- coding: UTF-8 -*-
# __author__ :zhuhongqiang
import pymysql as MySQLdb

dburl = "127.0.0.1"
username = "root"
password = "root"
dbname = "test"


# 根据sql 操作数据库
def getConnection(sql,type): #1:查询 2:增删改
    connections = MySQLdb.connect(dburl, username, password, dbname,charset="utf8")
    # 使用cursor()方法获取操作游标
    cursor = connections.cursor()
    results = ""
    create_table_sql ="CREATE TABLE IF NOT EXISTS `user001` " \
                      "(`id` int(11) NOT NULL AUTO_INCREMENT,`username` varchar(20) DEFAULT '' COMMENT '用户名'," \
                      "`age` int(2) DEFAULT '0' COMMENT '年龄',`sex` tinyint(1) DEFAULT '0' COMMENT '0:男1:女'," \
                      "PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8"
    try:
        # 执行sql语句
        cursor.execute(create_table_sql)
        cursor.execute(sql)
        # 提交到数据库执行
        connections.commit()
        # 获取所有记录列表
        if type==1:
            results = cursor.fetchall()
        elif type ==2:
            results=cursor.rowcount
        else:
            results = "请求类型参数未识别..."
        print("主键: %d " %cursor.lastrowid)
        print(results)
    except Exception as e:
        # Rollback in case there is any error
        print("get db is error....",e)
        connections.rollback()
    finally:
        cursor.close()
        connections.close()
    return results


if __name__ == "__main__":
    userName ="jack"
    age = 18
    sex = 1
    sql = "INSERT INTO `test`.`user001` (`username`, `age`, `sex`) VALUES ('%s', '%s', '%d')" % (userName,age,sex) #插入
    # sql =" select * from user001" #查询
    # sql =" UPDATE user001 SET username="李斯特" where id =1" #修改
    # sql ="DELETE FROM user001 WHERE id =1" #删除
    result = getConnection(sql,2)