Coverage for src/pyensae/datasource/convert.py: 97%

34 statements  

« prev     ^ index     » next       coverage.py v7.2.7, created at 2023-07-03 02:16 +0200

1""" 

2@file 

3@brief Various conversion functions. 

4""" 

5import pandas 

6from pyquickhelper.loghelper import noLOG 

7from ..sql.database_main import Database 

8 

9 

10def dBase2df(file, encoding="cp437"): 

11 """ 

12 converts a dBase file into a list of dataframe (one per table) 

13 

14 @param file file name 

15 @param encoding table encoding 

16 @return list of dataframes (pandas) 

17 

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) 

24 

25 

26def dBase2sqllite( 

27 db, table, encoding="cp437", overwrite_table=None, fLOG=noLOG): 

28 """ 

29 Put all rows from a dBase database into sqlite 

30 

31 Add a dbase table to an open sqlite database. 

32 

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 

38 

39 The table will be removed if it exists. 

40 """ 

41 

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 } 

53 

54 if isinstance(db, str): 

55 cursor = Database(db, LOG=fLOG) 

56 cursor.connect() 

57 else: 

58 cursor = db 

59 

60 if isinstance(table, str): 

61 import dbfread # pylint: disable=C0415 

62 table = dbfread.DBF(table, load=False, encoding=encoding) 

63 

64 cursor.execute('drop table if exists %s' % table.name) 

65 

66 field_types = {} 

67 for f in table.fields: 

68 field_types[f.name] = typemap.get(f.type, 'TEXT') 

69 

70 table_name = overwrite_table if overwrite_table is not None else table.name 

71 

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) 

78 

79 # Create data rows 

80 refs = ', '.join([':' + f for f in table.field_names]) 

81 sql = 'insert into %s values (%s)' % (table_name, refs) 

82 

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) 

87 

88 if isinstance(db, str): 

89 cursor.commit() 

90 cursor.close()