将 Django 项目从 Sqlite迁移至 PostgreSQL

20 2017-02-07 08:36:11

前言

由于自己之前对数据库不熟悉,在创建Django应用的时候,默认采用了自带的Sqlite。当网站的数据量变大了以后,数据查询变的越发复杂了的时候,数据库成为了网站浏览速度的瓶颈。比如,模糊查找不支持索引之类的问题,严重影响了用户体验。于是觉得迁移数据库显得越来越重要。在迁移的过程中发现了各种各样的问题。

数据迁移的方法

首先我先去了网上找了相关的迁移数据库的方法,但是只有寥寥几个干货,于是我就参照了其中一个,比如这个老蔡博客中的方法老蔡博客,虽然博客上的是Mysql对我来说参考的价值还是很大的。

参照博客中的方法创建了postgres数据库,设置好setting

把数据导出

python manage.py dumpdata --all > imtx

然后导入

python manage.py loaddata imtx.json --database=postgresql

数据量最大的一个表反而导不出来,不知道原因为何,然后想着手工导出导入。

那么多的表手工导入导出,显然是非常不现实的,当然要写个脚本了,而且出错了的话还有记录。

数据迁移的脚本

程序的思路就是把所有的表全部导出来,然后再全部导入到postgres里面去,当然再导入的过程中也出现了很多错误,出现最多的就是postgres中的列跟导出的不对应,然后导致的数据类型的错误。这种错误我能想到的只能是去把列drop掉然后再添加到合适的位置。还有一个错误就是导出的列中有空值但是对应的字段的约束为NOT NULL, 这种也把约束drop掉了。

ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;

脚本源码

#coding=utf-8
import re
import sys
import sqlite3
import csv
import psycopg2
import psycopg2.extras
import psycopg2.errorcodes

database = ""
user = ""
password = ""
host = ""
port = ""

def select_table():
    conn = sqlite3.connect('/path/db.sqlite3',timeout=30.0)
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = cursor.fetchall()
    conn.close()
    return tables

def backup_table(table):
    conn = sqlite3.connect('/path/db.sqlite3',timeout=30.0)
    cursor = conn.cursor()
    sql = "PRAGMA table_info(%s)" %(table)
    cursor.execute(sql)
    col_names = cursor.fetchall()
    #print(col_names)
    name_list = ''
    for col_name in col_names:
        name_list = name_list + col_name[1]
        if not col_name == col_names[-1]:
            name_list = name_list + ','
    #print(name_list)
    sql = 'select %s from %s' %(name_list,table)
    csvfile = '/tmp/%s.csv' %(table)
    data = cursor.execute(sql)

    with open(csvfile, 'w') as f:
        writer = csv.writer(f)
        writer.writerows(data)
def up_postgres(table):
    try:
        conn = psycopg2.connect(database=database, user=user,  password=password, host=host, port=port)
        cursor = conn.cursor()
        sql = 'TRUNCATE TABLE  %s CASCADE;' %(table)
        cursor.execute(sql)
        sql = "COPY %s FROM '/tmp/%s.csv' DELIMITER ',' CSV;" %(table, table)
        cursor.execute(sql)
        conn.commit()
        cursor.close()
        conn.close()
    except psycopg2.Error as e :
        print(e)

if __name__ == "__main__":
    tables = select_table()
    for table in tables:
        if 'races' in table[0]:
            continue
        print(table[0])
        backup_table(table[0])
        up_postgres(table[0])