current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have the header dict as this

header= {
'catA' : {
'name': 'user_name',
'age':'user_age'
},
'catB' : {
'name': 'person_name',
'age': 'person_age'
}
}

The postgres table is flat with all columns from header like this

user_name , user_age, person_name, person_age

The data which i want to insert looks like this, one row per row in DB

data = [('user_name', 'john'), ('user_age', '23'), ('person_name', 'rupan') . . . ]

what is best way to insert

I want something like this

query = "INSERT INTO mytable (header.cols) VALUES (data)"

Also i want to esacpe the data so that no malacious data goes in database

share|improve this question
    
It would be nice to add information about PostgreSQL driver you use. Is it ODBC, psycopg, pg8000? If you use Jython you can also use JDBC driver. –  Michał Niklas Dec 30 '13 at 9:10

2 Answers 2

up vote 1 down vote accepted

You can escape parameters but this not good idea. It is better to use prepared statement. Such statements are much easier do database to parse and use. See at pyodbc explanation: http://code.google.com/p/pyodbc/wiki/GettingStarted#Parameters

There is code that uses such prepared statement:

data = [
    ('user_name', "Adam 'Adi' Bobek"), ('user_age', 23), ('person_name', "Jurek 'Jerry' Jimowski") ,('person_age', 28),
    ]
data = dict(data)
cols = ",".join(data.keys())
qmarks = ','.join(['?' for s in data.keys()])
values = [v for v in data.values()]
insert_statement = "INSERT INTO users (%s) VALUES (%s);" % (cols, qmarks)

import pyodbc
connection = pyodbc.connect('DSN=pglocal')
cursor = connection.cursor()
cursor.execute(insert_statement, values)
connection.commit()

Other PostgreSQL/database drivers (psycopg, JDBC) can use similar techniques. This was tested with pyodbc and odbc (part of PyWin32 package that is also part of ActiveState Python distribution).

EDIT:

There is example of prepared with psycopg2, read: http://www.psycopg.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries to find more:

import psycopg2
....
connect_string = 'dbname=test host=localhost port=5493 user=postgres password=postgres'
connection = psycopg2.connect(connect_string)
cursor = connection.cursor()
psycopg_marks  = ','.join(['%s' for s in data.keys()])
insert_statement = "INSERT INTO users (%s) VALUES (%s)" % (cols, psycopg_marks)
cursor.execute(insert_statement, values)
connection.commit()
share|improve this answer
    
I tried your code and it gave me this error psycopg2.ProgrammingError: syntax error at or near "," LINE 1: ...ct_area) VALUES (nextval('my_id_seq'), ?,?,?,?,?,?... –  user3113427 Jan 2 at 3:40
    
pscopg2 is similar, but use %s instead of ? used by ODBC or JDBC. See my updated answer and read: psycopg.org/psycopg/docs/… , especially "The problem with the query parameters" –  Michał Niklas Jan 2 at 6:06
    
thanks michal , its working now –  user3113427 Jan 2 at 6:29
up vote 1 down vote

What about this?

>>> data = [('user_name', 'john'), ('user_age', '23'), ('person_name', 'rupan') ,('person_age',28)]
>>> data = dict(data)
>>> cols = ",".join(data.keys())
>>> values = ",".join("'"+ v + "'" if type(v) is str else str(v) for v in data.values())
>>> query = "INSERT INTO mytable (%s) VALUES (%s)" % (cols,values)
>>> query
"INSERT INTO mytable (person_name,user_name,user_age,person_age) VALUES ('rupan','john','23',28)"
share|improve this answer
    
that looks correct but my values conatin all sort of characters like `'";{}<>` that is causing error. how can i escape thise before inserting –  user3113427 Dec 30 '13 at 4:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.