python连接mysql的一些基础知识＋安装Navicat可视化数据库+flask_sqlalchemy写数据库

一．mysql基础知识 

 １．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

２．创建表

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)

３．插入并且加上了重复插入的异常

# 在存放图片路径的表中插入数据
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

４．查询

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

５．更新

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

６．删除

# 删除
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()

 ３．表的设计与增删改查调试

#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()