python 连接mysql 建表建库实例

NULL 为空 ,DEFAULT 默认值

# -*- coding: utf-8 -*-
"""
 @Time   : 2020/10/28 10:16 
 @Athor   : LinXiao
 @功能   :
"""
# ------------------------------
import pymysql
from dbutils.pooled_db import PooledDB

from config import config_template

"""
1、连接本地数据库
2、建立游标
3、创建表
4、插入表数据、查询表数据、更新表数据、删除表数据
"""


# 连接池
class MysqlPool:
    config={
        'creator': pymysql,
        'host': config_template['MYSQL']['HOST'],
        'port': config_template['MYSQL']['PORT'],
        'user': config_template['MYSQL']['USER'],
        'password': config_template['MYSQL']['PASSWD'],
        'db': config_template['MYSQL']['DB'],
        'charset': config_template['MYSQL']['CHARSET'],
        'maxconnections': 70,  # 连接池最大连接数量
        'cursorclass': pymysql.cursors.DictCursor
    }
    pool=PooledDB(**config)

    def __enter__(self):
        self.conn=MysqlPool.pool.connection()
        self.cursor=self.conn.cursor()
        return self

    def __exit__(self, type, value, trace):
        self.cursor.close()
        self.conn.close()


# 连接mysql
def init_mysql():
    con=pymysql.connect(
        host='localhost',  # 连接的是本地数据库
        user='root',  # 自己的mysql用户名
        passwd='123456',  # 自己的密码
        db='fpf',  # 数据库的名字
        charset='utf8mb4',  # 默认的编码方式：
        cursorclass=pymysql.cursors.DictCursor)
    cursor=con.cursor()
    return cursor


# 建库和建表(字段)
def create_db_table():
    # cursor=init_mysql()
    with MysqlPool() as db:
        # 如果存在student表，则删除
        db.cursor.execute("DROP database IF EXISTS fpf")
        db.cursor.execute("CREATE database fpf")
        # 使用库
        db.cursor.execute("use fpf;")
        # 建表

        # sql 语句
        sql="""
            CREATE TABLE IF NOT EXISTS `fangyuan`(
                                       `house_id`          BIGINT(20)  NOT NULL ,
                                       `type_id1`          tinyint(2)  NOT NULL  DEFAULT 1,
                                       `title`             VARCHAR(80) NOT NULL,
                                       `cover`             varchar(50) NULL,
                                       `pic`               varchar(50) null,
                                       `province_name`     varchar(50) NOT NULL,
                                       `city_name`         varchar(50) NOT NULL,
                                       `area`              varchar(50) NOT NULL,
                                       `address`           varchar(255)  NULL,
                                       `auction_start`     varchar(10) NOT NULL,
                                       `auction_end`       varchar(10) NOT NULL,
                                       `initial_price`     varchar(20) NOT NULL,
                                       `price_lower_offset`    varchar(20) NOT NULL DEFAULT 0,
                                       `bond`              varchar(20) NOT NULL,
                                       `consult_price`     varchar(20)  NULL,
                                       `court_name`        varchar(255)  NULL,      # 处置单位  
                                       `contact_name`      varchar(50)  NULL,
                                       `contact_tel`       varchar(255)  NULL,
                                       `source_url`         varchar(255)    NULL,       # 拍卖链接  
                                       `enroll_num`         varchar(10) NULL    DEFAULT 0,  # 报名人数  
                                       `access_num`         varchar(20) NULL    DEFAULT 0,  # 围观人数  
                                       `remind_num`         varchar(20) NULL    DEFAULT 0,  # 提醒人数  
                                       `bid_count`          varchar(10) NULL,               # 加价次数  
                                       `deal_time`          varchar(10) NULL,                 # 结束时间/成交时间,10位时间戳    
                                       `final_price`        varchar(10) NULL,                 # 成交价格    
                                       `auction_id`         varchar(10) NOT NULL,       # 拍卖状态：1待开始,2进行中3已成交，4已流拍，5已撤回，6已终止 7已暂缓    
                                       `auction_count`      varchar(10) NOT NULL,       # 拍卖次数：1一拍 2二拍 3三拍 4变卖 5重新拍卖/再次拍卖 6破产   
                                       `updatetime`         varchar(10) NOT NULL,       # 推送时间：10位时间戳   
                                       `is_update`          varchar(10) NOT NULL    DEFAULT 1,  # 是否更新，更新2  
                                       `is_save`            varchar(10) NULL        DEFAULT 0,  
                                        PRIMARY KEY ( `house_id` )
                                        )ENGINE=InnoDB DEFAULT CHARSET=utf8;
                """
        db.cursor.execute(sql)
        print("创建数据库创建表成功!")
        db.conn.commit()
        db.conn.close()
        # cursor.execute("create table fangyuan(house_id char(20) NULL ,bd char(20),name char(20),bond char(20),)")


def insert_data(house_data):
    # ziduan = house_id, type_id1, title, cover, pic, province_name, city_name, area, address, auction_start, auction_end, initial_price, price_lower_offset, bond, consult_price, court_name, contact_name, contact_tel, source_url, enroll_num, access_num, remind_num, bid_count, deal_time, final_price, auction_id, auction_count, updatetime, is_update, is_save

    with MysqlPool() as db:
        try:
            # 使用cursor()方法获取操作游标
            cursor=db.cursor
            # SQL 插入语句
            sql="INSERT INTO fangyuan VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s," \
                " %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

            # 执行SQL语句
            cursor.execute(sql, (house_data["house_id"], house_data["type_id1"],
                                 house_data["title"], house_data["cover"],
                                 house_data["pic"], house_data["province_name"],
                                 house_data["city_name"], house_data["area"],
                                 house_data["address"], house_data["auction_start"],
                                 house_data["auction_end"], house_data["initial_price"],
                                 house_data["price_lower_offset"],
                                 house_data["bond"], house_data["consult_price"],
                                 house_data["court_name"], house_data["contact_name"],
                                 house_data["contact_tel"], house_data["source_url"],
                                 house_data["enroll_num"], house_data["access_num"],
                                 house_data["remind_num"], house_data["bid_count"],
                                 house_data["deal_time"], house_data["final_price"],
                                 house_data["auction_id"], house_data["auction_count"],
                                 house_data["updatetime"], house_data["is_update"],
                                 house_data["is_save"],))
            print('插入成功!')
            # 提交修改
            db.conn.commit()
            db.conn.close()
        except:
            print('插入数据失败!')



if __name__ == "__main__":
    # 创建表
    # create_db_table()
    house_data ={
        "_id": "6303995390571603801397",
        "house_id": "630399539057",
        "title": "崇州市崇阳镇早兴街55号“阳光锦园”1栋1单元2层1号住宅",
        "type_id1": 1,
        "cover": str("https://img.alicdn.jpg"),
        "pic": str([
            "https://img.alicdn.com"
        ]),
        "province_name": "四川省",
        "city_name": "成都市",
        "area": "崇州市",
        "address": "崇州市崇阳镇早兴街55号“阳光锦园”1栋1单元2层1号住宅",
        "price_lower_offset": "2,000",
        "court_name": "崇州市人民法院",
        "contact_name": "四川中环来拍网络科技有限公司",
        "contact_tel": "028-68892642",
        "bond": "50,000",
        "initial_price": "510,000",
        "consult_price": "630,673",
        "source_url": "https://sf-ite",
        "enroll_num": "0",
        "access_num": "1809",
        "remind_num": "21",
        "auction_start": 1605751200,
        "auction_end": 1605837600,
        "bid_count": " ",
        "deal_time": " ",
        "final_price": " ",
        "auction_id": 2,
        "auction_count": 1,
        "updatetime": "",
        "is_update": 1,
        "is_save": 0
    }

    insert_data(house_data)

运行时会先要求在navicat 中先手动新建 数据库名
效果:

insert_data(house_data)
插入数据效果: