Mysql – Query to Access a specfic data in the row using python

MySQLpython

Am using wordpress and phpmyadmin for handling my database
Below is the code I use to view entries on my form using python. In the form I ask users to check/uncheck specific questions. It works as desired.

import MySQLdb as mdb
con = mdb.connect('localhost', 'root', 'password', 'WordpressDB', unix_socket="/opt/lampp/var/mysql/mysql.sock");
with con: 
    cur = con.cursor()
    cur.execute("SELECT data FROM `wp_form_builder_entries` WHERE entries_id = 5")
    rows = cur.fetchall()

Output generated by this code is as follows:

enter image description here

Works just as desired but I don't want all the data. I just need to know whether its checked or unchecked. If the user has checked value is "s:9:'checked'" else its "s:9:'unchecked'"

What query should I use to retrieve "s:9:'checked'"?

Best Answer

If I understand it correctly, you are storing all data in one field of the table? If so, MySQL will not be of much use as you need to unserialize the data in python. If so, then this question is not really for DBA, but more for StackOverflow.

If you however want to restructure the table and keep various data elements in different columns instead of one column called "data", then you could SELECT only that particular column(s) that are relevant instead of querying whole string.

Related Solutions

Using AWS DynamoDB Geospatial index with Python

The "geospatial feature" of dynamodb isn't really anything else then calculating a geohash from the coordinates, storing the hash in the same row, create an index on the hash, and when querying based on a location, calculate the hash of that location and compare to the indexed hashes in the table. The algorithm of geohash is really simple and with a reasonable knowledge it could be coded in a couple of hours in any language. You could even create a lambda function (since October 2015 even in Python!) that is attached to a dynamodb stream and stores the geohash automagically.

But probably it is a much better idea to check out dynamodb streams + lambda + cloudsearch integration that has native support for geospatial queries and boto also has a cloudsearch interface.

Troubleshooting Python Code for Inserting into MySQL Database

and this is the code to insert the data:

    import mysql.connector

    db = mysql.connector.connect(host="127.0.0.1", # your host, usually localhost
             user="root", # your username
              passwd="", # your password
              db="music")# name of the data base
    cur=db.cursor()

    q = "insert into name_test(fname,sname) values(%s, %s)"
    v = { "bob", "smith" }

    cur.execute(q,v) #runs the query
    db.commit() #ensures that its saved
    cur.close() #closes the cursor
    db.close() #closes the connection

source: https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html

hope this helps :)

Best Answer

Related Solutions

Using AWS DynamoDB Geospatial index with Python

Troubleshooting Python Code for Inserting into MySQL Database

Related Question