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

Solution 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.

Solution 2

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())

Solution 3

pip install mysql failed for me on MacOS Sierra with EnvironmentError: mysql_config not found.

pip install pymysql works. PyMySQL is written in pure Python, so should be more cross-platform than statically compiled modules.

Example code:

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('[email protected]', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('[email protected]',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

Solution 4

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/

View more solutions

Comments

[Cees Timmerman]

sudo pip install mysql fails on MacOS Sierra with EnvironmentError: mysql_config not found.

[Phillip]

I'm also on Sierra but can't presently use either: If anyone can help, this is my question: stackoverflow.com/questions/46145845/…

[larsbutler]

@EricFossum What's the issue?

[Eric Fossum]

``` ➜ ~ python3 -m pip install mysql Collecting mysql ... Collecting MySQL-python (from mysql) ... Complete output from command python setup.py egg_info: Traceback (most recent call last): File "<string>", line 1, in <module> File "/tmp/pip-build-r33d2gw7/MySQL-python/setup.py", line 13, in <module> from setup_posix import get_config File "/tmp/pip-build-r33d2gw7/MySQL-python/setup_posix.py", line 2, in <module> from ConfigParser import SafeConfigParser ModuleNotFoundError: No module named 'ConfigParser' ``` I removed some fluff

[larsbutler]

@EricFossum It looks like a bug in the library. I just submitted a patch: github.com/farcepest/MySQLdb1/pull/136.

[Mohamed Benkedadra]

wouldn't go with pymysql for serious projects ... the maintainer doesn't solve any bugs, he just asks you to go to stack overflow, for bugs that are obviously in the library. i mean i understand that people are busy, taking a long time to solve bugs is ok. but saying that bugs are "RARE" is not.

[Oly Dungey]

Doesn't work - sh: mysql_config: command not found when running pip install mysql

[rikyeah]

Could you take the time to annotate this answer as python language? insert code in backtick fences