diff options
Diffstat (limited to 'python-csvs-to-sqlite.spec')
| -rw-r--r-- | python-csvs-to-sqlite.spec | 667 |
1 files changed, 667 insertions, 0 deletions
diff --git a/python-csvs-to-sqlite.spec b/python-csvs-to-sqlite.spec new file mode 100644 index 0000000..9aef4ec --- /dev/null +++ b/python-csvs-to-sqlite.spec @@ -0,0 +1,667 @@ +%global _empty_manifest_terminate_build 0 +Name: python-csvs-to-sqlite +Version: 1.3 +Release: 1 +Summary: Convert CSV files into a SQLite database +License: Apache License, Version 2.0 +URL: https://github.com/simonw/csvs-to-sqlite +Source0: https://mirrors.nju.edu.cn/pypi/web/packages/c1/48/5ddd047d3e76fbee30b2aaece88568e4fa42bef0e6011f61f0b364eeb1b2/csvs_to_sqlite-1.3.tar.gz +BuildArch: noarch + +Requires: python3-click +Requires: python3-dateparser +Requires: python3-pandas +Requires: python3-py-lru-cache +Requires: python3-six +Requires: python3-pytest +Requires: python3-cogapp + +%description +# csvs-to-sqlite + +[](https://pypi.org/project/csvs-to-sqlite/) +[](https://github.com/simonw/csvs-to-sqlite/releases) +[](https://github.com/simonw/csvs-to-sqlite/actions?query=workflow%3ATest) +[](https://github.com/simonw/csvs-to-sqlite/blob/main/LICENSE) + +Convert CSV files into a SQLite database. Browse and publish that SQLite database with [Datasette](https://github.com/simonw/datasette). + +Basic usage: + + csvs-to-sqlite myfile.csv mydatabase.db + +This will create a new SQLite database called `mydatabase.db` containing a +single table, `myfile`, containing the CSV content. + +You can provide multiple CSV files: + + csvs-to-sqlite one.csv two.csv bundle.db + +The `bundle.db` database will contain two tables, `one` and `two`. + +This means you can use wildcards: + + csvs-to-sqlite ~/Downloads/*.csv my-downloads.db + +If you pass a path to one or more directories, the script will recursively +search those directories for CSV files and create tables for each one. + + csvs-to-sqlite ~/path/to/directory all-my-csvs.db + +## Handling TSV (tab-separated values) + +You can use the `-s` option to specify a different delimiter. If you want +to use a tab character you'll need to apply shell escaping like so: + + csvs-to-sqlite my-file.tsv my-file.db -s $'\t' + +## Refactoring columns into separate lookup tables + +Let's say you have a CSV file that looks like this: + + county,precinct,office,district,party,candidate,votes + Clark,1,President,,REP,John R. Kasich,5 + Clark,2,President,,REP,John R. Kasich,0 + Clark,3,President,,REP,John R. Kasich,7 + +([Real example taken from the Open Elections project](https://github.com/openelections/openelections-data-sd/blob/master/2016/20160607__sd__primary__clark__precinct.csv)) + +You can now convert selected columns into separate lookup tables using the new +`--extract-column` option (shortname: `-c`) - for example: + + csvs-to-sqlite openelections-data-*/*.csv \ + -c county:County:name \ + -c precinct:Precinct:name \ + -c office -c district -c party -c candidate \ + openelections.db + +The format is as follows: + + column_name:optional_table_name:optional_table_value_column_name + +If you just specify the column name e.g. `-c office`, the following table will +be created: + + CREATE TABLE "office" ( + "id" INTEGER PRIMARY KEY, + "value" TEXT + ); + +If you specify all three options, e.g. `-c precinct:Precinct:name` the table +will look like this: + + CREATE TABLE "Precinct" ( + "id" INTEGER PRIMARY KEY, + "name" TEXT + ); + +The original tables will be created like this: + + CREATE TABLE "ca__primary__san_francisco__precinct" ( + "county" INTEGER, + "precinct" INTEGER, + "office" INTEGER, + "district" INTEGER, + "party" INTEGER, + "candidate" INTEGER, + "votes" INTEGER, + FOREIGN KEY (county) REFERENCES County(id), + FOREIGN KEY (party) REFERENCES party(id), + FOREIGN KEY (precinct) REFERENCES Precinct(id), + FOREIGN KEY (office) REFERENCES office(id), + FOREIGN KEY (candidate) REFERENCES candidate(id) + ); + +They will be populated with IDs that reference the new derived tables. + +## Installation + + $ pip install csvs-to-sqlite + +`csvs-to-sqlite` now requires Python 3. If you are running Python 2 you can install the last version to support Python 2: + + $ pip install csvs-to-sqlite==0.9.2 + +## csvs-to-sqlite --help + +<!-- [[[cog +import cog +from csvs_to_sqlite import cli +from click.testing import CliRunner +runner = CliRunner() +result = runner.invoke(cli.cli, ["--help"]) +help = result.output.replace("Usage: cli", "Usage: csvs-to-sqlite") +cog.out( + "```\n{}\n```".format(help) +) +]]] --> +``` +Usage: csvs-to-sqlite [OPTIONS] PATHS... DBNAME + + PATHS: paths to individual .csv files or to directories containing .csvs + + DBNAME: name of the SQLite database file to create + +Options: + -s, --separator TEXT Field separator in input .csv + -q, --quoting INTEGER Control field quoting behavior per csv.QUOTE_* + constants. Use one of QUOTE_MINIMAL (0), + QUOTE_ALL (1), QUOTE_NONNUMERIC (2) or + QUOTE_NONE (3). + + --skip-errors Skip lines with too many fields instead of + stopping the import + + --replace-tables Replace tables if they already exist + -t, --table TEXT Table to use (instead of using CSV filename) + -c, --extract-column TEXT One or more columns to 'extract' into a + separate lookup table. If you pass a simple + column name that column will be replaced with + integer foreign key references to a new table + of that name. You can customize the name of + the table like so: state:States:state_name + + This will pull unique values from the 'state' + column and use them to populate a new 'States' + table, with an id column primary key and a + state_name column containing the strings from + the original column. + + -d, --date TEXT One or more columns to parse into ISO + formatted dates + + -dt, --datetime TEXT One or more columns to parse into ISO + formatted datetimes + + -df, --datetime-format TEXT One or more custom date format strings to try + when parsing dates/datetimes + + -pk, --primary-key TEXT One or more columns to use as the primary key + -f, --fts TEXT One or more columns to use to populate a full- + text index + + -i, --index TEXT Add index on this column (or a compound index + with -i col1,col2) + + --shape TEXT Custom shape for the DB table - format is + csvcol:dbcol(TYPE),... + + --filename-column TEXT Add a column with this name and populate with + CSV file name + + --fixed-column <TEXT TEXT>... Populate column with a fixed string + --fixed-column-int <TEXT INTEGER>... + Populate column with a fixed integer + --fixed-column-float <TEXT FLOAT>... + Populate column with a fixed float + --no-index-fks Skip adding index to foreign key columns + created using --extract-column (default is to + add them) + + --no-fulltext-fks Skip adding full-text index on values + extracted using --extract-column (default is + to add them) + + --just-strings Import all columns as text strings by default + (and, if specified, still obey --shape, + --date/datetime, and --datetime-format) + + --version Show the version and exit. + --help Show this message and exit. + +``` +<!-- [[[end]]] --> + + + + +%package -n python3-csvs-to-sqlite +Summary: Convert CSV files into a SQLite database +Provides: python-csvs-to-sqlite +BuildRequires: python3-devel +BuildRequires: python3-setuptools +BuildRequires: python3-pip +%description -n python3-csvs-to-sqlite +# csvs-to-sqlite + +[](https://pypi.org/project/csvs-to-sqlite/) +[](https://github.com/simonw/csvs-to-sqlite/releases) +[](https://github.com/simonw/csvs-to-sqlite/actions?query=workflow%3ATest) +[](https://github.com/simonw/csvs-to-sqlite/blob/main/LICENSE) + +Convert CSV files into a SQLite database. Browse and publish that SQLite database with [Datasette](https://github.com/simonw/datasette). + +Basic usage: + + csvs-to-sqlite myfile.csv mydatabase.db + +This will create a new SQLite database called `mydatabase.db` containing a +single table, `myfile`, containing the CSV content. + +You can provide multiple CSV files: + + csvs-to-sqlite one.csv two.csv bundle.db + +The `bundle.db` database will contain two tables, `one` and `two`. + +This means you can use wildcards: + + csvs-to-sqlite ~/Downloads/*.csv my-downloads.db + +If you pass a path to one or more directories, the script will recursively +search those directories for CSV files and create tables for each one. + + csvs-to-sqlite ~/path/to/directory all-my-csvs.db + +## Handling TSV (tab-separated values) + +You can use the `-s` option to specify a different delimiter. If you want +to use a tab character you'll need to apply shell escaping like so: + + csvs-to-sqlite my-file.tsv my-file.db -s $'\t' + +## Refactoring columns into separate lookup tables + +Let's say you have a CSV file that looks like this: + + county,precinct,office,district,party,candidate,votes + Clark,1,President,,REP,John R. Kasich,5 + Clark,2,President,,REP,John R. Kasich,0 + Clark,3,President,,REP,John R. Kasich,7 + +([Real example taken from the Open Elections project](https://github.com/openelections/openelections-data-sd/blob/master/2016/20160607__sd__primary__clark__precinct.csv)) + +You can now convert selected columns into separate lookup tables using the new +`--extract-column` option (shortname: `-c`) - for example: + + csvs-to-sqlite openelections-data-*/*.csv \ + -c county:County:name \ + -c precinct:Precinct:name \ + -c office -c district -c party -c candidate \ + openelections.db + +The format is as follows: + + column_name:optional_table_name:optional_table_value_column_name + +If you just specify the column name e.g. `-c office`, the following table will +be created: + + CREATE TABLE "office" ( + "id" INTEGER PRIMARY KEY, + "value" TEXT + ); + +If you specify all three options, e.g. `-c precinct:Precinct:name` the table +will look like this: + + CREATE TABLE "Precinct" ( + "id" INTEGER PRIMARY KEY, + "name" TEXT + ); + +The original tables will be created like this: + + CREATE TABLE "ca__primary__san_francisco__precinct" ( + "county" INTEGER, + "precinct" INTEGER, + "office" INTEGER, + "district" INTEGER, + "party" INTEGER, + "candidate" INTEGER, + "votes" INTEGER, + FOREIGN KEY (county) REFERENCES County(id), + FOREIGN KEY (party) REFERENCES party(id), + FOREIGN KEY (precinct) REFERENCES Precinct(id), + FOREIGN KEY (office) REFERENCES office(id), + FOREIGN KEY (candidate) REFERENCES candidate(id) + ); + +They will be populated with IDs that reference the new derived tables. + +## Installation + + $ pip install csvs-to-sqlite + +`csvs-to-sqlite` now requires Python 3. If you are running Python 2 you can install the last version to support Python 2: + + $ pip install csvs-to-sqlite==0.9.2 + +## csvs-to-sqlite --help + +<!-- [[[cog +import cog +from csvs_to_sqlite import cli +from click.testing import CliRunner +runner = CliRunner() +result = runner.invoke(cli.cli, ["--help"]) +help = result.output.replace("Usage: cli", "Usage: csvs-to-sqlite") +cog.out( + "```\n{}\n```".format(help) +) +]]] --> +``` +Usage: csvs-to-sqlite [OPTIONS] PATHS... DBNAME + + PATHS: paths to individual .csv files or to directories containing .csvs + + DBNAME: name of the SQLite database file to create + +Options: + -s, --separator TEXT Field separator in input .csv + -q, --quoting INTEGER Control field quoting behavior per csv.QUOTE_* + constants. Use one of QUOTE_MINIMAL (0), + QUOTE_ALL (1), QUOTE_NONNUMERIC (2) or + QUOTE_NONE (3). + + --skip-errors Skip lines with too many fields instead of + stopping the import + + --replace-tables Replace tables if they already exist + -t, --table TEXT Table to use (instead of using CSV filename) + -c, --extract-column TEXT One or more columns to 'extract' into a + separate lookup table. If you pass a simple + column name that column will be replaced with + integer foreign key references to a new table + of that name. You can customize the name of + the table like so: state:States:state_name + + This will pull unique values from the 'state' + column and use them to populate a new 'States' + table, with an id column primary key and a + state_name column containing the strings from + the original column. + + -d, --date TEXT One or more columns to parse into ISO + formatted dates + + -dt, --datetime TEXT One or more columns to parse into ISO + formatted datetimes + + -df, --datetime-format TEXT One or more custom date format strings to try + when parsing dates/datetimes + + -pk, --primary-key TEXT One or more columns to use as the primary key + -f, --fts TEXT One or more columns to use to populate a full- + text index + + -i, --index TEXT Add index on this column (or a compound index + with -i col1,col2) + + --shape TEXT Custom shape for the DB table - format is + csvcol:dbcol(TYPE),... + + --filename-column TEXT Add a column with this name and populate with + CSV file name + + --fixed-column <TEXT TEXT>... Populate column with a fixed string + --fixed-column-int <TEXT INTEGER>... + Populate column with a fixed integer + --fixed-column-float <TEXT FLOAT>... + Populate column with a fixed float + --no-index-fks Skip adding index to foreign key columns + created using --extract-column (default is to + add them) + + --no-fulltext-fks Skip adding full-text index on values + extracted using --extract-column (default is + to add them) + + --just-strings Import all columns as text strings by default + (and, if specified, still obey --shape, + --date/datetime, and --datetime-format) + + --version Show the version and exit. + --help Show this message and exit. + +``` +<!-- [[[end]]] --> + + + + +%package help +Summary: Development documents and examples for csvs-to-sqlite +Provides: python3-csvs-to-sqlite-doc +%description help +# csvs-to-sqlite + +[](https://pypi.org/project/csvs-to-sqlite/) +[](https://github.com/simonw/csvs-to-sqlite/releases) +[](https://github.com/simonw/csvs-to-sqlite/actions?query=workflow%3ATest) +[](https://github.com/simonw/csvs-to-sqlite/blob/main/LICENSE) + +Convert CSV files into a SQLite database. Browse and publish that SQLite database with [Datasette](https://github.com/simonw/datasette). + +Basic usage: + + csvs-to-sqlite myfile.csv mydatabase.db + +This will create a new SQLite database called `mydatabase.db` containing a +single table, `myfile`, containing the CSV content. + +You can provide multiple CSV files: + + csvs-to-sqlite one.csv two.csv bundle.db + +The `bundle.db` database will contain two tables, `one` and `two`. + +This means you can use wildcards: + + csvs-to-sqlite ~/Downloads/*.csv my-downloads.db + +If you pass a path to one or more directories, the script will recursively +search those directories for CSV files and create tables for each one. + + csvs-to-sqlite ~/path/to/directory all-my-csvs.db + +## Handling TSV (tab-separated values) + +You can use the `-s` option to specify a different delimiter. If you want +to use a tab character you'll need to apply shell escaping like so: + + csvs-to-sqlite my-file.tsv my-file.db -s $'\t' + +## Refactoring columns into separate lookup tables + +Let's say you have a CSV file that looks like this: + + county,precinct,office,district,party,candidate,votes + Clark,1,President,,REP,John R. Kasich,5 + Clark,2,President,,REP,John R. Kasich,0 + Clark,3,President,,REP,John R. Kasich,7 + +([Real example taken from the Open Elections project](https://github.com/openelections/openelections-data-sd/blob/master/2016/20160607__sd__primary__clark__precinct.csv)) + +You can now convert selected columns into separate lookup tables using the new +`--extract-column` option (shortname: `-c`) - for example: + + csvs-to-sqlite openelections-data-*/*.csv \ + -c county:County:name \ + -c precinct:Precinct:name \ + -c office -c district -c party -c candidate \ + openelections.db + +The format is as follows: + + column_name:optional_table_name:optional_table_value_column_name + +If you just specify the column name e.g. `-c office`, the following table will +be created: + + CREATE TABLE "office" ( + "id" INTEGER PRIMARY KEY, + "value" TEXT + ); + +If you specify all three options, e.g. `-c precinct:Precinct:name` the table +will look like this: + + CREATE TABLE "Precinct" ( + "id" INTEGER PRIMARY KEY, + "name" TEXT + ); + +The original tables will be created like this: + + CREATE TABLE "ca__primary__san_francisco__precinct" ( + "county" INTEGER, + "precinct" INTEGER, + "office" INTEGER, + "district" INTEGER, + "party" INTEGER, + "candidate" INTEGER, + "votes" INTEGER, + FOREIGN KEY (county) REFERENCES County(id), + FOREIGN KEY (party) REFERENCES party(id), + FOREIGN KEY (precinct) REFERENCES Precinct(id), + FOREIGN KEY (office) REFERENCES office(id), + FOREIGN KEY (candidate) REFERENCES candidate(id) + ); + +They will be populated with IDs that reference the new derived tables. + +## Installation + + $ pip install csvs-to-sqlite + +`csvs-to-sqlite` now requires Python 3. If you are running Python 2 you can install the last version to support Python 2: + + $ pip install csvs-to-sqlite==0.9.2 + +## csvs-to-sqlite --help + +<!-- [[[cog +import cog +from csvs_to_sqlite import cli +from click.testing import CliRunner +runner = CliRunner() +result = runner.invoke(cli.cli, ["--help"]) +help = result.output.replace("Usage: cli", "Usage: csvs-to-sqlite") +cog.out( + "```\n{}\n```".format(help) +) +]]] --> +``` +Usage: csvs-to-sqlite [OPTIONS] PATHS... DBNAME + + PATHS: paths to individual .csv files or to directories containing .csvs + + DBNAME: name of the SQLite database file to create + +Options: + -s, --separator TEXT Field separator in input .csv + -q, --quoting INTEGER Control field quoting behavior per csv.QUOTE_* + constants. Use one of QUOTE_MINIMAL (0), + QUOTE_ALL (1), QUOTE_NONNUMERIC (2) or + QUOTE_NONE (3). + + --skip-errors Skip lines with too many fields instead of + stopping the import + + --replace-tables Replace tables if they already exist + -t, --table TEXT Table to use (instead of using CSV filename) + -c, --extract-column TEXT One or more columns to 'extract' into a + separate lookup table. If you pass a simple + column name that column will be replaced with + integer foreign key references to a new table + of that name. You can customize the name of + the table like so: state:States:state_name + + This will pull unique values from the 'state' + column and use them to populate a new 'States' + table, with an id column primary key and a + state_name column containing the strings from + the original column. + + -d, --date TEXT One or more columns to parse into ISO + formatted dates + + -dt, --datetime TEXT One or more columns to parse into ISO + formatted datetimes + + -df, --datetime-format TEXT One or more custom date format strings to try + when parsing dates/datetimes + + -pk, --primary-key TEXT One or more columns to use as the primary key + -f, --fts TEXT One or more columns to use to populate a full- + text index + + -i, --index TEXT Add index on this column (or a compound index + with -i col1,col2) + + --shape TEXT Custom shape for the DB table - format is + csvcol:dbcol(TYPE),... + + --filename-column TEXT Add a column with this name and populate with + CSV file name + + --fixed-column <TEXT TEXT>... Populate column with a fixed string + --fixed-column-int <TEXT INTEGER>... + Populate column with a fixed integer + --fixed-column-float <TEXT FLOAT>... + Populate column with a fixed float + --no-index-fks Skip adding index to foreign key columns + created using --extract-column (default is to + add them) + + --no-fulltext-fks Skip adding full-text index on values + extracted using --extract-column (default is + to add them) + + --just-strings Import all columns as text strings by default + (and, if specified, still obey --shape, + --date/datetime, and --datetime-format) + + --version Show the version and exit. + --help Show this message and exit. + +``` +<!-- [[[end]]] --> + + + + +%prep +%autosetup -n csvs-to-sqlite-1.3 + +%build +%py3_build + +%install +%py3_install +install -d -m755 %{buildroot}/%{_pkgdocdir} +if [ -d doc ]; then cp -arf doc %{buildroot}/%{_pkgdocdir}; fi +if [ -d docs ]; then cp -arf docs %{buildroot}/%{_pkgdocdir}; fi +if [ -d example ]; then cp -arf example %{buildroot}/%{_pkgdocdir}; fi +if [ -d examples ]; then cp -arf examples %{buildroot}/%{_pkgdocdir}; fi +pushd %{buildroot} +if [ -d usr/lib ]; then + find usr/lib -type f -printf "/%h/%f\n" >> filelist.lst +fi +if [ -d usr/lib64 ]; then + find usr/lib64 -type f -printf "/%h/%f\n" >> filelist.lst +fi +if [ -d usr/bin ]; then + find usr/bin -type f -printf "/%h/%f\n" >> filelist.lst +fi +if [ -d usr/sbin ]; then + find usr/sbin -type f -printf "/%h/%f\n" >> filelist.lst +fi +touch doclist.lst +if [ -d usr/share/man ]; then + find usr/share/man -type f -printf "/%h/%f.gz\n" >> doclist.lst +fi +popd +mv %{buildroot}/filelist.lst . +mv %{buildroot}/doclist.lst . + +%files -n python3-csvs-to-sqlite -f filelist.lst +%dir %{python3_sitelib}/* + +%files help -f doclist.lst +%{_docdir}/* + +%changelog +* Wed May 10 2023 Python_Bot <Python_Bot@openeuler.org> - 1.3-1 +- Package Spec generated |