Prev: Drawing glyphs based on their index (NOT their character code)
Next: starting repl programmatically
From: MRAB on 20 May 2010 12:56
Victor Subervi wrote:
> Hi;
> I have this code:
>
> #!/usr/bin/python
>
> import cgitb; cgitb.enable()
> import cgi
> import sys,os
> sys.path.append(os.getcwd())
> import MySQLdb
> from login import login
>
> def create_edit_passengers4():
> print "Content-Type: text/html"
> print
> print '''
> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Frameset//EN"
> "http://www.w3.org/TR/xhtml1/DTD/xhtml1-frameset.dtd">
> <head xmlns="http://www.w3.org/1999/xhtml">
> </head>
> <body>
> '''
> user, passwd, db, host = login()
> database = MySQLdb.connect(host, user, passwd, db)
> cursor = database.cursor()
> form = cgi.FieldStorage()
> cursor.execute('create table if not exists Passengers (id int(11)
> auto_increment primary key, flights_id int(11) not null, customer_id
> int(11) not null, foreign key (id) references Flights (flights_id),
> foreign key (id) references Customers (customer_id), name varchar(40),
> weight int) engine=InnoDB;')
> new_passengers = int(form.getfirst('new_passengers'))
> i = 0
> while i < new_passengers:
> cursor.execute('insert into Passengers values (Null, %s, %s, "%s",
> %s);' % (form.getfirst('%d:flight' % i), form.getfirst('%d:customer' %
> i), form.getfirst('%d:name' % i, ''), form.getfirst('%d:weight' % i)))

When performing SQL operations, don't insert the values using Python's
string formatting, because that makes it vulnerable to SQL-injection
attacks, ie don't do this:

cursor.execute(sql_command % values)

do this:

cursor.execute(sql_command, values)

> i += 1
> print "All passenger information has successfully been added."
> cursor.close()
> print "</body>\n</html>"
>
> create_edit_passengers4()
>
> Now, it throws no errors; however, it doesn't insert. If I print out the
> insert statement to screen and then manually insert it in MySQL it
> inserts. Huh??
>
I think you need to 'commit' any changes to do to the database.
From: Christian Heimes on 20 May 2010 13:21
MRAB wrote:
> I think you need to 'commit' any changes to do to the database.

Yeah, you are right.
Also some RDBMS don't support DDL and DML statements inside one
transaction. You may need to commit and begin after the create table DDL.

Christian

From: John Nagle on 21 May 2010 13:29
Christian Heimes wrote:
> MRAB wrote:
>> I think you need to 'commit' any changes to do to the database.
>
> Yeah, you are right.
> Also some RDBMS don't support DDL and DML statements inside one
> transaction. You may need to commit and begin after the create table DDL.
>
> Christian

Er, yes. Generally, you don't try a create on every insert transaction.
Since your create has foreign keys, it will fail if the other tables don't
already exist. So you need to have a more organized way of setting up the
database.

InnoDB is a proper transaction database. You must commit after insert
or the inserts will be properly rolled back and undone after
database disconnect.

John Nagle
 | 
Pages: 1
Prev: Drawing glyphs based on their index (NOT their character code)
Next: starting repl programmatically