dbautomate 0.5.6

A Python package for connecting with database.

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
  • Important links
  • Source code

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)

1. 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.
   
2. If you are inserting the multiple entries then enter y.
   
3. 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()

1. If you want to see the data in the form of DataFrame enter y.
2. 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

Go to top