connect python to mysql

connect python to mysql

Postby iFunction » Tue Sep 20, 2016 1:29 pm

Hi there,

Really struggling here, following this tutorial: https://code.tutsplus.com/tutorials/cre ... -cms-22972 and I have everything working except the connection to Mysql.

The following is the app that runs everything:
Code: Select all
#!/usr/bin/python3

from flask import Flask, render_template, json, request
from flask.ext.mysql import MySQL
from werkzeug import generate_password_hash, check_password_hash

mysql = MySQL()
app = Flask(__name__)

# MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'jay'
app.config['MYSQL_DATABASE_PASSWORD'] = 'jay'
app.config['MYSQL_DATABASE_DB'] = 'BucketList'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)


@app.route('/')
def main():
    return render_template('index.html')

@app.route('/showSignUp')
def showSignUp():
    return render_template('signup.html')


@app.route('/signUp',methods=['POST','GET'])
def signUp():
    try:
        _name = request.form['inputName']
        _email = request.form['inputEmail']
        _password = request.form['inputPassword']

        # validate the received values
        if _name and _email and _password:
           
            # All Good, let's call MySQL
           
            conn = mysql.connect()
            cursor = conn.cursor()
            _hashed_password = generate_password_hash(_password)
            cursor.callproc('sp_createUser',(_name,_email,_hashed_password))
            data = cursor.fetchall()

            if len(data) is 0:
                conn.commit()
                return json.dumps({'message':'User created successfully !'})
            else:
                return json.dumps({'error':str(data[0])})
        else:
            return json.dumps({'html':'<span>Enter the required fields</span>'})

    except Exception as e:
        return json.dumps({'error':str(e)})
    finally:
        cursor.close()
        conn.close()

if __name__ == "__main__":
app.run()


I seem to have all the right Flask modules installed, as no errors are now thrown up, and the little website looks quite nice, I've modified it for my purposes. However, I cannot get python to connect to mysql. I have tried typing the relevant parts directly into the terminal, and there are no errors, but when I log into mysql in another terminal window, the table is empty.

How can I test if I am actually connecting to it as python doesn't give any indication, so long as the syntax is right, it just gives you another prompt.

Regards
iFunction
 
Posts: 41
Joined: Mon Apr 04, 2016 2:33 pm

Re: connect python to mysql

Postby nilamo » Tue Sep 20, 2016 6:13 pm

Does the user have insert permissions on the database?
Is commit() actually getting called (ie, is len(data)==0)?
If you run the stored procedure from a mysql terminal, does it work? (unrelated, but why are you using stored procedures?)
This forum is shutting down. Future discussion should take place at http://python-forum.io
User avatar
nilamo
 
Posts: 87
Joined: Thu Jul 21, 2016 8:00 pm
Location: Michigan

Re: connect python to mysql

Postby iFunction » Fri Sep 23, 2016 10:33 am

Thanks for your input, everything I have done has been in line with the tutorial, so for example, I am using stored procedures because the tutorial said to do so and as I don't know what they are pe se, it is tricky to know where the issue lies.

I've now decided to stop doing anything but carry on using the Raspberry Pi for learning python only, as it is not good for anything database related, even building a LAMP server has issues that a Linux PC doesn't have, and before you say it being a learning curve, yes it is, but some are a lot easier and quicker than others, and while I know little about networking at the moment, and am trying to learn coding, I would rather not have the frustration of finding an answer that often takes up to a month to solve, when it just isn't there on other distos. Classic example "Open Office", excellent bit of software, love it you can do lots with it, but only if you have version 5 or above. Writing macro's in Open Office 4 is painful and deeply unpleasant code that runs on Open Office 5 and excel will run on Open Office 4, but it simply won't do what you want it to do, your spreadsheets will be full of formulas instead of data horrible.

I have Linux Mint on my main computer at home, and it runs everything fine. In fact all the issues I have had learning on the Raspberry Pi is down to the fact that it is so stripped down, many standard things are a problem, no firewall being one of the biggest. All you can do with a Raspberry pi is ssh into it, you can't connect directly to any database, that I have used, from outside, something blocks all ports as standard, but it is not a traditional firewall as the Raspberry Pi doesn't come with one. Great if you're a kid learning out or an Linux expert, but for beginner/intermediate programmer, forget it and get a known linux distro running on an intel or AMD cpu. Since learning on my Linux box at home, suddenly most of my issues have just gone away. I originally was learning about databases using couchdb, but that fell flat on it's face because of this not being able to connect to it from another computer, you could ssh into it and then use it as if you were there, but I don't think that's very helpful when you are trying to learn what is good and bad practice in general.

just my 2 cents of battling with a network of raspberry pi's (I have to use them for work) for over a year. Oh yes, and they lose their IP addresses for some reason, haven't been able to work that one out either.
iFunction
 
Posts: 41
Joined: Mon Apr 04, 2016 2:33 pm


Return to Web Development

Who is online

Users browsing this forum: No registered users and 2 guests