Persist Excel/CSV sheets to MySQL (and vice versa)
Project description
exql
Persist Excel/CSV sheets to MySQL (and vice versa)
Installation
pip install exql
How it works
Convert a directory into a database and the .csv and .xls files in that directory to tables (or vice versa i.e. database/table to director/.csv). Currently, only .csv/.xsl to MySQL conversion is supported.
directory <==> database
CSV/Excel sheets in directory <==> tables in database
For example,
MyDir <-> CREATE SCHEMA MyDir;
|-file1.csv <-> CREATE TABLE file1 (...);
|-file2.xlsx <-> CREATE TABLE file2 (...);
Usage
Import exql and create an Exql object passing your MySQL username, password, etc.
from exql import exql
exql_obj = exql.Exql("root", "mysql@123", "localhost", 3306, False)
Create a database by passing path of directory to be converted to a DB. A sample directory is present in the resources
directory
exql_obj.create_db_from_directory("/path/demo_university_db")
Similarly, other methds can be used to create individual tables, delete rows, etc.
Method | Description |
---|---|
create_db_from_directory | Create MySQL DB with name same as the directory name and tables built using .csv/.xlsx files present within the directory |
create_table_from_csv | Persist data from a .csv/.xslx file to a single MySQL table |
insert_in_table | Insert data from .csv into existing table |
select_into_csv | Select rows read from a DB using the provided query into a .csv |
delete_from_db | Delete rows matching column-value pairs provided in .csv from MySQL table |
write_db_to_dir | Create a directory containing all tables of a DB saved as .csv files |
Note
- In the .xsl or .csc files, SQL commands can be provided directly. Thus, the .csv or .xsl can have SQL queries as follows
student_id | name | age | added_on |
---|---|---|---|
INT | VARCHAR(100) | INT | DATETIME |
PRIMARY KEY | NOT NULL | NOT NULL | |
100001 | (SELECT UPPER('John Keats')) | 45 | now() |
100002 | 'William Shakespeare' | 23 | now() |
(SELECT COUNT(*) FROM table2) | 'Robert Frost' | 54 | now() |
- Cells containing text must be enclosed in inverted commas or else they would be interpreted as SQL keywords/identifiers
- Refer to the
resources
folder for .csv/.xsl file templates needed for table creation/insertion/deletion
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distributions
No source distribution files available for this release.See tutorial on generating distribution archives.
Built Distribution
exql-1.0-py3-none-any.whl
(13.3 kB
view hashes)