Project Description
About
dbautomate is a Python package that provides flexible, interactive and expressive data manipulation with databases. It designed to make working with "relational" and "non-relational" databases seamless, interactive and efficient, provides a flexible and scalable solution for diverse data storage and retrievel needs.
Table of Contents
- Main Features
- Where to get it
- Installation
- Dependencies
-
How to use it
- Import the library for MySQL
- Create an instance of MySQL class
- Connect to MySQL Server
- Check the MySQL handler object
- Execute query with MySQL
- To insert the data in table
- To save the data of the table
- To close the MySQL connection
- Import the library for MongoDB
- Create an instance of MongoDB class
- Get the client of MongoDB
- Create database of MongoDB
- Create collection of MongoDB
- To insert the data in collection
- To find the data of the collection
- To save the data of the collection
- To delete the data of the collection
- To update the data of the collection
- To close the mongo client
- Functionality
- Development
Main Features
Here are some of the key features of dbautomate as follows:
- This dbautomate works with MongoDB as well as MySQL databases.
- When working with MySQL, it can executes any query, it can insert multiple type of data, it can save the data as well.
- When working with MongoDB, it creates database and collection, data can be insert, find, delete, and update as well, the data can be saved locally.
Where to get it
The source code is currently hosted on GitHub at: https://github.com/ravi46931/dbautomatepkg
Installation
Install via pip:
# PyPI
pip install dbautomate
Dependencies
dbautomate supports Python3.8, Python3.9, python3.11.
Installation requires:
- pymongo
- pymongo[srv]
- dnspython
- pandas
- numpy
- termcolor
- mysql-connector-python
- prettytable
How to use it
Let suppose you want to use MySQL database.
Import the library for MySQL
from dbautomate import mysqloperator
Create an instance of MySQL class
mysql_handler=mysqloperator.MySQL_connector()
Connect to MySQL Server
config = {
'host': 'your_mysql_host',
'user': 'your_mysql_user',
'password': 'your_mysql_password',
'database': 'your_database_name'
}
conn=mysql_handler.connect_to_mysql(config, attempts=3, delay=2)
# attempts: Number of times it tries to connect the server in case of failure.
# delay: time after which next attempt will happen
# These two parameters are optional
For successful connection it gives following message:
Connected successfully....
Check the MySQL handler object
print(mysql_handler)
Output:
MySQLHandler Object -
Config:
{'host': 'your_mysql_host', 'user': 'your_mysql_user', 'password': 'your_mysql_password', 'database': 'your_database_name'}
Connected: True
Execute query with MySQL
query="select * from table_name"
mysql_handler.execute_query(query)
It prints table along with successful execution message.
Query executed successfully....
+----------------+------------+
| purchase_price | sale_price |
+----------------+------------+
| 8000 | 9505 |
+----------------+------------+
| 8500 | 10105 |
+----------------+------------+
| 7000 | 8505 |
+----------------+------------+
| 10500 | 11505 |
+----------------+------------+
To insert the data in table
Insert single entry or multiple entries.
table_name='cats'
values=[
('Mena',5),
('Kena',11)
]
mysql_handler.insert_data(table_name, values)
If you want to use different database to insert the data, you can achieve this by mentioning the database.
db_name='book'
table_name='english_books'
values=[......]
mysql_handler.insert_data(table_name, values, db_name)
You can insert CSV file into the table as well.
filepath='path/to/your/data.csv'
mysql_handler.bulk_insert(table_name, filepath)
# Change the database as well
filepath='path/to/your/data.csv'
db_name='books'
mysql_handler.bulk_insert(table_name, filepath, db_name)
- If the first entry is autoincrement id, and you have not provided that in your input data (aka 'values' in above code) then enter 'y', but if you have provided in your input data then enter something else.
- If you are inserting the multiple entries then enter y.
- If you are inserting the single entry then enter n.
A success message shows after successful insertion of the data.
Inserted successfully....
To save the data of the table
To save the table locally from the current active database.
table_name='cats'
mysql_handler.save_data(table_name)
To save the data from the different database.
db_name='animals'
table_name='cats'
mysql_handler.save_data(table_name, db_name)
Prints the success message after the saving data.
Enter the filename: data.csv
File: 'data.csv' saved successfully....
To close the MySQL connection
mysql_handler.close_connection()
It prints successful connection close.
MySQL connection closed.
Let suppose you want to use MongoDB database.
Import the library for MongoDB
from dbautomate import mongodboperator
Create an instance of MongoDB class
mongo=mongodboperator.MongoDB_connector()
Get the client of MongoDB
uri="localhost:27017"
client=mongo.get_mongo_client(uri)
A success message prints.
Connected to MongoDB Successfully....
Create database of MongoDB
Ensure you have already created with client.
database_name='firstdb'
mongo.create_database(database_name)
After creating database it prints success message.
Database created successfully....
Create collection of mongoDB
Ensure you have already created with client and database.
collection_name='first'
mongo.create_collection(collection_name)
After creating collection it prints success message.
Collection created successfully....
To insert the data in collection
You can insert single or multiple entries.
# Single Entry
single_entry={'name':'abc', 'age':24}
mongo.insert_data(single_entry)
A success message prints.
Inserted successfully(Single entry)....
# Multiple Entries
multiple_entries=[{'name':'abc', 'age':24},{'name':'def', 'age':22}]
mongo.insert_data(multiple_entries)
A success message prints.
Data inserted successfully....
If you want to insert data from the CSV, EXCEL or JSON file, you can achieve this by following way.
# To insert the data in the current collection
mongo.bulk_insert(filepath)
# To insert the data in a new or different collection
collection_name='employee'
mongo.bulk_insert(filepath, collection_name)
A success message prints.
Data inserted successfully....
To find the data of the collection
You can find the data.
mongo.find_data()
- If you want to see the data in the form of DataFrame enter y.
- If you want to see the data in the form of List enter n.
To save the data of the collection
You can save the data locally.
mongo.save_data()
Enter the type of the file and name of the file (that you want to save).
Do you want to save the data as json file or csv file?(json/csv)json
Enter the filename: q.json
File: 'q.json' saved successfully....
To delete the data of the collection
For deleting the entire data from the collection.
mongo.delete_data()
- Enter y if you want to delete the entire data.
- It will delete entire data, Do you want to delete the data?(y/n): y
All entry deleted
For delete the data based on the key value.
key_value={'age':25}
mongo.delete_data(key_value)
Enter one if you want to delete the one entry else many it delete all the entry based on the key_value Do you want delete the one entry or mutiple entries?(one/many) one
Entry deleted successfully
If you choose many option then the following message will show
Multiple entries deleted successfully
To update the data of the collection
filter_criteria={'age': 25}
update_data=[{'$set': {'age':24}}]
mongo.update_data_entry(filter_criteria, update_data)
Enter one if you want to update the single entry.
Do you want single entry update on multiple?(one/many) one
Updated Successfully (one entry)...
Enter many if you want to update the multiple entries.
Do you want single entry update on multiple?(one/many) many
Updated Successfully (multiple entries)....
To close the mongo client
mongo.close_mongo_client()
After successful closing the client.
MongoDB client closed successfully..
Functionality
For more detail of each of the functions can be reed the docstrings
print(mysql_handler.insert_data.__doc__)
Development
Important links
- Source code repo: GitHub Link
- Download Release: Pypi
- Bugs/ Feature requests: GitHub issue Tracker
Source code
You can check the latest sources with the command:
https://github.com/ravi46931/dbautomatepkg.git