CODEWITHSUNDEEP
pythonhtmlflaskmysql-pythonpythonanywhere
warrenfitzhenry
warrenfitzhenry

Reputation: 2299

Python Flask inserting data from form

This is a beginners attempt for inserting data into a mysql table using flask. I can enter data in the form, but it doesn't insert the data. I'm using pythonanywhere, so the error message is not clear...

Transactions.html:

 <h1>Enter a Transaction</h1>

 <form action="/transactions" method=post>
     <dl>
         <dt>Sale Item:
         <dd><input type=text name="Item" required/>
         <dt>Shack:
         <dd><input type=text name="Shack" required/>
         <dt>Reference:
         <dd><input type=text name="Paym_Reference" required/>
         <dt>Amount:
         <dd><input type=text name="Amount" required/>
     </dl)
     <br>
<p><input type=submit value=Enter>
  </form>

MySQL: transactions

+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| trans_id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| Item           | varchar(20) | YES  |     | NULL    |                |
| Shack          | varchar(10) | YES  |     | NULL    |                |
| Paym_Reference | varchar(20) | YES  |     | NULL    |                |
| Amount         | int(10)     | YES  |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+

Code:

import MySQLdb as my
conn = my.connect ('hello.mysql.pythonanywhere-services.com','hello','password','SIIL$Transactions')
c = conn.cursor ()

@app.route('/add_data', methods=['GET', 'POST'])
def add_data():
    Item= request.form('Item')
    Shack= request.form('Shack')
    Paym_Reference= request.form('Paym_Reference')
    Amount= request.form('Amount')
    c.execute("INSERT INTO transactions(Item, Shack, Paym_Reference, Amount) VALUES ('',%s,%s,%s,%s)")
    conn.commit()
    return 'Done'

@app.route('/transactions', methods=['GET', 'POST'])
def transactions():
    add_data()
    return render_template('Transactions.html')

Upvotes: 0

Views: 7258

Answers (2)

Giles Thomas
Giles Thomas

Reputation: 5867

PythonAnywhere developer here -- you should get the same error messages on our system as on any other -- did you check the error log for your web app?

There are a couple of problems with your code. Like Stanislav Filin says, the empty string in the SQL that you're passing to c.execute should not be there; you're specifying four columns so you only need four values.

But you also need to pass in some values to insert. The SQL string is just a command to send to the database, and you need to tell the system what values to replace the %ss with when it does that.

So instead of 

c.execute("INSERT INTO transactions(Item, Shack, Paym_Reference, Amount) VALUES ('',%s,%s,%s,%s)")

...you should have

c.execute("INSERT INTO transactions(Item, Shack, Paym_Reference, Amount) VALUES (%s,%s,%s,%s)", (Item, Shack, Paym_Reference, Amount))

See this help page for more examples.

One final thing -- using the MySQL library directly in a Flask app is going to be really quite hard. There are a lot of complexities involved in using it, particularly around connection management. The code you give above will work for a single user of your site, and only as long as it's not idle for longer than the database's connection timeout (five minutes on PythonAnywhere), but then it will break with confusing error messages.

SQLAlchemy is an excellent tool that you can put between your Flask code and the database that handles connection management and a lot of other things very well, and saves you the effort. I wrote a blog post on building a database-backed website with Flask and SQLAlchemy a year or so ago, and I really recommend you take a look.

Upvotes: 4

Stanislav Filin
Stanislav Filin

Reputation: 124

You have mistake in SQL-query

Your code:

c.execute("INSERT INTO transactions(Item, Shack, Paym_Reference, Amount) VALUES ('',%s,%s,%s,%s)")

Correct Insert

c.execute("INSERT INTO transactions(Item, Shack, Paym_Reference, Amount) VALUES (%s,%s,%s,%s)")

Another example from w3schools

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

Upvotes: 1

Related Questions