Qiang Blog

Just another zhangjingqiang's blog.

用Python脚本导入文件中数据到MySQL数据库

#!/usr/bin/python
# coding: UTF-8

import sys
import os

reload(sys)
sys.setdefaultencoding('utf8')

def main():
    if len(sys.argv) == 2 and os.path.isdir(sys.argv[1]):
        make_file()
    else:
        print 'Please input a regular path'

def make_file():
    files = os.listdir(sys.argv[1])
    print files
    for file in files:
        if '.tsv' in file and '.swp' not in file:
            os.system("iconv -f SHIFT-JIS -t UTF-8 {0} > {1}".format(sys.argv[1] + "/" + file, 'tmp'))
            file_name = ''
            # Error Report
            if 'errors_report' in file:
                if 'English Error' in open('tmp').read():
                    file_name = 'errors_report_en'
                    os.system("mv tmp {0}".format(file_name))
                elif 'Japanese Error' in open('tmp').read():
                    file_name = 'errors_report_ja'
                    os.system("mv tmp {0}".format(file_name))
            # Warning Report
            if 'warnings_report' in file:
                file_name = 'warnings_report'
                os.system("mv tmp {0}".format(file_name))

            _import(file_name)
            _remove_tmp(file_name)

def _import(file_name):
    """
    Import to MySQL
    """
    os.system("mysqlimport -u root --local caption {0}".format(file_name))

def _remove_tmp(file_name):
    """
    Remove renamed file
    """
    os.system("rm {0}".format(file_name))

if __name__ == "__main__":
    main()

这个脚本可以把一个目录中的所有文件读入数据库,并且根据文件中数据不同,读入不同的表。例如数据库中有这样的表:

  • errorsreporten
  • errorsreportja
  • warnings_report

表中不用ID,完全和文件中的数据格式一致。对所有字段加uniq索引,确保读入的数据不重复插入。
此例目标文件为 .tsv 文件,编码不是UTF-8,要先转码。

脚本使用方法:

python import_to_db.py <path>

mysql python

Deploy Rails with Docker

A sample using Rails + MySQL

Install Docker on Mac OS X

http://docs.docker.com/engine/installation/mac/

Create docker machine

$ docker-machine create --driver virtualbox testapp

Import ENV

eval "$(docker-machine env testapp)"

Create Dockerfile

FROM ruby:2.2.0
RUN apt-get update -qq && apt-get install -y build-essential mysql-client libmysqlclient-dev nodejs
RUN mkdir /testapp
WORKDIR /testapp
ADD Gemfile /testapp/Gemfile
ADD Gemfile.lock /testapp/Gemfile.lock
RUN bundle install
ADD . /testapp

Create docker-compose.yml

db:
  image: mysql
  environment:
    MYSQL_ROOT_PASSWORD: 1234
  ports:
    - "3306:3306"
web:
  build: .
  command: bundle exec rails s -p 3000 -b '0.0.0.0'
  volumes:
    - .:/testapp
  ports:
    - "3000:3000"
  links:
    - db

Modify config/database.yml

default: &default
  adapter: mysql2
  encoding: utf8
  pool: 5
  username: root
  password: 1234
  host: db

development:
  <<: *default
  database: testapp_development

test:
  <<: *default
  database: testapp_test

production:
  <<: *default
  database: testapp_production

Build image

$ docker-compose build

Boot app

$ docker-compose up

Create database

$ docker-compose run web rake db:create

Get docker machine's ip

$ docker-machine ip testapp

Access app

http://[IP_ADDRESS]:3000


Reference

https://docs.docker.com/compose/rails/

docker mysql ruby-on-rails