473,542 Members | 6,044 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

insert a dictionary into sql data base

I have a dictionary that contains a row of data intended for a data base.

The dictionary keys are the field names. The values are the values to be
inserted.

I am looking for a good pythonic way of expressing this, but I have a
problem with the way lists are represented when converted to strings.

Lets say my dictionary is

data = {"fname": "todd", "lname": "Bush"}
fields = data.keys()
vals = []
for v in fields:
vals.append(data[v])

sql = """INSERT INTO table (%s) VALUES (%s);""" % (fields, vals)

but fields and vals are represented as lists. So, then I need to strip the
[] from them, but then ... there must be an easier way.

Any advise?

--
David Bear
-- let me buy your intellectual property, I want to own your thoughts --
Dec 5 '05 #1
7 20948
David Bear wrote:
The dictionary keys are the field names. The values are the values to be
inserted.

I am looking for a good pythonic way of expressing this, but I have a
problem with the way lists are represented when converted to strings.

Lets say my dictionary is

data = {"fname": "todd", "lname": "Bush"}
fields = data.keys()
vals = []
for v in fields:
vals.append(data[v])

sql = """INSERT INTO table (%s) VALUES (%s);""" % (fields, vals)

but fields and vals are represented as lists. So, then I need to strip the
[] from them, but then ... there must be an easier way.

Any advise?


1) use parameters to pass in the values (see
http://www.python.org/peps/pep-0249.html )

2) use parameters to pass in values

3) use parameters to pass in values

4) here's a simplified version of your code:

data = {"fname": "todd", "lname": "Bush"}

fields = data.keys()
values = data.values()

cursor.execute(
"INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)),
*values
)

(this assumes that your database uses %s for parameters; if it uses
? instead, replace "%%s" with "?". see the paramstyle documentation
in the db-api docs (pep 249) for more info)

</F>

Dec 5 '05 #2
Fredrik Lundh wrote:
cursor.execute(
"INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)),
*values
)


Thanks for the hint. However, I don't understand the syntax.

I will be inserting in to postgresql 8.x. I assumed the entire string would
be evaluated prior to being sent to the cursor. However, when I attempt to
manual construct the sql insert statment above I get an error:
print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),

*values)
File "<stdin>", line 1
print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
*values)
^
SyntaxError: invalid syntax
--
David Bear
-- let me buy your intellectual property, I want to own your thoughts --
Dec 5 '05 #3
David Bear wrote
Fredrik Lundh wrote:
cursor.execute(
"INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)),
*values
)
Thanks for the hint. However, I don't understand the syntax.

I will be inserting in to postgresql 8.x. I assumed the entire string would
be evaluated prior to being sent to the cursor.


Looks like you missed advice 1-3. I'll take it again: DON'T USE STRING
FORMATTING TO INSERT VALUES IN A DATABASE. Sorry for shouting,
but this is important. Parameter passing gives you simpler code, and
fewer security holes.
However, when I attempt to manual construct the sql insert statment
above I get an error:
print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),

*values)
File "<stdin>", line 1
print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
*values)
^
SyntaxError: invalid syntax


DON'T MANUALLY CONSTRUCT THE SQL INSERT STATEMENT. Use string
formatting to insert the field names, but let the database layer deal with
the values.

If you want to do things in two steps, do the fields formatting first

query = "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields))

and pass the query and the values sequence to the database layer:

cursor.execute(query, values)

The database will take care of the rest.

</F>

Dec 5 '05 #4
Fredrik Lundh wrote:
David Bear wrote
Fredrik Lundh wrote:
> cursor.execute(
> "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)),
> *values
> )
Thanks for the hint. However, I don't understand the syntax.

I will be inserting in to postgresql 8.x. I assumed the entire string
would be evaluated prior to being sent to the cursor.


Looks like you missed advice 1-3. I'll take it again: DON'T USE STRING
FORMATTING TO INSERT VALUES IN A DATABASE. Sorry for shouting,
but this is important. Parameter passing gives you simpler code, and
fewer security holes.


please, shout until I 'get it'... I don't mind. I just don't understand
using the star in front of the values variable; it generates a syntax
exception for me.
However, when I attempt to manual construct the sql insert statment
above I get an error:
>>> print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),

*values)
File "<stdin>", line 1
print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
*values)
^
SyntaxError: invalid syntax


DON'T MANUALLY CONSTRUCT THE SQL INSERT STATEMENT. Use string
formatting to insert the field names, but let the database layer deal with
the values.


since I am so new to this, I didn't know the database layer would handle
this for me. Is the evaluation of the fieldname done by the dbapi layer or
by postgresql?
If you want to do things in two steps, do the fields formatting first

query = "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields))

and pass the query and the values sequence to the database layer:

cursor.execute(query, values)
I found this info on the pgdb interface:

http://www.pygresql.org/pg.html

section 4.7 describes the insert method. It is passed the tablename and a
dictionary. But it doesn't describe how it resolves fieldnames and their
values. I assume the dictionary key MUST correspond to a named field.

The database will take care of the rest.
this is my trouble. I always think I need to do more -- but I can't seem to
find good examples on the http://www.pygresql.org/pgdb.html website.

Do know of any good examples?

</F>


--
David Bear
-- let me buy your intellectual property, I want to own your thoughts --
Dec 5 '05 #5
Fredrik Lundh wrote:
David Bear wrote
Fredrik Lundh wrote:
> cursor.execute(
> "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)),
> *values
> )


Thanks for the hint. However, I don't understand the syntax.

I will be inserting in to postgresql 8.x. I assumed the entire string
would be evaluated prior to being sent to the cursor.


Looks like you missed advice 1-3. I'll take it again: DON'T USE STRING
FORMATTING TO INSERT VALUES IN A DATABASE. Sorry for shouting,
but this is important. Parameter passing gives you simpler code, and
fewer security holes.
However, when I attempt to manual construct the sql insert statment
above I get an error:
>>> print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),

*values)
File "<stdin>", line 1
print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
*values)
^
SyntaxError: invalid syntax


DON'T MANUALLY CONSTRUCT THE SQL INSERT STATEMENT. Use string
formatting to insert the field names, but let the database layer deal with
the values.

If you want to do things in two steps, do the fields formatting first

query = "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields))

and pass the query and the values sequence to the database layer:

cursor.execute(query, values)

The database will take care of the rest.

</F>


I think I'm missing some important documentation somewhere. Here's what I
tried (using both % and $ signs):
sql 'INSERT INTO nic (addr_code,ip_address,property_control,mac_address ) VALUES
(%s);'
sql2 'INSERT INTO nic (addr_code,ip_address,property_control,mac_address ) VALUES
($s);' values ['p', '129.219.120.134', '6154856', '00:40:50:60:03:02']
cursor.execute(sql1, values) Traceback (most recent call last):
File "<stdin>", line 1, in ?
NameError: name 'sql1' is not defined cursor.execute(sql, values)

Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/usr/lib64/python2.4/site-packages/pgdb.py", line 163, in execute
self.executemany(operation, (params,))
File "/usr/lib64/python2.4/site-packages/pgdb.py", line 187, in
executemany
raise OperationalError, "internal error in '%s': %s" % (sql,err)
pg.OperationalError: internal error in 'INIT': not all arguments converted
during string formatting

I get the same error if using $ sign.

When I look at the pygresql dbapi official site at
http://www.pygresql.org/pgdb.html

"this section needs to be written"...

I would really appreciate some more examples on using pgdb (pygresql)
--
David Bear
-- let me buy your intellectual property, I want to own your thoughts --
Dec 6 '05 #6
On Mon, 05 Dec 2005 18:00:21 -0700, David Bear wrote
Fredrik Lundh wrote:
DON'T MANUALLY CONSTRUCT THE SQL INSERT STATEMENT. Use string
formatting to insert the field names, but let the database layer deal with
the values.

If you want to do things in two steps, do the fields formatting first

query = "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields))

and pass the query and the values sequence to the database layer:

cursor.execute(query, values)

The database will take care of the rest.

</F>


I think I'm missing some important documentation somewhere. Here's
what I tried (using both % and $ signs):
sql 'INSERT INTO nic (addr_code,ip_address,property_control,mac_address )
VALUES
(%s);'
sql2 'INSERT INTO nic (addr_code,ip_address,property_control,mac_address )
VALUES
($s);' values ['p', '129.219.120.134', '6154856', '00:40:50:60:03:02']
cursor.execute(sql1, values) Traceback (most recent call last):
File "<stdin>", line 1, in ?
NameError: name 'sql1' is not defined cursor.execute(sql, values)

Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/usr/lib64/python2.4/site-packages/pgdb.py", line 163, in execute
self.executemany(operation, (params,))
File "/usr/lib64/python2.4/site-packages/pgdb.py", line 187, in
executemany
raise OperationalError, "internal error in '%s': %s" % (sql,err)
pg.OperationalError: internal error in 'INIT': not all arguments converted
during string formatting

I get the same error if using $ sign.

When I look at the pygresql dbapi official site at
http://www.pygresql.org/pgdb.html

"this section needs to be written"...

I would really appreciate some more examples on using pgdb (pygresql)


It appears that Fredrik gave you good advice but bad example code. The example
he gave you constructs an insert query with only one parameter placeholder.
You'll need as many placeholders as the number of values that are inserted.

The following example should work better:

def insertDict(curs, tablename, data):
fields = data.keys()
values = data.values()
placeholder = "%s"
fieldlist = ",".join(fields)
placeholderlist = ",".join([placeholder] * len(fields))
query = "insert into %s(%s) values (%s)" % (tablename, fieldlist,
placeholderlist)
curs.execute(query, values)

The main thing to note here is that we *are* using string formatting to build
a query that's based on a variable table name and a variable column list, but
we *are not* using string formatting to fill in the values.[*]

On a somewhat related note, it's unfortunate that many database modules use %s
as parameter placeholders, because it makes it too tempting to write bad code
such as

cur.execute("insert into tab1(spam,eggs) values (%s,%s)" % (a,b)) # Bad, uses
vulnerable and error-prone string formatting

instead of

cur.execute("insert into tab1(spam,eggs) values (%s,%s)", (a,b)) # Good, uses
parameters.
[*] This code blindly trusts that the table name and dictionary keys don't
contain SQL injection attacks. If the source of these is not completely
trustworthy, the code needs to be hardened against such attacks. I'll leave
that as an exercise for the reader.

Hope this helps,

Carsten.

Dec 6 '05 #7
Carsten Haese wrote:
The
example he gave you constructs an insert query with only one parameter
placeholder. You'll need as many placeholders as the number of values that
are inserted.

The following example should work better:

def insertDict(curs, tablename, data):
fields = data.keys()
values = data.values()
placeholder = "%s"
fieldlist = ",".join(fields)
placeholderlist = ",".join([placeholder] * len(fields))
query = "insert into %s(%s) values (%s)" % (tablename, fieldlist,
placeholderlist)
curs.execute(query, values)

The main thing to note here is that we *are* using string formatting to
build a query that's based on a variable table name and a variable column
list, but we *are not* using string formatting to fill in the values.[*]

On a somewhat related note, it's unfortunate that many database modules
use %s
as parameter placeholders, because it makes it too tempting to write bad
code
such as

cur.execute("insert into tab1(spam,eggs) values (%s,%s)" % (a,b)) # Bad,
uses vulnerable and error-prone string formatting

instead of

cur.execute("insert into tab1(spam,eggs) values (%s,%s)", (a,b)) # Good,
uses parameters.

[*] This code blindly trusts that the table name and dictionary keys don't
contain SQL injection attacks. If the source of these is not completely
trustworthy, the code needs to be hardened against such attacks. I'll
leave that as an exercise for the reader.

Hope this helps,

Carsten.


Thank you very much for the greater explanation. Yes, I was not
understanding that that %s in one instance was a python string format
operator, and in another instance it was a placeholder sent to the dbapi
objects (and I supposed on down into the data base cursor) for the
parameters following the function call. BIG DIFFERENCE.

--
David Bear
-- let me buy your intellectual property, I want to own your thoughts --
Dec 6 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4272
by: N i E ¶ W i A D o M y | last post by:
Hello. I have a problem with insert a picture (bmp) to data base. I wan't add picture to cell in data base (Access) like double decimal. Someone have a idea how to make this? Please help MarianZED
2
1747
by: gaston | last post by:
Hi All I have three data bases with a table each one, what I want to do is to get the data from each table and insert it in a new data base table. The thing is that may be there is going to a lot of data may be 300,000 registers. Is there a way to code this? Thanks In Advance
8
3663
by: slb813 | last post by:
Hello, I am having a problem that I can't seem to work out. I am running Apache 2.2 on a Windows XP platform, using PHP5. I am attempting to insert a row into a MS Access data base with a PHP script. I have a good connection into the data base, because select's work well. The error that I am getting is: Warning: odbc_exec(): SQL error: ...
0
1168
by: twilight lover | last post by:
i write a small prog with builder c++ 5 that extract lines from text file then add them into Paradoxe7 table these lines will appear in a DBGrid on the Form by a click on Button1 the file contains many paragraphs each paragraph has the same end line "end" , like this ; line1 line2 line3 ... end
1
1293
by: vaskarbasak | last post by:
Hi friends, I have a 500 zip file. I am trying to read the zip file .Then insert the data in to DB .But it is taking long time. I have to insert all record into DB with in 1 hr. please help me... Thanks Vaskar
13
2128
by: deepunarayan | last post by:
Hi I have Problem in ASP. I have created a Multi choice Question page in ASP with Submit button. When I submit my page the User Selected values will be taken to the other page where validation will happen. My problem is, the number of question will be dynamic and I need to validate the scores and give the final scores. I tried to use...
0
1453
by: Sabzar | last post by:
Hi every buddy.. i need to store resume in mysql data base and also retrieve to display it in jsp page .what the data type should i use for database. i tried it as a blob type. and in action class i tried to store it as String fn="file path ......"; File file = new File(fn); fis = new FileInputStream(file);...
0
1606
Vkas
by: Vkas | last post by:
I have a default .aspx form i HAVE 3 TEXT BOX 1 DROPDOWN LIST IN MY DEFAULT PAGE. a access data base file at locaion C:\asp.netdb\feedback.accdb I want to insert the values into the access database from this page my default.aspx code is <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
0
7384
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7567
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7718
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
5862
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5239
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4878
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3377
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3372
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
615
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.