How do I insert data into table?

Question

I have created table using this create command as:

CREATE TABLE test_table(id INT PRIMARY KEY,name VARCHAR(50),price INT)

i want to insert into this table wherein values are stored already in variable

bookdb=# name = 'algorithms'
bookdb-# price = 500
bookdb-# INSERT INTO test_table VALUES(1,'name',price);

I get the following error:

ERROR: syntax error at or near "name" LINE 1: name = 'algorithms'

Can anyone point out the mistake and propose solution for the above?

Thanks in advance

Edit:

import psycopg2
import file_content
try:
    conn = psycopg2.connect(database='bookdb',user='v22')
    cur = conn.cursor()
    cur.execute("DROP TABLE IF EXISTS book_details")
    cur.execute("CREATE TABLE book_details(id INT PRIMARY KEY,name VARCHAR(50),price INT)")
    cur.execute("INSERT INTO book_details VALUES(1,'name',price)")  
    conn.commit()
except:
    print "unable to connect to db"

I have used the above code to insert values into table,variables name and price containing the values to be inserted into table are available in file_content python file and i have imported that file.The normal INSERT statement takes values manually but i want my code to take values which are stored in variables.

Like the error says: your first line isn't a valid SQL statement. You want to replace the variable content in your python code. — mabi, Aug 05 '13 at 07:15
edited the above code.how to supply values to INSERT statement which stored in variables? — user2635299, Aug 05 '13 at 07:29
Hmmm… I thought substantially rewriting a question was frowned upon on StackExchange. This is no longer a Postgres question. This newly rewritten version is a Python programming question. — Basil Bourque, Aug 05 '13 at 07:34

score 1 · Answer 1 · edited May 23 '17 at 12:20

SQL does not support the concept of variables.

To use variables, you must use a programming language, such as Java, C, Xojo. One such language is PL/pgSQL, which you can think of as a superset of SQL. PL/PgSQL is often bundled as a part of Postgres installers, but not always.

I suggest you read some basic tutorials on SQL.

See this similar question: How do you use script variables in PostgreSQL?

score 0 · Answer 2 · answered Aug 05 '13 at 07:36

0

If you are using PSQL console:

\set name 'algo'
\set price 10
insert into test_table values (1,':name',:price)
\g

answered Aug 05 '13 at 07:36

Luca Basso Ricci

17,829
2
47
69

thanks it works.But i want it to done in my python code.see my edit – user2635299 Aug 05 '13 at 07:44

score 0 · Accepted Answer · answered Aug 05 '13 at 08:43

don't have postgres installed here, but you can try this

import psycopg2
import file_content
try:
    conn = psycopg2.connect(database='bookdb',user='v22')
    cur = conn.cursor()
    cur.execute("DROP TABLE IF EXISTS book_details")
    cur.execute("CREATE TABLE book_details(id INT PRIMARY KEY,name VARCHAR(50),price INT)")
    cur.execute("INSERT INTO book_details VALUES(1, '%s', %s)" % (name, price))
    conn.commit()
except:
    print "unable to connect to db"

How do I insert data into table?

3 Answers3