1

I am trying to come up with a python script to retrieve data from MySQL and post the data in json format to a web server. I have two separate python codes, one for retrieving the data in MySQL and one for posting the data in json format. The main issue that I am facing is that I do not know how to integrate them together.

Code for retrieving data from MySQL:

   import MySQLdb 
   db = MySQLdb.connect("locahost", "root", "12345", "testdatabase") 
   curs=db.cursor() 
   curs.execute("SELECT * from mydata")
   reading = curs.fetchall() 
   print "Data Info: %s" % reading

Code for posting to web server:

import json
import urllib2
import requests

data = {
       'ID' :1
       'Name' :Bryan
       'Class' :3A
}
req = urllib2.Request('http://abcd.com') //not the actual url
req.add_header('Content type', 'application/json')
response=urllib.urlopen(req, json.dumps(data))

I have referenced the codes from the following links:

Retrieve data from MySQL

Retrieve data from MySQL 2nd link

Post to web server

Post to web server 2nd link

Would appreciate any form of assistance.

gram95
  • 103
  • 2
  • 15

2 Answers2

1

You can use the connection as a library file,

File connection.py :

def db_connect(query):
    import MySQLdb db = MySQLdb.connect("locahost", "root", "12345", "testdatabase") 
    curs=db.cursor() 
    curs.execute(query)
    reading = curs.fetchall() 
    return reading

Main file: webserver.py

import json
import urllib2
import requests
import connection   

mysql_data = connection.db_connect("SELECT * from mydata")
#data = <Your logic to convert string to json>
req = urllib2.Request('http://abcd.com') //not the actual url
req.add_header('Content type', 'application/json')
response=urllib.urlopen(req, json.dumps(data))

Method 2 you can also try sql alachemy which gives directly dict data out of sql query. You can use filters instead of direct sql query.

I recomend this way is better and you can go through the link "https://pythonspot.com/en/orm-with-sqlalchemy/"

Naggappan Ramukannan
  • 2,564
  • 9
  • 36
  • 59
  • From your code, you are using webserver.py to run connection.py to do the sql database connection right? – gram95 Aug 07 '17 at 06:52
  • Also, for the db_connect(query) in connection.py, what does the query refer to? – gram95 Aug 07 '17 at 07:05
  • @gram95 query refers to the string which you pass it from webserver.py (see line mysql_data = ) where you pass the query string. – Naggappan Ramukannan Aug 07 '17 at 08:43
  • So, I replace it with mysql_data? Sorry, I'm a bit confused. – gram95 Aug 07 '17 at 08:59
  • It is very basic thing how do you call a function with arguments? in (mysql_data = connection.db_connect("SELECT * from mydata") you are passing the query to "connection.db_connect" and there you use this string in a variable called query. – Naggappan Ramukannan Aug 07 '17 at 09:08
  • @gram95 yes thanks, and as I mentioned the better way to go is use something like sqlalchemy to connect with DB , so that you can write query as filter and it will be returning in data type dictionary. Please accept this answer if you feel as correct – Naggappan Ramukannan Aug 07 '17 at 09:48
-1
def db_connect(query):
    import MySQLdb db = MySQLdb.connect("locahost", "root", "12345", "testdatabase") 
    curs=db.cursor() 
    curs.execute(query)
    reading = curs.fetchall() 
    return reading
Regolith
  • 2,944
  • 9
  • 33
  • 50
Ramesh
  • 1
  • 1
  • Can you please add something more than just a code segment. How should they use this code to achieve what they are trying to do? – Nigel Ren Aug 07 '17 at 12:35