CODEWITHSUNDEEP
pythondatabasepostgresqlpsycopg2
user1771791
user1771791

Reputation: 5

python 3.6 sql database connection

I am trying to insert a row into my postgresql database with a table created from 

    CREATE TABLE public.coinbase_btc_usd
(
  id bigserial primary key,
  price integer NOT NULL,
  buy integer NOT NULL,
  sell integer NOT NULL,
  "timestamp" timestamp with time zone
)

However when my python 3.6 script runs and tries to add a row using psycopg2 like this it returns an error saying "no results to fetch" and nothing is added to my db.

        sql_query = "INSERT INTO coinbase_btc_usd(price, buy, sell, timestamp)" \
                " VALUES (" + exchange_rate + ', ' + buy_rate + ', ' + sell_rate + ", \'2015-10-10 06:44:33.8672177\')"

    print(sql_query)

    cur.execute(sql_query)

I also printed the sql_query variable to see exactly what was getting attempted to execute and this was printed to the output

INSERT INTO coinbase_btc_usd(price, buy, sell, timestamp) VALUES (16392.10, 16563.40, 16235.42, '2015-10-10 06:44:33.8672177')

Upvotes: 0

Views: 407

Answers (2)

mhawke
mhawke

Reputation: 87134

Make sure that you are committing the transaction:

cur.execute(sql_query)
conn.commit()

Or you can enable auto commit to commit each query immediately after execution:

conn.autocommit = True

Furthermore, it costs nothing to prevent SQL injection attack - just use parametersied queries. In fact your code will actually be cleaner as well as safer:

sql_query = "INSERT INTO coinbase_btc_usd(price, buy, sell, timestamp) VALUES (%s, %s, %s, %s)"
cur.execute(sql_query, (exchange_rate, buy_rate, sell_rate, timestamp))
conn.commit()

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51649

change the 

sql_query = "INSERT INTO coinbase_btc_usd(price, buy, sell, timestamp)" \
            " VALUES (" + exchange_rate + ', ' + buy_rate + ', ' + sell_rate + ", \'2015-10-10 06:44:33.8672177\')"

to:

sql_query = "INSERT INTO coinbase_btc_usd(price, buy, sell, timestamp)" \
            " VALUES (" + exchange_rate + ', ' + buy_rate + ', ' + sell_rate + ", \'2015-10-10 06:44:33.8672177\') returning *"

this should fix no results to fetch in my assumption.

If you see no row added, you most probably begin transaction and never commit it.

Upvotes: 0

Related Questions