Hot-keys on this page
r m x p toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1"""
2@file
3@brief Various conversion functions.
4"""
5import pandas
6from pyquickhelper.loghelper import noLOG
7from ..sql.database_main import Database
10def dBase2df(file, encoding="cp437"):
11 """
12 converts a dBase file into a list of dataframe (one per table)
14 @param file file name
15 @param encoding table encoding
16 @return list of dataframes (pandas)
18 The module relies on `dbfread <https://pypi.python.org/pypi/dbfread/>`_.
19 """
20 import dbfread # pylint: disable=C0415
21 table = dbfread.DBF(file, load=False, encoding=encoding)
22 res = list(table)
23 return pandas.DataFrame(res)
26def dBase2sqllite(
27 db, table, encoding="cp437", overwrite_table=None, fLOG=noLOG):
28 """
29 Put all rows from a dBase database into sqlite
31 Add a dbase table to an open sqlite database.
33 @param db cursor on SQLite or file name
34 @param table DBF object or filename
35 @param encoding encoding if table is a filename
36 @param overwrite_table overwrite the table name
37 @param fLOG logging function, to see the progress
39 The table will be removed if it exists.
40 """
42 typemap = {
43 'F': 'FLOAT',
44 'L': 'BOOLEAN',
45 'I': 'INTEGER',
46 'C': 'TEXT',
47 'N': 'REAL', # because it can be integer or float
48 'M': 'TEXT',
49 'D': 'DATE',
50 'T': 'DATETIME',
51 '0': 'INTEGER',
52 }
54 if isinstance(db, str):
55 cursor = Database(db, LOG=fLOG)
56 cursor.connect()
57 else:
58 cursor = db
60 if isinstance(table, str):
61 import dbfread # pylint: disable=C0415
62 table = dbfread.DBF(table, load=False, encoding=encoding)
64 cursor.execute('drop table if exists %s' % table.name)
66 field_types = {}
67 for f in table.fields:
68 field_types[f.name] = typemap.get(f.type, 'TEXT')
70 table_name = overwrite_table if overwrite_table is not None else table.name
72 # Create the table
73 #
74 defs = ', '.join(['%s %s' % (f, field_types[f])
75 for f in table.field_names])
76 sql = 'create table %s (%s)' % (table_name, defs)
77 cursor.execute(sql)
79 # Create data rows
80 refs = ', '.join([':' + f for f in table.field_names])
81 sql = 'insert into %s values (%s)' % (table_name, refs)
83 for i, rec in enumerate(table):
84 cursor._connection.execute(sql, list(rec.values()))
85 if i % 20000 == 0:
86 fLOG("moving line ", i, " to table", table_name)
88 if isinstance(db, str):
89 cursor.commit()
90 cursor.close()