一.mysql基础知识
1.connect连接数据库
import pymysql
def get_conn():
conn = pymysql.connect(host='xxx.xxx.xxx.xxx', port=3306, user='root', passwd='', db='newspaper_rest') # db:表示数据库名称
return conn
2.创建表
import pymysql
def get_conn():
conn = pymysql.connect(host='xxx.xxx.xxx.xxx', port=3306, user='root', passwd='', db='newspaper_rest') # db:表示数据库名称
return conn
# 创建mysql表
def ceartTable(cursor,form_name):
#删数据库行为 要慎重
# # # 创建newspaper_rest 数据库, 如果存在则删除newspaper_rest 数据库
cursor.execute("drop database if exists newspaper_rest")
cursor.execute("create database newspaper_rest")
# 选择 newspaper_rest 这个数据库
cursor.execute("use newspaper_rest")
# KEY
# IDENTITY
# 将sql中的内容为创建一个名为recordid_form的表
sql = """CREATE TABLE IF NOT EXISTS %s (
recordid VARCHAR (100),
publish_date VARCHAR (100),
page_num VARCHAR (100),
ISdeal VARCHAR (1),
primary key (recordid,publish_date,page_num)
)"""%(form_name)
# # 如果存在表则删除
# cursor.execute("drop table if exists %s"%(form_name))
# 创建表
cursor.execute(sql)
print("successfully create table")
db = get_conn()
cursor = db.cursor()
# # # # # # 创建数据库和表
form_name = 'imgs_list_path_form'
ceartTable(cursor, form_name)
3.插入并且加上了重复插入的异常
# 在存放图片路径的表中插入数据
def insert_pub_page_name_data(db,cursor,recordid, publish_name,page_num,isdeal):
# ISdeal = [str(isdeal)]*len(recordid)
# data_tuple = tuple(zip(recordid, ISdeal))
try:
# 插入数据
sql = "INSERT INTO imgs_list_path_form (recordid,publish_date,page_num,ISdeal) VALUES('%s','%s','%s','%s')"%(recordid, publish_name,page_num,isdeal)
cursor.execute(sql)
# cursor.executemany(sql, data_tuple)
# 提交到数据库执行
db.commit()
print("successfully insert publish date data!")
except Exception as e:
print('insert multipy!', e)
return False
return True
4.查询
def find_pub_page_name_data(cursor,is_deal):
# 要执行的sql语句
sql = "select * from imgs_list_path_form where ISdeal ='%s'"%(is_deal)
cursor.execute(sql)
try:
result = cursor.fetchall()
except TypeError:
print("An exception was raised")
else:
# print(result, "successfully find")
print("successfully find publish date data!!!")
# print('res:', result)
return result
5.更新
def update_pub_page_name_data(db,cursor,recordid,publish_name,page_num):
try:
# 更改
sql = "update imgs_list_path_form set ISdeal='1' where recordid='%s' AND publish_date='%s' AND page_num='%s'"%(recordid,publish_name,page_num)
cursor.execute(sql)
db.commit()
print('update Success!')
except Exception as e:
print('update fail!',e)
return False
return True
6.删除
# 删除
def deleteRecord(db, cursor, key, value):
# 要执行的sql语句
sql = "delete from recordid_library where " + key + "=" + value
cursor.execute(sql)
db.commit()
print("successfully delete")
可视化数据库:
二.安装Navicat
首先上官网上下载LINUX版本: http://www.navicat.com.cn/download/navicat-premium
1.安装
1 下载 navicat110_mysql_en.tar.gz 文件
2.tar -zxvf xxx/navicat112_mysql_cs_x64
3 解压后 进入解压后的目录运行命令:
./start_navicat
2.修改中文可视
连接上数据库后里面的中文数据是乱码,把Ubuntu的字符集修改为zh_CN.utf8就行了,修改方法:
1.查看系统支持的字符集: locale -a
2,修改字符集: export LANG=zh_CN.utf8
3.用vim命令打开start_navicat文件,会看到 export LANG=”en_US.UTF-8” 将这句话改为 export LANG=”zh_CN.UTF-8”。
3.破解
sudo rm -r /home/fzh/.navicat64/
删除后,再次启动navicat时,会加载wine的更新配置窗口,启动后之前用的连接名以及密码已经没有了,需要重新创建连接。
4.卸载
1.首先将Navicat 安装包删除,
2. rm -r ~/.navicat64
5.不断延长使用时间
cd ~/.navicat64/
rm -r *.reg
6.目前最新的
chmod +x navicat15-premium-cs.AppImage
./navicat15-premium-cs.AppImage
三.flask_sqlalchemy写数据库
官方文档:https://sqlalchemy-utils.readthedocs.io/en/latest/database_helpers.html
1.数据库不存在,进行创建
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_utils.functions import database_exists, create_database
from flask_sqlalchemy import SQLAlchemy
from flask import Flask, jsonify, request
db = SQLAlchemy()
#本地配置
username = 'root'
password = '123456'
ip = '127.0.0.1'
port = '3306'
database = "fzh_database"
sql_url = "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8mb4".format(username, password, ip, port, database)
engine = create_engine(sql_url, encoding="utf8")
# 数据库不存在的话就创建
if not database_exists(engine.url):
create_database(engine.url)
2.创建表
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_utils.functions import database_exists, create_database
from flask_sqlalchemy import SQLAlchemy
from flask import Flask, jsonify, request
db = SQLAlchemy()
#本地配置
username = 'root'
password = '123456'
ip = '127.0.0.1'
port = '3306'
database = "fzh_database"
sql_url = "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8mb4".format(username, password, ip, port, database)
app = Flask(__name__)
app.config[
"SQLALCHEMY_DATABASE_URI"
] = sql_url
# app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db.init_app(app)
#创建表
class AIRedFile(db.Model):
__tablename__ = "ai_redfile"
pid = db.Column("pid", db.String(255))
file_id = db.Column("file_id", db.String(255))
file_path = db.Column("file_path", db.String(255))
is_deal = db.Column("is_deal", db.String(1))
type_ = db.Column("type", db.String(5))
img_id = db.Column("img_id", db.String(255))
angle = db.Column("angle", db.String(5))
page_num = db.Column("page_num", db.Integer)
url = db.Column("url", db.String(255))
gpu_id = db.Column("gpu_id", db.String(1))
key_ID = db.Column("key_ID", db.Integer, primary_key=True, autoincrement=True)
create_time = db.Column('create_time', db.DateTime)
update_time = db.Column('update_time', db.DateTime)
def __init__(self, pid, file_id, file_path, is_deal, type_, img_id, angle, page_num, url, gpu_id, create_time, update_time):
self.pid = pid
self.file_id = file_id
self.file_path = file_path
self.is_deal = is_deal
self.type_ = type_
self.img_id = img_id
self.angle = angle
self.page_num = page_num
self.url = url
self.gpu_id = gpu_id
self.create_time = create_time
self.update_time = update_time
# Base.metadata.create_all(engine) #创建表结构
with app.app_context():
db.create_all()
3.表的设计与增删改查调试
#coding:utf-8
"""
数据库的设计
"""
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
db = SQLAlchemy()
class AIDaxin(db.Model):
bank_id = db.Column("bank_id", db.String(255))
bank_name = db.Column("bank_name", db.String(255))
pid = db.Column("pid", db.String(255))
file_pid = db.Column("file_pid", db.String(255))
#自己设置自增id
key_ID = db.Column("key_ID", db.Integer, primary_key=True, autoincrement=True)
file_path = db.Column("file_path", db.String(255))
is_deal = db.Column("is_deal", db.String(1))
create_time = db.Column('create_time', db.DateTime)
update_time = db.Column('update_time', db.DateTime)
__tablename__ = "ai_daxin"
def __init__(self, bank_id, bank_name, pid, file_pid, file_path, is_deal, create_time, update_time):
self.bank_id = bank_id
self.bank_name = bank_name
self.pid = pid
self.file_pid = file_pid
self.file_path = file_path
self.is_deal = is_deal
self.create_time = create_time
self.update_time = update_time
#debug 使用
def ai_daxin_test():
from flask import Flask, jsonify, request
app = Flask(__name__)
app.config[
"SQLALCHEMY_DATABASE_URI"
] = "mysql+pymysql://root:123456@localhost:3306/fzh_local_test"
# # # #增
# import datetime
# with app.app_context():
# test_news = AIDaxin('bank_id1', 'bank_name1','pid1', 'file_pid3', 'file_path3', '0', datetime.datetime.now(), None)
# db.session.add(test_news)
# db.session.commit()
# 查
# with app.app_context():
# pdf_infos = AIDaxin.query.filter_by(is_deal="0").all()
# print('pdf_infos:', pdf_infos)
# for pdf_info in pdf_infos:
# print('pdf_info.pid:', pdf_info.pid)
# print('pdf_info.file_pid:', pdf_info.file_pid)
# print('pdf_info.file_path:', pdf_info.file_path)
# print('pdf_info.is_deal:', pdf_info.is_deal)
# #删
# with app.app_context():
# newspapers = AINewspaper.query.filter_by(is_deal="0").first()
# db.session.delete(newspapers)
# db.session.commit()
# print('newspapers:', newspapers)
# 改
# with app.app_context():
# pdf_infos = AIDaxin.query.filter_by(file_path="file_path2").first()
# pdf_infos.is_deal = '1'
# db.session.commit()
if __name__ == '__main__':
ai_dain_test()