[Tutor] formatting sql Was: Some help Please

[James Reynolds]

On Wed, Feb 15, 2012 at 12:13 PM, bob gailer <bgailer at gmail.com> wrote:

> Welcome to python help. We are a few volunteers who donate time to assist.
>
> To assist you better:
> 1 - provide a meaningful subject line - such as "formatting sql"
> 2 - tell us what OS and Python version you are using.
> 3 - what is your prior Python experience?
>
>
> On 2/15/2012 9:17 AM, JOSEPH MARTIN MPALAKA wrote:
>
>> take an example of updating Bank Accounts,
>> gaving the following table:
>>
>> acc_id          acc_name            standing_Balance
>> mn0001          computer             20000
>>
>> my problem is how can i credit the standing balance from user data,as
>> in making a deposit onto the computer account, using the code below:-
>>
>>
>>
>> import MySQLdb as mdb
>> import sys
>>
>> con = mdb.connect('localhost', 'joseph', 'jmm20600', 'savings');
>>
>> dep = input('Enter Amount: ')
>>
>>             cur.execute("UPDATE accounts SET Standing_Amount =
>> (Standing_Amount + dep) WHERE Acc_ID = 'MN0001'")
>>
> In you table the acc_id is 'mn0001'
> In your sql Acc_ID = 'MN0001'
> Why the difference in case?
> Why the () around Standing_Amount + dep?
>
>
>>        conn.commit()
>>
>> HOw do i format "dep" in order to be added onto the standing_Amount,to
>> make an increment?
>>
>> Please, is it the same thing with the withdrawing format, in case i
>> want to decrement the account as in withdrawing??
>>
>>
>> joseph
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
> --
> Bob Gailer
> 919-636-4239
> Chapel Hill NC
>
> ______________________________**_________________
> Tutor maillist  -  Tutor at python.org
> To unsubscribe or change subscription options:
> http://mail.python.org/**mailman/listinfo/tutor<http://mail.python.org/mailman/listinfo/tutor>
>


Normally, sql doesn't care about case with respect to table names. I
believe in certain implementations they are always lower case, even if you
pass an upper.

The problem, as Bob pointed out above, cur.execute("UPDATE accounts SET
Standing_Amount =
(Standing_Amount + dep) WHERE Acc_ID = 'MN0001'")

here, specifically, (Standing_Amount + dep)

Sql isn't going to have any idea what "dep" is when passed, because it is
going to see a string called "Dep". Well, it will know what it is, it will
be a string and will through an error that it can't add a string and
integer.
When i want to update a value in sql, normally i extract to python, do the
math there, and then update. You can do this in sql if you prefer and I'm
sure there are good reasons for doing it, I'm just more comfortable working
in python than I am sql.

Also, when passing variables to SQL, if you are doing raw queries and not
using some ORM, I would get used to using the "?" within the sql string and
then passing your variables in the following list. Otherwise, you are going
to leave yourself open to injection attacks (provided you are doing direct
string manipulations).

To do this in sql, you need to run a select statement first, saving the
results to a @variable. use that @variable later (after passing in an
integer) to add the two numbers.

To do this in python, also run a select statement, save it to some python
variable. Add your new result to it, and send that new result along to the
DB using your update query.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/tutor/attachments/20120215/ad7fdfb6/attachment.html>