• R/O
  • SSH

execsql: Commit

Default repository for execsql.py


Commit MetaInfo

Révision4190a6070f1e5ebc1a6a8b64eba4fea99406f123 (tree)
l'heure2022-01-04 05:47:47
Auteurrdnielsen
Commiterrdnielsen

Message de Log

Changed to use the openpyxl library to read .xlsx files.

Change Summary

Modification

diff -r a46edb2436df -r 4190a6070f1e CHANGELOG.rst
--- a/CHANGELOG.rst Wed Dec 22 13:20:02 2021 -0800
+++ b/CHANGELOG.rst Mon Jan 03 12:47:47 2022 -0800
@@ -1,6 +1,7 @@
11 ========== ========== =================================================================================
22 Version Date Features
33 ========== ========== =================================================================================
4+1.96.0 2022-01-03 Reading of .xlsx files now uses the openpyxl library--a new requirement.
45 1.95.0 2021-12-03 The SYSTEM_CMD metacommand now logs the command to execsql.log.
56 1.94.0 2021-10-19 Modified the INCLUDE and IMPORT metacommands to recognize leading tildes on the filename, and added the $PATHSEP system variable.
67 1.93.0 2021-10-02 Added the USER variant of the CONNECT metacommand.
diff -r a46edb2436df -r 4190a6070f1e doc/source/conf.py
--- a/doc/source/conf.py Wed Dec 22 13:20:02 2021 -0800
+++ b/doc/source/conf.py Mon Jan 03 12:47:47 2022 -0800
@@ -50,7 +50,7 @@
5050
5151 # General information about the project.
5252 project = u'execsql'
53-copyright = u'2017-2021, Dreas Nielsen'
53+copyright = u'2017-2022, Dreas Nielsen'
5454 author = u'Dreas Nielsen'
5555
5656 # The version info for the project you're documenting, acts as replacement for
@@ -58,9 +58,9 @@
5858 # built documents.
5959 #
6060 # The short X.Y version.
61-version = u'1.95'
61+version = u'1.96'
6262 # The full version, including alpha/beta/rc tags.
63-release = u'1.95'
63+release = u'1.96'
6464
6565 # A string of reStructuredText that will be included at the beginning of
6666 # every source file that is read.
diff -r a46edb2436df -r 4190a6070f1e doc/source/copyright.rst
--- a/doc/source/copyright.rst Wed Dec 22 13:20:02 2021 -0800
+++ b/doc/source/copyright.rst Mon Jan 03 12:47:47 2022 -0800
@@ -3,7 +3,7 @@
33 Copyright and License
44 ================================
55
6-Copyright (c) 2007-2021 R.Dreas Nielsen
6+Copyright (c) 2007-2022 R.Dreas Nielsen
77
88 This program is free software: you can redistribute it and/or modify it
99 under the terms of the GNU General Public License as published by the
diff -r a46edb2436df -r 4190a6070f1e doc/source/requirements.rst
--- a/doc/source/requirements.rst Wed Dec 22 13:20:02 2021 -0800
+++ b/doc/source/requirements.rst Mon Jan 03 12:47:47 2022 -0800
@@ -26,25 +26,26 @@
2626 must be installed. The libraries required for each database or spreadsheet
2727 application are:
2828
29- * PostgreSQL: `psycopg2 <http://initd.org/psycopg/>`_.
30-
31- * Firebird: `fdb <https://pypi.python.org/pypi/fdb/>`_.
29+ * PostgreSQL: `psycopg2 <https://pypi.org/project/psycopg2/>`_.
3230
33- * MySQL or MariaDB: `pymysql <https://pypi.python.org/pypi/PyMySQL/>`_.
31+ * Firebird: `fdb <https://pypi.org/project/fdb/>`_.
3432
35- * SQL Server: `pyodbc <https://pypi.python.org/pypi/pyodbc/>`_.
33+ * MySQL or MariaDB: `pymysql <https://pypi.org/project/PyMySQL/>`_.
3634
37- * MS-Access: `pyodbc <https://pypi.python.org/pypi/pyodbc/>`_
35+ * SQL Server: `pyodbc <https://pypi.org/project/pyodbc/>`_.
36+
37+ * MS-Access: `pyodbc <https://pypi.org/project/pyodbc/>`_
3838 and `pywin32 <https://pypi.org/project/pywin32/>`_.
3939
4040 * Oracle: `cx-Oracle <https://pypi.org/project/cx-Oracle/>`_.
4141
42- * DSN data source: `pyodbc <https://pypi.python.org/pypi/pyodbc/>`_.
42+ * DSN data source: `pyodbc <https://pypi.org/project/pyodbc/>`_.
4343
4444 * `OpenDocument <http://www.opendocumentformat.org/>`_ spreadsheets:
45- `odfpy <https://pypi.python.org/pypi/odfpy/>`_.
45+ `odfpy <https://pypi.org/project/odfpy/>`_.
4646
47- * Excel spreadsheets (read only): `xlrd <https://pypi.python.org/pypi/xlrd>`_.
47+ * Excel spreadsheets (read only): `xlrd <https://pypi.org/project/xlrd>`_ for .xls files
48+ and `openpyxl <https://pypi.org/project/openpyxl/>`_ for .xlsx files.
4849
4950
5051 Connections to SQLite databases are made using Python's standard library, so no
diff -r a46edb2436df -r 4190a6070f1e execsql/execsql.py
--- a/execsql/execsql.py Wed Dec 22 13:20:02 2021 -0800
+++ b/execsql/execsql.py Mon Jan 03 12:47:47 2022 -0800
@@ -14,7 +14,7 @@
1414 # Dreas Nielsen (RDN)
1515 #
1616 # COPYRIGHT AND LICENSE
17-# Copyright (c) 2007, 2008, 2009, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021 R.Dreas Nielsen
17+# Copyright (c) 2007, 2008, 2009, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022 R.Dreas Nielsen
1818 # This program is free software: you can redistribute it and/or modify
1919 # it under the terms of the GNU General Public License as published by
2020 # the Free Software Foundation, either version 3 of the License, or
@@ -27,12 +27,12 @@
2727 #
2828 # ===============================================================================
2929
30-__version__ = "1.95.1"
31-__vdate = "2021-12-22"
30+__version__ = "1.96.0"
31+__vdate = "2022-01-03"
3232
3333 primary_vno = 1
34-secondary_vno = 95
35-tertiary_vno = 1
34+secondary_vno = 96
35+tertiary_vno = 0
3636
3737 import os
3838 import os.path
@@ -96,8 +96,10 @@
9696 global odf
9797 # json is used to write JSON
9898 global json
99-# xlrd is used to read Excel files
99+# xlrd is used to read .xls Excel files
100100 global xlrd
101+# openpyxl is used to read .xlsx Excel files
102+global openpyxl
101103 # imports for Encrypt
102104 global itertools
103105 global base64
@@ -1549,7 +1551,7 @@
15491551 global xlrd
15501552 import xlrd
15511553 except:
1552- fatal_error("The xlrd library is needed to read Excel spreadsheets.")
1554+ fatal_error("The xlrd library is needed to read Excel (.xls) spreadsheets.")
15531555 self.filename = None
15541556 self.encoding = None
15551557 self.wbk = None
@@ -1651,6 +1653,95 @@
16511653
16521654
16531655
1656+class XlsxFileError(Exception):
1657+ def __init__(self, error_message):
1658+ self.value = error_message
1659+ def __repr__(self):
1660+ return u"XlsxFileError(%r)" % self.value
1661+ def __str__(self):
1662+ return repr(self.value)
1663+
1664+class XlsxFile(object):
1665+ def __repr__(self):
1666+ return u"XlsxFile()"
1667+ class XlsxLog(object):
1668+ def __init__(self):
1669+ self.log_msgs = []
1670+ def write(self, msg):
1671+ self.log_msgs.append(msg)
1672+ def __init__(self):
1673+ try:
1674+ global openpyxl
1675+ import openpyxl
1676+ except:
1677+ fatal_error("The openpyxl library is needed to read Excel (.xlsx) spreadsheets.")
1678+ self.filename = None
1679+ self.encoding = None
1680+ self.wbk = None
1681+ self.errlog = self.XlsxLog()
1682+ def open(self, filename, encoding=None):
1683+ self.filename = filename
1684+ self.encoding = encoding
1685+ if os.path.isfile(filename):
1686+ self.wbk = openpyxl.load_workbook(filename)
1687+ else:
1688+ raise XlsxFileError("There is no Excel file %s." % self.filename)
1689+ def sheet_named(self, sheetname):
1690+ # Return the sheet with the matching name. If the name is actually an integer,
1691+ # return that sheet number.
1692+ if isinstance(sheetname, int):
1693+ sheet_no = sheetname
1694+ else:
1695+ try:
1696+ sheet_no = int(sheetname)
1697+ if sheet_no < 1:
1698+ sheet_no = None
1699+ except:
1700+ sheet_no = None
1701+ if sheet_no is not None:
1702+ # User-specified sheet numbers should be 1-based
1703+ sheet = self.wbk[self.wbk.sheetnames[sheet_no - 1]]
1704+ else:
1705+ sheet = self.wbk[sheetname]
1706+ return sheet
1707+ def sheet_data(self, sheetname, junk_header_rows=0):
1708+ try:
1709+ sheet = self.sheet_named(sheetname)
1710+ except:
1711+ raise XlsxFileError("There is no Excel worksheet named %s in %s." % (sheetname, self.filename))
1712+ # Don't rely on sheet.max_column and sheet.max_row, because Excel will count columns
1713+ # and rows that have ever been filled, even if they are now empty. Base the column count
1714+ # on the number of contiguous non-empty cells in the first row, and process the data up to nrows until
1715+ # a row is entirely empty.
1716+ # Get the header row, skipping junk rows
1717+ rowsrc = sheet.iter_rows(max_row = junk_header_rows + 1, values_only = True)
1718+ for hdr_row in rowsrc:
1719+ pass
1720+ # Get the number of columns
1721+ ncols = 0
1722+ for c in range(len(hdr_row)):
1723+ if not hdr_row[c]:
1724+ break
1725+ ncols += 1
1726+ # Get all the data rows
1727+ sheet_data = []
1728+ rowsrc = sheet.iter_rows(min_row = junk_header_rows + 1, values_only = True)
1729+ for r in rowsrc:
1730+ if not any(r):
1731+ break
1732+ sheet_data.append(list(r))
1733+ for r in range(len(sheet_data)):
1734+ rd = sheet_data[r]
1735+ for c in range(len(rd)):
1736+ if isinstance(rd[c], stringtypes):
1737+ if rd[c] == '=FALSE()':
1738+ rd[c] = False
1739+ elif rd[c] == '=TRUE()':
1740+ rd[c] = True
1741+ return sheet_data
1742+
1743+
1744+
16541745 # End of file I/O.
16551746 #===============================================================================================
16561747
@@ -13337,7 +13428,15 @@
1333713428
1333813429 def xls_data(filename, sheetname, junk_header_rows, encoding=None):
1333913430 # Returns the data from the specified worksheet as a list of headers and a list of lists of rows.
13340- wbk = XlsFile()
13431+ if len(filename) < 4:
13432+ raise ErrInfo(type="cmd", other_msg="%s is not a recognizable Excel spreadsheet name." % filename)
13433+ ext3 = filename[-3:].lower()
13434+ if ext3 == "xls":
13435+ wbk = XlsFile()
13436+ elif ext3 == 'lsx':
13437+ wbk = XlsxFile()
13438+ else:
13439+ raise ErrInfo(type="cmd", other_msg="%s is not a recognizable Excel spreadsheet name." % filename)
1334113440 try:
1334213441 wbk.open(filename, encoding)
1334313442 except:
@@ -13351,7 +13450,7 @@
1335113450 if len(alldata) == 1:
1335213451 return alldata[0], []
1335313452 colhdrs = alldata[0]
13354- if any([x is None or len(x)==0 for x in colhdrs]):
13453+ if any([x is None or (isinstance(x, stringtypes) and len(x)==0) for x in colhdrs]):
1335513454 if conf.create_col_hdrs:
1335613455 for i in range(len(colhdrs)):
1335713456 if colhdrs[i] is None or len(colhdrs[i]) == 0:
diff -r a46edb2436df -r 4190a6070f1e setup.py
--- a/setup.py Wed Dec 22 13:20:02 2021 -0800
+++ b/setup.py Mon Jan 03 12:47:47 2022 -0800
@@ -5,7 +5,7 @@
55 long_description = f.read()
66
77 setuptools.setup(name='execsql',
8- version='1.95.1',
8+ version='1.96.0',
99 description="Runs a SQL script against a PostgreSQL, MS-Access, SQLite, MS-SQL-Server, MySQL, MariaDB, Firebird, or Oracle database, or an ODBC DSN. Provides metacommands to import and export data, copy data between databases, conditionally execute SQL and metacommands, and dynamically alter SQL and metacommands with substitution variables. Data can be exported in 18 different formats, including CSV, TSV, ODS, HTML, JSON, LaTeX, and Markdown tables, and using custom templates.",
1010 author='Dreas Nielsen',
1111 author_email='dreas.nielsen@gmail.com',
diff -r a46edb2436df -r 4190a6070f1e templates/script_template.sql
--- a/templates/script_template.sql Wed Dec 22 13:20:02 2021 -0800
+++ b/templates/script_template.sql Mon Jan 03 12:47:47 2022 -0800
@@ -50,13 +50,30 @@
5050 -- These settings may need to be changed for some uses of the script.
5151 -- ------------------------------------------------------------------------------
5252
53+-- The "output_dir" variable will be set to a run-specific directory name that
54+-- is dynamically created and is different for each run of this script. The
55+-- run-specific output directories will be created underneath a parent directory.
56+-- The path to that parent directory is specified here. By default, the parent
57+-- directory is named "DB_output" and is under the current directory.
58+-- !x! sub output_parent DB_output
59+
60+-- Create a unique number for every run, with corresponding output
61+-- directories and optionally a narrative descriptions.
62+-- !x! sub do_run_numbering True
63+-- Prompt for a run description?
64+-- !x! sub get_run_description True
65+
5366 -- Path to the SQL script library.
5467 ---- !x! sub script_library /path/to/sql/library
5568
56--- Change the base filename of the logfile as appropriate (e.g., to "data_loading").
69+-- Change the base filename of the logfile as appropriate.
70+-- If run numbering is enabled, the logfile path and name that are set here
71+-- will be changed in the initialization section of this script.
5772 -- !x! sub logfile logfiles/logfile_!!$date_tag!!.txt
5873
5974 -- Flag to control whether the custom logfile is rewritten each time.
75+-- If run numbering is enabled, this ordinarily will have no effect because
76+-- a new logfile will be created for each run.
6077 -- !x! sub clean_logfile No
6178
6279 -- The actions to be carried out by this script; this information will be
@@ -72,9 +89,19 @@
7289 ---- !x! sub metadata_comment
7390
7491 -- Define a user-specific staging schema. This prevents multiple users from
75--- creating conflicting staging tables.
92+-- creating conflicting staging tables. Not all DBMSs support schemas.
7693 -- !x! sub staging stg_!!$db_user!!
94+-- The current user must have permission to create a schema. Errors are
95+-- ignored because not all DBMSs support schemas.
96+-- !x! error_halt off
7797 create schema if not exists !!staging!!;
98+-- !x! error_halt on
99+
100+-- !x! if(not !!do_run_numbering!!)
101+ -- Use an date-tagged directory name for "output_dir".
102+ -- !x! sub output_dir !!output_parent!!!!$pathsep!!!!$date_tag!!
103+-- !x! endif
104+
78105
79106 -- Allow settings from a custom configuration file, 'custom.conf',
80107 -- to add to or replace configuration settings.
@@ -91,6 +118,12 @@
91118
92119
93120 -- ------------------------------------------------------------------------------
121+-- Dataset-specific configuration
122+-- ------------------------------------------------------------------------------
123+
124+
125+-- ------------------------------------------------------------------------------
126+-- ------------------------------------------------------------------------------
94127 -- Run-specific configuration
95128 -- ------------------------------------------------------------------------------
96129 -- Flag to control the amount of information displayed.
@@ -105,7 +138,7 @@
105138 -- Flag to control whether transactions are to be committed.
106139 -- !x! sub do_commit Yes
107140
108--- Flag to controle execution of debugging code.
141+-- Flag to control execution of debugging code.
109142 -- !x! sub do_debug No
110143
111144
@@ -129,11 +162,53 @@
129162 -- Initialization
130163 -- ==============================================================================
131164
165+-- ------------------------------------------------------------------------------
166+-- Set up the run number, get a run description, and save it.
167+-- A run number is always assigned, but it is used for the output directory
168+-- and logfile prefix only if the option is specified.
169+-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
170+-- !x! if(file_exists(DB_run.conf))
171+ -- !x! sub_ini file DB_run.conf section run
172+-- !x! endif
173+-- !x! if(not sub_defined(run_no))
174+ -- !x! sub run_no 0
175+-- !x! endif
176+-- !x! sub_add run_no 1
177+-- !x! rm_file DB_run.conf
178+-- !x! write "# Automatically-generated database run number setting. Do not edit." to DB_run.conf
179+-- !x! write "[run]" to DB_run.conf
180+-- !x! write "run_no=!!run_no!!" to DB_run.conf
181+-- !x! sub run_tag !!run_no!!
182+-- !x! if(not is_gt(!!run_no!!, 9))
183+ -- !x! sub run_tag 0!!run_tag!!
184+-- !x! endif
185+-- !x! if(not is_gt(!!run_no!!, 99))
186+ -- !x! sub run_tag 0!!run_tag!!
187+-- !x! endif
188+-- !x! if(!!do_run_numbering!!)
189+ -- !x! sub output_dir !!data_dir!!!!$pathsep!!Run_!!run_tag!!_!!$date_tag!!
190+ -- !x! sub logfile logfiles!!$pathsep!!Run_!!run_tag!!_logfile.txt
191+ -- !x! if(!!get_run_description!!)
192+ -- !x! prompt enter_sub run_description message "Please enter a description for this run (!!run_tag!!)"
193+ -- !x! if(not sub_empty(run_description))
194+ -- !x! write "!!run_description!!" to !!output_dir!!!!$pathsep!!run_description.txt
195+ -- !x! export query <<select !!run_no!! as "Run_no", '!!run_description!!' as "Description";>> append to DB_run_descriptions.csv as csv
196+ -- !x! endif
197+ -- !x! endif
198+-- !x! endif
199+-- ------------------------------------------------------------------------------
200+
201+
202+
132203 -- !x! console on
133204
134205 -- !x! if(!!clean_logfile!!) {rm_file !!logfile!!}
135206
136207 -- Write a prologue to the logfile.
208+-- !x! if(file_exists(!!logfile!!))
209+ -- !x! write "" to !!logfile!!
210+ -- !x! write "" to !!logfile!!
211+-- !x! endif
137212 -- !x! write "==============================================================" to !!logfile!!
138213 -- !x! if(sub_defined(script_purpose))
139214 -- !x! write "!!script_purpose!!" tee to !!logfile!!
@@ -214,7 +289,6 @@
214289 -- !x! begin script crashed
215290 -- !x! write "" tee to !!logfile!!
216291 -- !x! write "Script halted by an error." tee to !!logfile!!
217- -- !x! write #The error message was: !!$error_message!!# to !!logfile!!
218292 -- !x! execute script cleanup
219293 -- !x! write "" to !!logfile!!
220294 -- !x! end script crashed
Afficher sur ancien navigateur de dépôt.