exql 1.0

Persist Excel/CSV sheets to MySQL (and vice versa)

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