Mysql 工作常用代码总结

复制表插入表

# 复制表
-- 在目标数据库中创建一个新表，并将源表的数据复制到新表中
CREATE TABLE XXX1.table1 AS SELECT * FROM XXX2.table1;

# 插入表
insert into XXX1.table_1 select * from XXX.table_2;

# 继承别的表结构，然后新建表格导入相应表数据
create table XXX.table_all as select * from XXX2.table_01 ;
comment on table XXX.table_template is '2024年就业招聘信息';

表授权

grant select on XXX.table1 to name_to_grant; 

python 下载/上传表数据

导入库

import pandas as pd
import numpy as np
import re
import psycopg2
from sqlalchemy import create_engine

下载数据

def download_sqlfile(sql_file):
    """
    连接数据库，下载所需数据
    """
    # 建立数据库连接
    con = psycopg2.connect(database="warehouse", user="name", password="password", host="XX.XXX.cn", port="5432")
    # 调用游标对象
    cur = con.cursor()

    # 执行语句，下载数据
    sql_command = "select * from %s"%sql_file # 
    # 获取数据
    # 由于数据库首次运行容易丢失连接，需两次重复运行
    try:
        data = pd.read_sql(sql_command, con,params=(sql_file,))
        print('连接数据库成功')
    except:
        print('首次连接数据库失败，尝试第二次连接数据库')
        data = pd.read_sql(sql_command, con,params=(sql_file,))
        print('连接数据库成功')
    
    # 关闭数据库连接关闭游标
    cur.close()
    con.close()
    
    return data

sql_file_1 = 'XXX.table_name'
df_1 = download_sqlfile(sql_file_1)

上传数据

# 数据上传到数据库
def upload_table(table_name, table):
    """
    将本地Dataframe存储到数据库中
    """
    # 数据库连接信息 database="warehouse", user="chengyuanting", password="ti59Ww43TAKNzCUj", host="pg.sunsharp.cn", port="5432")
    db_connection = {
        'host': 'pg.sunsharp.cn',
        'port': '5432',
        'dbname': 'warehouse',
        'user': 'chengyuanting',
        'password': 'ti59Ww43TAKNzCUj'
    }

    # 创建数据库引擎
    engine = create_engine('postgresql+psycopg2://{user}:{password}@{host}:{port}/{dbname}'.format(**db_connection))
    
    # 将本地Dataframe存储到数据库中
    with engine.connect() as conn:
        print(bool(conn))
        
        table.to_sql(name = table_name, con = engine, schema = 'staging_data_sy') # 注意此处to_sql需要使用sqlalchemy 包的 create_engine；数据库链接只能用engine不能使用
        
#     table.to_sql(table_name, engine, if_exists='replace', index=False)
    
    print('表格上传成功')
    
    return None


# 将data存储到数据库中
table_name = 'table_name'
try:
    upload_table(table_name, df_1)
except:
    print('第一次上传失败，正在进行第二次上传...')
    upload_table(table_name, df_1)