dataspoon

easy to use tools for working with data
Explore the Wiki»
View Demo | Report Bug | Request Feature

(back to top)

(back to top)

DBTool provides a simple way to connect to a MySQL database

1. create a database
2. create a table
3. create a row
4. create a column
5. create a value
6. create a key
7. create a link_key

DBTool() allows you to create and modify MySQL databases using Python with simple get and put commands.

For example, you can set and get a key/value pair to ('ala'/'kazam') in a row that is referenced by the link_key ('xyzzy'):

DBTool().put('xyzzy', 'ala', 'kazam')
value = DBTool().get('xyzzy', 'ala')

To perform the same operation in a different database ('magicdb'):

DBTool('magicdb').put('xyzzy', 'ala', 'kazam')
value = DBTool('magicdb').get('xyzzy', 'ala')

And, to perform the same operation in a different database ('magic_carpet') and a different table ('magic_table')

DBTool('magic_carpet', 'magic_table').put('xyzzy', 'ala', 'kazam')
value = DBTool('magic_carpet', 'magic_table').get('xyzzy', 'ala')

The behavior of the put method differs depending on the number of parameters passed.

    def put(self, primary_key, element_key=None, value=None):
        # 1 parameter: add _link_key (returns row_number of _link_key)
        # 3 parameters: for primary_key set key/value (returns row_number of _link_key)

Examples of using DBTool

    xyzzydb = DBTool() creates a database named dBTool with table named default_table.
    xyzzydb = DBTool('xyzzydb') creates a database named xyzzydb with a table named default_table.
    xyzzydb = DBTool('xyzzydb', 'best_magic_table') creates a database named xyzzydb with a table named best_magic_table.
    xyzzydb.put('link_key_xyzzy') creates a row with link_key ('link_key_xyzzy')
    xyzzydb.put('other_link_key', 'ala', 'kazam') sets key ('ala') to value ('kazam') in row with link_key ('other_link_key')
    
    1. Create a database ('xyzzydb', 'magic_table'): xyzzydb = DBTool('xyzzydb', 'magic_table').  The MySQL.table_name is a default name, unless you specify a table_name during creation (ex. xyzzydb = DBTool('xyzzydb', 'magic_table')) or after creation (ex. xyzzydb.open_table('new_magic_table'))
    2. Create a new row with a link_key ('link_key_xyzzy'): xyzzydb.put('link_key_xyzzy')
    3. Create or insert into a row with link_key_destination from link_key_source: xyzzydb.put('link_key_source', 'link_key_destination')
    4. Put key/value ('ala'/'kazam') into row with link_key ('link_key_destination'): xyzzydb.put('link_key_destination', 'ala', 'kazam')
    5. Get this_value ('kazam') using key ('ala') from row with link_key_destination : this_value = xyzzydb.get('link_key_destination', 'ala')
    6. Save xyzzydb.magic_table as a pandas.DataFrame .pkl file to location ('data/mysql.pkl'): xyzzydb.pickle_words('data/mysql.pkl')

1. Create a new instance of the OneHotWords class
2. Get the index of the word 'hello'
3. Get the word at index 1
4. Get all the words in the onehot index

1. create a new database
2. create a new table
3. add a column to the table
4. add a row to the table
5. get the row number for a link_key
6. get the value for a key in a row
7. get the entire row for a link_key
8. update a value in a row
9. delete a table
10. delete a row
11. delete a column
12. delete a database
13. get the number of rows in a table
14. get the column names for a table
15. get a dataframe for a table
16. get a dataframe for a link_key

1. Install MySQL:

   sudo rm -rf /var/lib/mysql/mysql
   sudo apt-get remove --purge mysql-server mysql-client mysql-common
   sudo apt-get autoremove
   sudo apt-get autoclean
   sudo apt-get install mysql-server
   

2. Install mysql-connector-python:

   pip install mysql-connector-python
   

3. Optionally, install mysql-workbench:

   sudo apt install mysql-workbench-community
   

4. You need to know the location (ex. 127.0.0.1 or 192.168.1.69) and port number (ex. 3306 or 50011)

5. You must know the username (ex. 'bilbo') and password (ex. 'baggins') of an authorized user in mysql

1. If you need help using dataspoon then Talk to John

2. Clone the repo

   git clone https://github.com/CentralFloridaAttorney/dataspoon.git
   

3. No NPM packages to install

   NO npm install
   

4. Install mysql packages

   pip install mysql-connector-python
   

5. Create a file named .env in the root directory of your project and populate it with the following values:

   # Used by DBTool
   DBTOOL_USER=bilbo
   DBTOOL_PASSWD=baggins
   DBTOOL_HOST=127.0.0.1
   DBTOOL_PORT=3306
   DBTOOL_DATABASE_NAME=default_database
   DBTOOL_TABLE_NAME=default_table
   DBTOOL_ONEHOTDB_NAME=default_onehotdb
   DBTOOL_ONEHOTDB_TABLE=default_onehotdb_table
   

6. Install dotenv:

   pip install dotenv
   

(back to top)

For more examples, please refer to the Documentation

(back to top)

• Feature 1
• Feature 2
• Feature 3
  • Nested Feature

See the open issues for a full list of proposed features (and known issues).

(back to top)

If you get a root password error when starting MySQL consider: https://askubuntu.com/questions/1406395/mysql-root-password-setup-error

If bilbo is not present then use mysql:

create user 'bilbo'@'127.0.0.1' identified by 'baggins';
GRANT ALL PRIVILEGES ON *.* TO 'bilbo'@'127.0.0.1' WITH GRANT OPTION;
show grants for 'bilbo'@'127.0.0.1';

If you update Ubuntu and cannot reboot then fix your grub: https://phoenixnap.com/kb/grub-rescue

(back to top)

Project Link: https://github.com/CentralFloridaAttorney/dbtool

(back to top)

(back to top)