best way for python to interface with MySQL

Question

I'm still very new to programming and trying to teach myself the correct ways of doing things.

I'm currently writing a script that will take 1-3 values, generated about every second, within python and then save it to a MySQL database. Down the road will build a web interface that will take those values and nicely display them, but for now I just want to save the data.

I will be running the Python code on a Raspberry Pi, and hopefully the web server with the MySQL server. If not on that device on another Raspberry Pi.

What I'm here asking is, what is the best way for Python to work with MySQL? Library or HTTP GET, or another way I don't know about... Since, I was going to build a web interface, I was thinking about having an API.php page that will take GET request and then the PHP code will write the data to the MySQL database.

What I had in mind was to have Python generate a link then use the request library to request the site e.g http://127.0.0.1/API.php?value1=XXX&Value2=YYY&value3=ZZZ then wait for a JSON return of something like "Data Saved" if the data was saved correctly. Then continue the loop of generating the next value.

What that be the best way of going about this or is there a better way? I know that I will have some security issues, and I hope to resolve them once I learn more about coding. And remember that I want the data written every 1 or 5 seconds. Hopefully, every 1 second.

Thank you guys for your responses, Throdne

Answer 1

There are a lot of ways you could do this, but since you stated that the MySQL server could be on the same Raspberry Pi or a different one, the following is the simplest way to get what you need set up.

First, you need to install a Python package which contains MySQL bindings. There are ton out there, but MySQL-python seems to be mature and good enough.

To install it:

$ pip install mysql

(If you're not already familiar with pip, check out the pip documentation to get started.)

Once you've got a MySQL server running on one of your Raspberry Pis, here's an example what you'll need to do in the code to connect to the server and run various types of queries:

import MySQLdb

host = '192.168.99.100'
user = 'myuser'
password = 'secret'
port = 3306
db = 'test'

conn = MySQLdb.Connection(
    host=host,
    user=user,
    passwd=password,
    port=port,
    db=db
)

# Example of how to insert new values:
conn.query("""INSERT INTO mytable VALUES ('foo3', 'bar2')""")
conn.commit()

# Example of how to fetch table data:
conn.query("""SELECT * FROM mytable""")
result = conn.store_result()
for i in range(result.num_rows()):
    print(result.fetch_row())

Check the MySQL-python user docs for more details on how to use the library.

The above code assumes a couple of things about your MySQL server:

• Your server is running at 192.168.99.100. It could be 127.0.0.1 (localhost) or another address.
• Your server already has the user myuser defined, with a password and appropriate permissions.
• You have already created a database called test.
• You have already created a table called mytable on database test which contains the VARCHAR fields of foo and bar.

I won't go into those details, since that's slightly out of scope for this thread, but if you need help have a look at the MySQL documentation for creating users and creating tables.

Answer 2

Python connect xampp mysql windows with pymysql:

Step-1:

Run Command Prompt D:\python-3.6.2\Scripts> pip install pymysql

Step-2:

#!D:/python-3.6.2/python.exe 
print ("Content-Type: text/html\n")
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='', db='python_mysql')
cur = conn.cursor()
cur.execute("SELECT * FROM authors")
print(cur.description)
print(" Test")
for row in cur:
    print(row)
cur.close()
conn.close()

Save the file as testconn.py in htdocs/PythonProject & open http://localhost/PythonProject\testconn.py

Crud Python & MySQL:

https://github.com/matinict/python-in-xampp-for-windows/

Answer 3

First install mysqlclient as this will also provide you support to work with Python 3.6

pip install mysqlclient

Example code

    import mysql.connector
    import _mysql
    db=_mysql.connect("127.0.0.1","root","umer","sys")
    #db=_mysql.connect(host,user,password,db)
    # Example of how to insert new values:
    db.query("""INSERT INTO table1 VALUES ('01', 'myname')""")
    db.store_result()
    db.query("SELECT * FROM new1.table1 ;") 
    #new1 is scheme table1 is table mysql 
    res= db.store_result()
    for i in range(res.num_rows()):
        print(result.fetch_row())