Mysql 创建数据库的设置默认编码和校验 python

在创建数据库的时候，经常用到一句：

CREATE DATABASE `xxxx` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

CREATE DATABASE `xxxx` :创建数据库  ,数据库的名称 使用 ` ` ,这里的 ` `  是ESC下面的那个符号 

DEFAULT CHARACTER SET utf8 :数据库字符集。设置数据库的默认编码为utf8  

COLLATE utf8_general_ci:数据库校对规则 

 

python 连接 mysql db ,多表查询

#!/usr/bin/env python
# encoding: utf-8
'''
@author: toby
@license: (C) Copyright 2013-2017, Node Supply Chain Manager Corporation Limited.
@contact: 
@software: pycharm
@file: sql.py
@time: 2020/6/6 11:47
@desc:
'''

import pymysql
# 添加用户允许从任何主机连接到mysql服务器
# GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

class mydb:
    def __init__(self):

        self.db = pymysql.connect(
            host = 'localhost',
            user = 'root',
            password = '123456',
            port = 3306,
            database = 'mydb', charset="utf8"
        )
        print('服务器连接成功!')
        print('=============================')

        self.cursor = self.db.cursor()

# # 得到一个可以执行SQL语句并且将结果作为字典返回的游标
# cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
#
# # 定义要执行的SQL语句
# sql = "select version()"
# cursor.execute(sql)
# # #使用fetchone()方法获取一条数据
# data = cursor.fetchone()
# print("Mysql version : %s"%data)

    def create_table(self):
        try:
            pass
            # 如果数据表已经存在使用execute()方法删除表。
            self.cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

            # 创建数据表SQL语句
            sql = """CREATE TABLE EMPLOYEE (
                             FIRST_NAME  CHAR(20) NOT NULL,
                             LAST_NAME  CHAR(20),
                             AGE INT,  
                             SEX CHAR(1),
                             INCOME FLOAT )"""

            self.cursor.execute(sql)
            print('创建table 成功!')
        except Exception as e:
            print(e)


    #创建
    def create_tb2(self):
        try:
            self.cursor.execute("DROP TABLE IF EXISTS department,staff")
            sql1 = """create
                    table
                    department(id  int,
                    name  varchar(20)  
                    )"""

            sql2 = """create
                    table staff(
                            id  int
                            primary key auto_increment,
                            name  varchar(20),  
                            sex  enum('male', 'female')  not null
                            default 'male',
                            age int,
                            dep_id  int
                        )"""

            self.cursor.execute(sql1)
            self.cursor.execute(sql2)
            print('创建table department   staff 成功!')
        except  Exception as  e:
            print(e)



    #插入
    def insert_tb(self):
        try:
            sql = """insert into department values
                    (200,'挖矿小分队'),
                    (201,'人力资源'),
                    (202,'销售'),
                    (203,'运营')"""
            sql2 = """insert into staff(name,sex,age,dep_id) values
                        ('程咬金','male',38,200),
                        ('露娜','female',26,201),
                        ('李白','male',38,201),
                        ('王昭君','female',28,202),
                        ('典韦','male',118,200),
                        ('小乔','female',16,204)"""
            self.cursor.execute(sql)
            self.cursor.execute(sql2)
            self.db.commit()
            print('insert department   staff 成功!')
        except Exception as e :
            print(e)
            print("插入失败")

    def duobiaosele(self):
        # 内链接 (常用) 只连接匹配的行 ,两个表中共同的属性的进行匹配显示,没有匹配上的 ,不显示
        try:
            print('================多表内联的查询=====================')
            sql = """
            select * from staff inner join department on staff.dep_id =department.id
            """
            self.cursor.execute(sql)

            res = self.cursor.fetchall()
            for i in res :
                print(i)
            print('=============================================')

            print('================多表外联的查询 左连接=====================')
            #外链常用   左连接
            sql2 ="""select * from staff left join department on staff.dep_id = department.id"""
            self.cursor.execute(sql2)

            res = self.cursor.fetchall()
            for i in res:
                print(i)

            print('=============================================')

            print('================多表外联的查询 右连接=====================')
            #外链  右连接
            sql3 = """select * from staff right join department on  staff.dep_id = department.id """
            self.cursor.execute(sql3)

            res = self.cursor.fetchall()
            for i in res:
                print(i)

            print('=============================================')

            print('================多表全部外联的查询=====================')
            #全部外链  显示左右两个表全部记录
            sql4 =sql2+" union "+sql3
            self.cursor.execute(sql4)
            res = self.cursor.fetchall()
            for i in res:
                print(i)


        except Exception as  e:
            print(e)




    def db_close(self):
        # 关闭光标对象
        self.cursor.close()

        # 关闭数据库连接
        self.db.close()


if __name__ == '__main__':
    mydb = mydb()
    # mydb.create_tb2()
    # mydb.insert_tb()
    mydb.duobiaosele()
    mydb.db_close()