Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #60274 > unrolled thread

JSON translated into SQL by python

Back to article view | Back to comp.lang.python

Contents

  JSON translated into SQL by python "Aaron G." <another.human.factor@gmail.com> - 2013-11-22 21:54 -0800
    Re: JSON translated into SQL by python Chris Angelico <rosuav@gmail.com> - 2013-11-23 18:35 +1100
    Re: JSON translated into SQL by python Jussi Piitulainen <jpiitula@ling.helsinki.fi> - 2013-11-23 10:06 +0200
    Re: JSON translated into SQL by python Peter Otten <__peter__@web.de> - 2013-11-23 09:52 +0100

#60274 — JSON translated into SQL by python

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)

[toc] | [next] | [standalone]

#60281

On Sat, Nov 23, 2013 at 4:54 PM, Aaron G.
<another.human.factor@gmail.com> wrote:
> 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

[toc] | [prev] | [next] | [standalone]

#60282

Aaron G. writes:

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

[toc] | [prev] | [next] | [standalone]

#60284

Aaron G. wrote:

> 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 '?')

> do_query_amazon(conn, query)
> close_psql_amazon(conn)

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web