python操作mysql之pymysql

　　pymysql

config = {
    'host' : '192.168.70.12',
    'user' : 'zsbi',
    'password' : 'zsbi',
    'port' : 3306,
    'charset' : 'utf8'
    }

#db = pymysql.connect(host='localhost', user='root', password='******', port=3306) 
db = pymysql.connect(**config) 

cursor = db.cursor() #获取游标
#select TABLE_NAME, TABLE_ROWS from information_schema.tables where table_type='BASE TABLE'
#https://www.cnblogs.com/reyinever/p/10869434.html
sql = 'select * from zsbi.aa10 limit 1,5'
cursor.execute(sql)# 执行SQL查询，获取数据
data = cursor.fetchall()# 获取单条数据 fetchone, fetchmany
# 获取列名列表
sql = 'SHOW FULL COLUMNS FROM zsbi.aa10'
cursor.execute(sql)#获取列名
fea = [i[0] for i in cursor.fetchall()]
cursor.close()
db.close()# 关闭数据库连接
#数据整理为dataframe格式
data = pd.DataFrame(data,columns=lis)#list转化成dataframe格式


from sqlalchemy import create_engine

#填写链接信息
engine = create_engine("mysql+pymysql://【此处填用户名】:【此处填密码】@【此处填host】:【此处填port】/【此处填数据库的名称】?charset=utf8") 
# 例如：engine = create_engine("mysql+pymysql://root:666666@localhost:3306/ajx?charset=utf8")
engine = create_engine("mysql+pymysql://{user}:{password}@{host}:{port}/ajx?charset=utf8".format(**config))

#开始写入
data.to_sql(name = 'split',con = engine,if_exists = 'append',index = False,index_label = False)



DROP TABLE IF EXISTS `cdr`;
CREATE TABLE `cdr` (
    `direction` varchar(255) DEFAULT NULL,
    `caller` varchar(255) DEFAULT NULL,
    `callee` varchar(255) DEFAULT NULL,
    `context` varchar(255) DEFAULT NULL,
    `start_stamp` timestamp NULL DEFAULT NULL,
    `answer_stamp` varchar(255) DEFAULT NULL,
    `end_stamp` timestamp NULL DEFAULT NULL,
    `duration` int(255) DEFAULT NULL,
    `billsec` int(255) DEFAULT NULL,
    `hangup_cause` varchar(255) DEFAULT NULL,
    `uuid` varchar(255) DEFAULT NULL,
    `bleg_uuid` varchar(255) DEFAULT NULL,
    `accountcode` varchar(255) DEFAULT NULL,
    `read_codec` varchar(255) DEFAULT NULL,
    `write_codec` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

　　

　　load data infile 

load data infile 'd:/test.csv' 
IGNORE
into table 将要导入的数据库表名 
fields 
    terminated by '\t'  -- 在字段之间写入制表符
    optionally enclosed by '"' 
    escaped by '\\' 
lines 
    terminated by '\r\n';

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]  -- 针对重复数据，replace先删后写=更新数据，ignore忽略重复数据
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t'] -- 在字段之间写入制表符
        [[OPTIONALLY] ENCLOSED BY '']  --不把字段包含在任何引号字符中。如果是ENCLOSED BY "'" ：使用单引号把各个字段括起来；
        [ESCAPED BY '\\' ] --当字段值中出现制表符、新行或‘\’时，使用‘\’进行转义。
    ]
    [LINES
        [STARTING BY '\n'] -- 在行的末端写入新行。
        [TERMINATED BY ''] --不会跳过任何行前缀。
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...)]

　　

　　select into outfile

#/etc/mysql/mysql.conf.d/mysqld.cnf
select * 
from tempdb.t 
into outfile ‘/home/mysql/t.txt’; 

'1100'$'监狱管理局'$\N$1$\N$'监狱管理局'$1$1$'1100'$'1'$\N$'2011-01-12 00:00:00'$
'1101'$'第一监狱'$'1100'$2$\N$'第一监狱'$1$2$'1101'$'1'$\N$'2011-01-12 00:00:00'$
'1102'$'第二监狱'$'1100'$2$\N$'第二监狱'$1$3$'1102'$'1'$\N$'2011-01-12 00:00:00'$


SELECT * INTO  outfile 'e:/tmp/t_aty_corp.sql'  
FIELDS 
    TERMINATED BY '$' 
OPTIONALLY 
    ENCLOSED BY "'" 
    ESCAPED BY '\\' 
LINES 
    STARTING BY '\r\n' 
    TERMINATED BY '' 
FROM db_aty.t_aty_corp;


^1100^$^监狱管理局^$\N$1$\N$^监狱管理局^$1$1$^1100^$^1^$\N$^2011-01-12 00:00:00^$
^1101^$^第一监狱^$^1100^$2$\N$^第一监狱^$1$2$^1101^$^1^$\N$^2011-01-12 00:00:00^$
^1102^$^第二监狱^$^1100^$2$\N$^第二监狱^$1$3$^1102^$^1^$\N$^2011-01-12 00:00:00^$


SELECT * INTO  outfile 'e:/tmp/t_aty_corp.sql'  
FIELDS TERMINATED BY '$' 
OPTIONALLY ENCLOSED BY "^" 
ESCAPED BY '\\' 
LINES STARTING BY '\r\n' 
TERMINATED BY ''   
FROM db_aty.t_aty_corp;