JSON translated into SQL by python

[Aaron G.]

I am new to programming python for JSON to SQL and I was wondering why this does not work. All the values for entering the DB are correct. The EnterpriseValue data is not entering the database.


#collect data from JSON source at Yahoo
url = ["db", "http://y.ahoo.it/wlB89"]
#check all sites
checkstatus(url[]);
#retrieve EnterpriseValue data from yahoo to DB
url = "http://y.ahoo.it/wlB89"
data = helper.openJSON_URL(url)
#store data
curObservation = data["EnterpriseValue"]

#connect to amazon and post data from Yahoo
conn = inti_psql_amazon("db name", "db user", "password", "db source")
query = "CREATE TABLE temp2 (ID int NOT NULL AUTO_INCREMENT, Enterprise_Value float, PRIMARY KEY(ID));"
query = "INSERT INTO TABLE temp2 (enterprise) VALUES("+ str(curObservation) +");"
do_query_amazon(conn, query)
close_psql_amazon(conn)

 

[Chris Angelico]

query = "INSERT INTO TABLE temp2 (enterprise) VALUES("+ str(curObservation) +");"

You just put the contents of curObservation into the query, as SQL
code. Is that really what you wanted to do? Most likely, you should be
using a parameterized query here; are you familiar with that concept?

You seem to have custom functions to do your database work here.
Without knowing what those functions do, it's hard for us to advise
further.

ChrisA

 

[Jussi Piitulainen]

I am new to programming python for JSON to SQL and I was wondering
why this does not work. All the values for entering the DB are
correct. The EnterpriseValue data is not entering the database.

#collect data from JSON source at Yahoo
url = ["db", "http://y.ahoo.it/wlB89"]
#check all sites
checkstatus(url[]);
#retrieve EnterpriseValue data from yahoo to DB
url = "http://y.ahoo.it/wlB89"
data = helper.openJSON_URL(url)
#store data
curObservation = data["EnterpriseValue"]

#connect to amazon and post data from Yahoo
conn = inti_psql_amazon("db name", "db user", "password", "db source")
query = "CREATE TABLE temp2 (ID int NOT NULL AUTO_INCREMENT, Enterprise_Value float, PRIMARY KEY(ID));"
query = "INSERT INTO TABLE temp2 (enterprise) VALUES("+ str(curObservation) +");"
do_query_amazon(conn, query)
close_psql_amazon(conn)

Possibly you should let the database know about the first query. Now
it's just a very short-lived string in the Python program.

Is there no exception?

My inclination would be to go to the Python interpreter and try very
simple interactions with the database. Create a table with only one
column. Insert one value. See if the value is there. Hello, world.

 

[Peter Otten]

I am new to programming python for JSON to SQL and I was wondering why
this does not work. All the values for entering the DB are correct. The
EnterpriseValue data is not entering the database.

#collect data from JSON source at Yahoo
url = ["db", "http://y.ahoo.it/wlB89"]
#check all sites
checkstatus(url[]);
#retrieve EnterpriseValue data from yahoo to DB
url = "http://y.ahoo.it/wlB89"
data = helper.openJSON_URL(url)
#store data
curObservation = data["EnterpriseValue"]

#connect to amazon and post data from Yahoo
conn = inti_psql_amazon("db name", "db user", "password", "db source")

inti_psql_amazon? Please post your actual code. Cut and paste, don't retype.
If there's an exception, post that, too. The source code of

do_query_amazon()

might be interesting, too. A commit() on the db cursor might do wonders.

query = "CREATE TABLE temp2 (ID int NOT NULL AUTO_INCREMENT,

Enterprise_Value float, PRIMARY KEY(ID));"

query = "INSERT INTO TABLE temp2 (enterprise) VALUES("+

str(curObservation) +");"

As a general remark, don't build your queries like that, rely on the API to
insert the values. E. g.:

cursor = conn.cursor()
cursor.execute("insert into table temp2 (enterprise) values (?);",
(curObservation,))

(Some databases use '%s' instead of '?')