SQLAlchemy / Python / Pyramid Write To Database Issue

Question

The problem:

I am working on a bit of code that is meant to create a new record in a linking table. The linking table has a primary key that is an auto number, the other two fields are VARCHAR with a length of 10.

The issue I'm having is I cannot seem to get partID into the table. If you look at the sql output you can clearly see it write None and u'1' (the orderID) to the table. So that tells me its recieving the orderID just fine. Also you can see that I did a print to find out what is inside my variable before passing it to the new object. It has 3 in it which is the correct partId. Somewhere between creating the new object and writing to the table it passes a null.

I've tried to cast it, Ive tried different ways of pulling the partID from the database, etc and I cannot for the life of me figure out what is wrong.

The code:

def updateOrderParts_view(request):
    part = None
    partToOrder = None
    idnum = None

    part = DBSession.execute(\
    "SELECT partID "+\
    "FROM tblParts "+\
    "WHERE partName = " + "'" +request.POST['partName'] +"'").fetchone()

    print "<---DEBUG--->"
    print part['partID']

    partToOrder = PartsByOrder(part['partID'], request.POST['orderID'])

    DBSession.add(partToOrder)
    return{}

The terminal output:

<---DEBUG--->
3
2013-04-24 08:14:47,985 INFO  [sqlalchemy.engine.base.Engine][Dummy-2] INSERT INTO "tblPartsByOrder" ("partID", "orderID") VALUES (?, ?)
2013-04-24 08:14:47,985 INFO  [sqlalchemy.engine.base.Engine][Dummy-2] (None, u'1')
2013-04-24 08:14:47,986 INFO  [sqlalchemy.engine.base.Engine][Dummy-2] COMMIT

I would appreciate any thoughts or comments on this issue

Thanks for your time

Please, please, never-ever manually construct an SQL query, especially from bits you've got from user input. Your code is vulnerable to SQL injection. Always use bind parameters: docs.sqlalchemy.org/en/latest/core/… — Sergey
– Sergey, Commented Apr 25, 2013 at 19:46

Peter Tirrell · Accepted Answer · 2013-04-26 17:11:01Z

1

First, I would look at doing the SQL lookup a little different, if you can. (I guess it depends, do you have a model based on "tblParts"? I'm going to use an example assuming there is a model object "Part"):

part = DBSession.query(Part.partID).filter(Part.partName == request.POST['partName']).first()

From there, I'm rusty on the exact syntax but I think you could do something like

print "<---DEBUG--->"
print part

partToOrder = PartsByOrder(part, request.POST['orderID'])

DBSession.add(partToOrder)

return{}

You might need to case "part" to a string (str(part)) if it's a casting problem.

Good luck,

answered Apr 26, 2013 at 17:11

Peter Tirrell

3,0134 gold badges33 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLAlchemy / Python / Pyramid Write To Database Issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related