Python之csv文件从MySQL数据库导入导出的章程,python批量导出导入MySQL用户的措施

亚洲城ca88手机版官网

一、相关注明

Python从MySQL数据库中程导弹出csv文件处理

数据库迁移(A ->
B),需求把用户也迁移过去,而用户表(mysql.user)有这八个个用户。有二种办法开始展览快捷迁移:
壹,在同版本的标准化下,直接备份A服务器的mysql数据库,还原到B服务器。
2,倘使分化版本的数码(伍.1 ->
伍.五),很只怕mysql数据库上边包车型客车有个别表结构,乃至表数据的暗中同意值都不一样样,依照壹的不二等秘书诀开始展览搬迁,即使最后也是足以健康访问,不过照旧某个不太放心,很或许会潜移默化到了B服务器上的MySQL,那样就需要用命令行来生成帐号了,这样是最安全和放心的。上边用python脚本来举行批量导出:

此脚本能够将Mysql的数量导出成Json格式,导出的剧情能够开始展览select查询鲜明。

csv文件导入MySQL数据库

复制代码 代码如下:

数据传入参数有:dbConfigName, selectSql, jsonPath, fileName。

import pymysql
import csv
import codecs
def get_conn():
  conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test_csv', charset='utf8')
  return conn
def insert(cur, sql, args):
  cur.execute(sql, args)
def read_csv_to_mysql(filename):
  with codecs.open(filename=filename, mode='r', encoding='utf-8') as f:
    reader = csv.reader(f)
    head = next(reader)
    conn = get_conn()
    cur = conn.cursor()
    sql = 'insert into tb_csv values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
    for item in reader:
      if item[1] is None or item[1] == '': # item[1]作为唯一键,不能为null
        continue
      args = tuple(item)
      print(args)
      insert(cur, sql=sql, args=args)
    conn.commit()
    cur.close()
    conn.close()
if __name__ == '__main__':
  read_csv_to_mysql('1.csv')

#!/bin/env python
# -*- encoding: utf-8 -*-
#—————————————–
# Name:        mysql_user_dump.py
# Purpose:     批量导出用户
# Author:      zhoujy
# Created:     2013-05-28
#—————————————–
import MySQLdb

依附的库有:MySQLdb、json,尤其MySQLdb须求事先安装好。

注:

def get_data(conn):
    query  = ‘select user,host from mysql.user order by user’
    cursor = conn.cursor()
    cursor.execute(query)
    lines  = cursor.fetchall()
    return lines

二、Python脚本及测试示例

一.csv文件的表头如:

def output_data(conn,rows):
    for user,host in rows:
        query  = “show grants for ‘%s’@’%s'” %(user,host)
        cursor = conn.cursor()
        cursor.execute(query)
        show_pri = cursor.fetchall()
        for grants_command in show_pri:
            print ”.join(grants_command)+’;’
        print ”

/Users/nisj/PycharmProjects/BiDataProc/oldPythonBak/mysqlData2json.py

序号,合同编号,义务人,档案柜号,柜内编号,权利人,放款金额,放款日期,他项权利证编号,抵押物地址,结清出库日期,备注,地区

if __name__ ==’__main__’:
    conn =
MySQLdb.connect(host=’localhost’,user=’root’,passwd=’123456′,db=’mysql’,port=3306,charset=’utf8′)
    rows  = get_data(conn)
    output_data(conn,rows)

# -*- coding=utf-8 -*-
import MySQLdb
import warnings
import datetime
import sys
import json
reload(sys)
sys.setdefaultencoding('utf8')

warnings.filterwarnings("ignore")

mysqlDb_config = {
  'host': 'MysqlHostIp',
  'user': 'MysqlUser',
  'passwd': 'MysqlPass',
  'port': 50512,
  'db': 'Tv_event'
}

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
tomorrow = today + datetime.timedelta(days=1)

def getDB(dbConfigName):
  dbConfig = eval(dbConfigName)
  try:
    conn = MySQLdb.connect(host=dbConfig['host'], user=dbConfig['user'], passwd=dbConfig['passwd'],
                port=dbConfig['port'])
    conn.autocommit(True)
    curr = conn.cursor()
    curr.execute("SET NAMES utf8");
    curr.execute("USE %s" % dbConfig['db']);

    return conn, curr
  except MySQLdb.Error, e:
    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
    return None, None

def mysql2json(dbConfigName, selectSql, jsonPath, fileName):
  conn, curr = getDB(dbConfigName)
  curr.execute(selectSql)
  datas = curr.fetchall()
  fields = curr.description

  column_list = []
  for field in fields:
    column_list.append(field[0])

  with open('{jsonPath}{fileName}.json'.format(jsonPath=jsonPath, fileName=fileName), 'w+') as f:
    for row in datas:
      result = {}
      for fieldIndex in range(0, len(column_list)):
        result[column_list[fieldIndex]] = str(row[fieldIndex])
      jsondata=json.dumps(result, ensure_ascii=False)
      f.write(jsondata + '\n')
  f.close()

  curr.close()
  conn.close()

# Batch Test
dbConfigName = 'mysqlDb_config'
selectSql = "SELECT uid,name,phone_num,qq,area,created_time FROM match_apply where match_id = 83 order by created_time desc;"
jsonPath = '/Users/nisj/Desktop/'
fileName = 'mysql2json'
mysql2json(dbConfigName, selectSql, jsonPath, fileName)

mysql的创建tb_csv表语句

运行:python mysql_user_dump.py

以上那篇用Python将mysql数据导出成json的措施就是我分享给大家的全体内容了,希望能给我们二个参考,也希望大家多多帮助脚本之家。

CREATE TABLE tb_csv(
  xuhao VARCHAR(20),
  htcode VARCHAR(20),
  yiwuren VARCHAR(20),
  dagh VARCHAR(20),
  gncode VARCHAR(20),
  quanliren VARCHAR(20),
  fkmoney VARCHAR(20),
  fkdata VARCHAR(20),
  qitacode VARCHAR(20),
  diyaaddr VARCHAR(100),
  jqdata VARCHAR(30),
  beizhu VARCHAR(30),
  zone  VARCHAR(30),
  PRIMARY KEY(htcode)
)CHARSET=utf8;

复制代码 代码如下:

你或然感兴趣的稿子:

  • Python三贯彻将地点JSON大数据文件写入MySQL数据库的章程
  • Python实现将MySQL数据库表中的数据导出生成csv格式文件的不二等秘书诀
  • Python之csv文件从MySQL数据库导入导出的法子
  • python批量导出导入MySQL用户的点子

此间为了便利,表中的字段都指明varchar类型

GRANT REPLICATION SLAVE ON *.* TO ‘rep’@’192.168.234.%’ IDENTIFIED BY
PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’;

MySQL数据库写入csv文件

GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ IDENTIFIED BY
PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’ WITH GRANT OPTION;

import pymysql
import csv
import codecs
def get_conn():
  conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test_csv', charset='utf8')
  return conn
def query_all(cur, sql, args):
  cur.execute(sql, args)
  return cur.fetchall()
def read_mysql_to_csv(filename):
  with codecs.open(filename=filename, mode='w', encoding='utf-8') as f:
    write = csv.writer(f, dialect='excel')
    conn = get_conn()
    cur = conn.cursor()
    sql = 'select * from tb_csv'
    results = query_all(cur=cur, sql=sql, args=None)
    for result in results:
      print(result)
      write.writerow(result)
if __name__ == '__main__':
  read_mysql_to_csv('2.csv')

GRANT ALL PRIVILEGES ON *.* TO ‘root’@’192.168.234.%’ IDENTIFIED BY
PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’;

上述那篇Python之csv文件从MySQL数据库导入导出的方法即是小编分享给大家的全部内容了,希望能给大家1个参考,也目的在于我们多多帮衬脚本之家。

GRANT USAGE ON *.* TO ‘test’@’192.168.234.%’ IDENTIFIED BY PASSWORD
‘*2A032F7C5BA932872F0F045E0CF6B53CF702F2C5’;
GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO
‘test’@’192.168.234.%’;

你也许感兴趣的文章:

  • Python三落实将地面JSON大数据文件写入MySQL数据库的不二法门
  • Python达成将MySQL数据库表中的数码导出生成csv格式文件的法子
  • python批量导出导入MySQL用户的点子
  • 用Python将mysql数据导出成json的法门

GRANT USAGE ON *.* TO ‘zzz_test’@’192.168.234.%’ IDENTIFIED BY
PASSWORD ‘*2A032F7C5BA932872F0F045E0CF6B53CF702F2C5’;
GRANT SELECT, INSERT, UPDATE, DELETE ON `zzz%`.* TO
‘zzz_test’@’192.168.234.%’;

终极把那些命令在B上面实行就好了,也可以在推行脚本的时候重定向到三个sql文件:如:user.sql,在到B服务器的数据Curry面施行source
user.sql 就成功了导入职业。
亚洲城ca88手机版官网,第二个措施最棒,不需求一里面包车型大巴删表和重建表的操作,最安全。

您或许感兴趣的篇章:

  • Python3达成将本地JSON大数据文件写入MySQL数据库的法子
  • Python达成将MySQL数据库表中的数量导出生成csv格式文件的主意
  • Python之csv文件从MySQL数据库导入导出的措施
  • 用Python将mysql数据导出成json的不二等秘书诀

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图