Python Flask inserting data from form

Question

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')

Giles Thomas · Accepted Answer · 2017-03-02 16:00:25Z

4

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.

answered Mar 2, 2017 at 16:00

Giles Thomas

5,8952 gold badges37 silver badges53 bronze badges

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

1 Comment

warrenfitzhenry Over a year ago

Thanks @Giles. I have gone through your tutorial, and tried using SQLalchemy. Think am almost there but getting stuck here: http://stackoverflow.com/questions/42579079/flask-post-data-to-table-using-sqlalchemy-mysql

Stanislav Filin · Accepted Answer · 2017-03-02 11:58:22Z

1

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');

answered Mar 2, 2017 at 11:58

Stanislav Filin

1241 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

Python Flask inserting data from form

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related