python 连接mysql 建表建库实例
2020-10-28 本文已影响0人
程序里的小仙女
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)
插入数据效果:
