How would one connect an SQL db securely to an external client?

Question

I'm attempting to connect a database, located on a web server, to a robot but I do not know how to connect the database to the robot. I would like the robot to run SELECT and UPDATE queries from the robot. The other issue is that I do not intend on using C-languages or Java; I plan on using python in the main control system.

I do know: PHP VBScript Batch Python

If anyone knows how to connect the DB to a bot it would be a great help.

Answer 1

So basically how to connect to an SQL DB in python? I'm working on a virtual bot right now doing the same thing. Look into the module , SQL-connector!
http://www.mysqltutorial.org/python-connecting-mysql-databases/
You would start with creating a config.ini with your credentials

[mysql]
host = localhost
database = python_mysql
user = root
password =

Read Config.ini and return a dictionary

from configparser import ConfigParser 
def read_db_config(filename='config.ini', section='mysql'):
    """ Read database configuration file and return a dictionary object
    :param filename: name of the configuration file
    :param section: section of database configuration
    :return: a dictionary of database parameters
    """
    # create parser and read ini configuration file
    parser = ConfigParser()
    parser.read(filename)

    # get section, default to mysql
    db = {}
    if parser.has_section(section):
        items = parser.items(section)
        for item in items:
            db[item[0]] = item[1]
    else:
        raise Exception('{0} not found in the {1} file'.format(section, filename))

    return db

and connect to MYSQL database

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config


def connect():
    """ Connect to MySQL database """

    db_config = read_db_config()

    try:
        print('Connecting to MySQL database...')
        conn = MySQLConnection(**db_config)

        if conn.is_connected():
            print('connection established.')
        else:
            print('connection failed.')

    except Error as error:
        print(error)

    finally:
        conn.close()
        print('Connection closed.')


if __name__ == '__main__':
    connect()

and update statement would look like the following

def update_book(book_id, title):
    # read database configuration
    db_config = read_db_config()

    # prepare query and data
    query = """ UPDATE books
                SET title = %s
                WHERE id = %s """

    data = (title, book_id)

    try:
        conn = MySQLConnection(**db_config)

        # update book title
        cursor = conn.cursor()
        cursor.execute(query, data)

        # accept the changes
        conn.commit()

    except Error as error:
        print(error)

    finally:
        cursor.close()
        conn.close()


if __name__ == '__main__':
    update_book(37, 'The Giant on the Hill *** TEST ***')