4Qx3s3r7

1#!/usr/bin/env python
2# -*- coding: utf-8 -*-
3
4"""
5dbf2oracle - convert dbf files into Oracle database
6
7Todo:
8- -v --verbose option
9- handle existing table (-f option?)
10- primary key option? (make first column primary key)
11- create only option?
12- insert only option?
13- options to select columns to insert?
14"""
15
16import os
17import sys
18import argparse
19import traceback
20
21from dbfread import DBF
22try:
23    import cx_Oracle
24except (ImportError, e):
25    raise ImportError(
26        str(e) + """ Was not found. Make sure you have it installed""")
27
28username = 'DbUser'
29password = 'secret'
30sid = 'ORCL'
31
32typemap = {
33    'F': 'NUMBER',
34    'L': 'VARCHAR2',
35    'I': 'INTEGER',
36    'C': 'VARCHAR2',
37    'N': 'NUMBER',  # because it can be integer or float
38    'M': 'VARCHAR2',
39    'D': 'DATE',
40    'T': 'DATE',
41    '0': 'INTEGER',
42}
43
44
45def add_table(cursor, table):
46    """Add a dbase table to an open Oracle database."""
47
48    # cursor.execute('drop table if exists %s' % table.name)
49
50    field_types = {}
51    for f in table.fields:
52        field_types[f.name] = typemap.get(f.type, 'VARCHAR2')
53
54    #
55    # Create the table
56    #
57    defs = ', '.join(['%s %s' % (f, field_types[f])
58                      for f in table.field_names])
59    sql = 'create table "%s" (%s)' % (table.name, defs)
60    # print(sql)
61    cursor.execute(sql)
62
63    # Create data rows
64    refs = ', '.join([':' + f for f in table.field_names])
65    sql = 'insert into %s values (%s)' % (table.name, refs)
66    print(sql)
67    i = 0
68    for rec in table:
69        i = i+1
70        # print(list(rec.values()))
71        cursor.execute(sql, list(rec.values()))
72        if i == 5000:
73            print("5000 registros. Commit....")
74            i = 0
75            cursor.connection.commit()
76
77
78def parse_args():
79    parser = argparse.ArgumentParser(
80        description='usage: %prog [OPTIONS] table1.dbf ... tableN.dbf')
81    arg = parser.add_argument
82
83    arg('-e', '--encoding',
84        action='store',
85        dest='encoding',
86        default=None,
87        help='character encoding in DBF file')
88
89    arg('--char-decode-errors',
90        action='store',
91        dest='char_decode_errors',
92        default='strict',
93        help='how to handle decode errors (see pydoc bytes.decode)')
94
95    arg('tables',
96        metavar='TABLE',
97        nargs='+',
98        help='tables to add to Oracle database')
99
100    return parser.parse_args()
101
102
103def main():
104    args = parse_args()
105
106    conn = cx_Oracle.connect(username, password, sid, encoding="cp850")
107    cursor = conn.cursor()
108
109    for table_file in args.tables:
110        try:
111            add_table(cursor, DBF(table_file,
112                                  lowernames=True,
113                                  encoding=args.encoding,
114                                  char_decode_errors=args.char_decode_errors))
115        except UnicodeDecodeError as err:
116            traceback.print_exc()
117            sys.exit('Please use --encoding or --char-decode-errors.')
118
119    conn.commit()
120
121
122if __name__ == '__main__':
123    main()