python操作mysql练习

最新推荐文章于 2022-08-18 22:49:57 发布

zhangbeizhen18

最新推荐文章于 2022-08-18 22:49:57 发布

阅读量242

点赞数

分类专栏： L01-基础

本文链接：https://blog.csdn.net/zhangbeizhen18/article/details/105137263

版权

L01-基础专栏收录该内容

83 篇文章 0 订阅

订阅专栏

备忘录：
练习，即可。
1.测试代码
包括，创建表，插入、更新、查询、删除。

import pymysql
import traceback
# PyMySQL是在Python3.x版本中用于连接MySQL服务器的一个库

class MysqlUtils:
    def __init__(self,host,port,dbName,user,password,charset='utf8'):
        #获取mysql连接
        self.conn = pymysql.connect(host=host,port=port,db=dbName,user=user,password=password,charset=charset)
    def common(self,sql,param=[]):
        try:
            cur = self.conn.cursor()
            rows = cur.execute(sql,param)
            self.conn.commit()
            cur.close()
            self.conn.close()
            return rows
        except Exception as e:
            traceback.print_exc()
            self.conn.rollback()

    def insert(self,sql,param):
        return self.common(sql,param)

    def update(self,sql,param):
        return self.common(sql,param)

    def delete(self,sql,param):
        return self.common(sql,param)

    def fetchOne(self,sql,param=[]):
        """一条记录"""
        try:
            cur = self.conn.cursor()
            cur.execute(sql,param)
            row = cur.fetchone()
            cur.close()
            self.conn.close()
            return row
        except Exception as e:
            traceback.print_exc()

    def fetchAll(self,sql,param):
        """多条记录"""
        try:
            cur = self.conn.cursor()
            cur.execute(sql,param)
            rows = cur.fetchall()
            cur.close()
            self.conn.close()
            return rows
        except Exception as e:
            traceback.print_exc()

    def createTable(self,sqlDelete,sqlCreate):
        """建表"""
        try:
            cur = self.conn.cursor()
            # 表存在就先删除
            cur.execute(sqlDelete)
            # 重建表
            cur.execute(sqlCreate)
            cur.close()
            self.conn.close()
        except Exception as e:
            traceback.print_exc()

def main():
    host = "localhost"
    port = 3306
    dbName = "demodb"
    user = "root"
    password = "123456"
    charset = 'utf8'
    utils = MysqlUtils(host,port,dbName,user,password,charset)

    #1.测试建表
    sqlDelete ="DROP TABLE IF EXISTS t_city_demo"
    sqlCreate ="CREATE TABLE `t_city_demo` ( \
                `CITY_NAME` VARCHAR(64) COLLATE utf8_bin NOT NULL COMMENT '城市名',\
                `LAND_AREA` DOUBLE DEFAULT NULL COMMENT '城市面积' \
                 ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='城市信息表'"
    utils.createTable(sqlDelete,sqlCreate)

    #2.测试插入
    sql = "INSERT INTO t_city (CITY_NAME,LAND_AREA,POPULATION,GROSS,AREA_NUMBER,POSTAL_CODE,TELEPHONE_CODE,CAR_CODE,CITY_DESCRIBE) \
            VALUES('杭州','8586','850','12000','330100','310000','0571','浙A','互联网城市')"
    utils.insert(sql,None)

    sql = "INSERT INTO t_city (CITY_NAME,LAND_AREA,POPULATION,GROSS,AREA_NUMBER,POSTAL_CODE,TELEPHONE_CODE,CAR_CODE,CITY_DESCRIBE) \
                VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    utils.insert(sql, ['宁波','8586','850','12000','330200','310000','0574','浙B','新型城市'])

    #3.测试更新
    sql = "UPDATE t_city SET LAND_AREA = '9000',POPULATION = '900' WHERE CITY_NAME='宁波'"
    utils.insert(sql, None)

    sql = "UPDATE t_city SET LAND_AREA = %s ,POPULATION = %s  WHERE CITY_NAME= %s  "
    utils.insert(sql, ['9900','990','宁波'])

    #4.测试查询
    sql = "SELECT CITY_NAME,LAND_AREA,POPULATION,GROSS,AREA_NUMBER,POSTAL_CODE,TELEPHONE_CODE,CAR_CODE,CITY_DESCRIBE FROM t_city WHERE  CITY_NAME= %s "
    row = utils.fetchOne(sql,['宁波'])
    for one in row:
        print(one)
    sql = "SELECT CITY_NAME,LAND_AREA,POPULATION,GROSS,AREA_NUMBER,POSTAL_CODE,TELEPHONE_CODE,CAR_CODE,CITY_DESCRIBE FROM t_city WHERE  CITY_NAME= %s or CITY_NAME= %s"
    rows = utils.fetchAll(sql,['宁波','杭州'])
    print(rows)
    for row in rows:
        for one in row:
            print(one)

    sql = "SELECT CITY_NAME,LAND_AREA,POPULATION,GROSS,AREA_NUMBER,POSTAL_CODE,TELEPHONE_CODE,CAR_CODE,CITY_DESCRIBE FROM t_city WHERE  CITY_NAME= %s or CITY_NAME= %s"
    rows = utils.fetchAll(sql,['宁波','杭州'])
    print(rows)
    #遍历方式
    for row in rows:
        cityName = row[0]
        landArea = row[1]
        print("cityName=%s,landArea=%s"%(cityName,landArea))

    #5.测试删除
    sql = "DELETE FROM t_city WHERE city_name= %s "
    utils.delete(sql,'杭州')

    sql = "DELETE FROM t_city WHERE city_name= %s or city_name= %s  "
    utils.delete(sql, ['杭州','宁波'])

if __name__ == '__main__':
    main()

2.建表语句一

CREATE TABLE `t_city_demo` (
  `CITY_NAME` VARCHAR(64) COLLATE utf8_bin NOT NULL COMMENT '城市名',
  `LAND_AREA` DOUBLE DEFAULT NULL COMMENT '城市面积',
  `POPULATION` BIGINT(16) DEFAULT NULL COMMENT '城市人口'
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='城市信息表'

3.建表语句二

CREATE TABLE `t_city` (
  `CITY_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '城市名',
  `LAND_AREA` double DEFAULT NULL COMMENT '城市面积',
  `POPULATION` bigint(16) DEFAULT NULL COMMENT '城市人口',
  `GROSS` double DEFAULT NULL COMMENT '生产总值',
  `AREA_NUMBER` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '行政区划代码',
  `POSTAL_CODE` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '邮政编码',
  `TELEPHONE_CODE` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '电话区号',
  `CAR_CODE` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '车牌代码',
  `CITY_DESCRIBE` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '城市描述'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='城市信息表'

以上，感谢。