Forums

Is it possible to export csv fro PAW to an external mysql? is it using df.to_sql command?

Yes. Make the csv available somewhere that the external MySQL server can see it and then import it there. The same applies for df.to_sql.

Hi Glenn,

Actually an apps that is written in Ionic2 want to access the mySQL data. One way is using AJAX and PHP to access MySQL data but PAW does not have PHP support, how can the apps and query the data in Mysql? PS: I use df.to_sql which is in csv form?

Just to clarify, are you trying to access mysql data in real time? Or are you trying to export a single snapshot of the data?

Hi conrad, just one time per day, export to mysql then using apps to access the data. I need to know what I need to do to make that happen

do you know how to connect to the database in general? Does this help?

I have done to export to PAW MySQl using df.to_sql command. Now I found that I need to export using json format instead of dataframe(correct me if I am wrong) Currently MySQL only allocate 0.25MB and I afraid json will consume more space. I may need to export to external database, correct? Now, I need to figure out how to export json to MySQl too, any idea?

To clarify, are you trying to put data into the pythonanywhere mysql database, or an external database?

If you are just trying to put data into pythonanywhere, and your data is already in pythonanywhere, then the easiest would be to directly put it in without export to csv etc.

I want to put the data in json form instead of dataframe. But i think pythonanywhere mysql is too small for json data

You mean you want to store json strings into the pythonanywhere mysql database?

You could use the json library I guess?

if there is a way I can run PHP in MySQL, I will convert data in MySQL to json since i load data with df.to_sql. which command in json library that allow me to save data in MySQL in data form?

I am curious without PHP, how can I connect the MySQL database from an apps which is using javascript. Please advice

An external app will not be able to connect directly to the mysql database using js because of security reasons. This is because it is strongly discouraged to open up a database completely to the internet. Instead, if you want to access it externally, you will have to use ssh tunnelling.

Ssh tunneling and php are the two only ways,correct? Which one is simpler?

We don't support PHP actually, so there's only one way! SSH tunnelling is a little fiddly, but you should be able to get it to work. See the link conrad already posted.

Incidentally, by coincidence I needed to dump some mysql data to json the other day, and I wrote a short script to do it -- not sure if it'll help with what you want to do, but there it is just in case...

Hi Harry, what is the max connections allow for MySQL database?

3 per web app, per web worker. For a free account, that means 3.

That means you need to be quite careful about closing database connections when you're finished with them, including when there's an error. It's a good idea to use a tool that does that for you and implements a connection pool, like sqlalchemy or django's ORM...

Hi Harry, I mean for a paid account, I have access from Android apps. I want to know how many ppl can query or access at the same time.

How are your Android apps connecting to the database, exactly?

apps will query thru MYSQL database. Data is sent from Pythonanywhere everyday to MYSQL. Apps side is written by vendor but I need to make sure database able to support. It will take json data but it will convert from mysql data after query.

I'm a bit confused -- if the apps are connecting directly to the database, then they will need credentials to log on to the database. That means that anyone who has the app will be able to connect to the database and change other people's data. And on PythonAnywhere, they'd need to use an ssh tunnel to connect to the database, which would require your own PythonAnywhere username and password, so anyone who has the app would also be able to log into your PythonAnywhere account and do whatever they wanted.

I'm sure I must be missing something, because I'm sure you won't be doing anything quite so insecure!

I have a similar app. I use HTTP like REST API to connect to MySQL via Flask App through sqlalchemy. Even I end up getting max_user exceeded error often. I am yet to find a solution. I guess, bkcollecton might be adopting a similar approach.

@SrinivasK -- that sounds like you're using the right approach. What settings are you using when you create the SQLAlchemy connection?

hi guys I am beginner

I will share my code so that others can benefit. If there is anything to be corrected, please let me know

I have a object_test.py file as below:

import client_model as model
from sqlalchemy import orm
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
from flask import Flask
app = Flask(__name__)
SQLALCHEMY_DATABASE_URI = "mysql+mysqlconnector://{username}:{password}@{hostname}/{databasename}".format(
username="SrinivasK",
password="******",
hostname="SrinivasK.mysql.pythonanywhere-services.com",
databasename="SrinivasK$NameOfDB",)
app.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI
app.config["SQLALCHEMY_POOL_RECYCLE"] = 299
app.config['SQLALCHEMY_POOL_SIZE'] = 3
engine = create_engine(SQLALCHEMY_DATABASE_URI, poolclass=NullPool)

in my flask_app

from object_test import engine
connection = engine.connect()
queryText = "select UID from table where name='Srinivask'"
try:
     a=connection.execute(queryText)
except exc.OperationalError:
     connection = engine.connect()
     a=connection.execute (queryText)

Few points that I would like to add (Which I could read from various sources, but not entirely sure) 1. SQLALCHEMY_POOL_RECYCLE is set as 299, which means a given connection will be alive for 299s and then it ll be disposed.

1. SQLALCHEMY_POOL_SIZE is set as 3, (to match with max_user allowed) so that every 4th connection is requested, 1st connection will be disposed and will be given to the new connection request.

2. The try and except with exc.OperationalError is used, because when sqlchemy wants to connect to the DB, it always assumes that there is already an existing connection. At times all the connections may be already busy. In which case it throws exc.OperationalError error and connections are freed. So the exception is caught and another attempt is made to connect, which usually succeeds.

3. if I try to connection.close in each of my apps, I get an error.

4. In spite of the above, I end up with some error if too many HTTP calls are made from my mobile app (which I have tried to eliminate through my mobile code)

Note: I tried to used poolclass=NullPool in create_engine, so that the confusing concept of Pooling is eliminated (each connection will be destroyed and recreated at each new request. This is probably more efficient in case of web apps, but in my opinion for mobile apps each connection is a new request and better off handle each time.) Not sure how this has impacted so far. I couldn't come up with anything to test this yet.

Thanks.

Hmmm. You definitely shouldn't need the try/except if you're using SQLAlchemy -- it should handle all of that kind of thing for you.

I suspect the problem is that although you're specifying SQLALCHEMY_POOL_RECYCLE and SQLALCHEMY_POOL_SIZE, you're not actually providing them to SQLAlchemy. By putting them inside app, you're associating them with your Flask app, but you're not making the SQLAlchemy library aware of the Flask app.

There are two options for fixing that:

(Recommended) Use the Flask SQLAlchemy extension:

from flask.ext.sqlalchemy import SQLAlchemy
...
db = SQLAlchemy(app)

Less good, but probably OK -- pass the pool size/recycle parameters in when you create the engine:

engine = create_engine(SQLALCHEMY_DATABASE_URI, poolclass=NullPool, pool_size=3, pool_recycle=299)

@SrinivasK my python side is similar with yours. But i haven't figure out the MySQL side as I plan to export to an external MySQL that support PHP. I am not sure how many connections can support by PAW MySQL, but it seems it supports if only a few ppl query the database (sorry for confusion in previous post, i actually just want to allow others to query the data in MySQl instead of access)

I notice you mention about REST API, what is the advantages compare using HTTP, PHP to query MySQL? Is REST API allow me to connect 2 MySQL (same data) as I want if database 1 is too busy, it can actually query from database 2 automatically.

Thanks

@giles, I was assuming that NullPool meant that there won't be any pooling. Then will it still be relevant to give a pool_size and pool_recycle? Thanks for your inputs, I am implementing them.

@bkcollection, Not sure if I understood your requirement correctly. But I will try to answer a simple question.

Question: I have a MySQL database in Pythonanywhere, I want to query it from an external source (from a server supporting PHP or an android app)

Answer: I will have to make one page/app in pythonanywhere (say in flask) for each possible query type. Like say a telephone directory which will 1. return phone no. from contact name, 2. return contact from phone no. 3. gets last call details from phone number 4.gets last call details from contact name. Then you will have to make 4 different page/app in pythonanywhere, which will take inputs through POST and return a string (in csv, json, xml format) based on the query result. I will show one example:

@app.route('/returnPhoneNoFromName', methods=['GET', 'POST'])
def phone_no_from_name():
    from object_test import engine #refer above comment and modifications suggested by giles
    d= None
    if request.method == 'POST' or request.method == 'GET':
        name = request.form['name'].lower()
        connection = engine.connect()
        queryText = "select phoneNo from TableDirectory where name='"+name+"'"
        try:
            a=connection.execute(queryText)
        except exc.OperationalError:
            connection = engine.connect()
            a=connection.execute (queryText)
        for b in a:
            for c in b:
                d=c
        if d != None:
            return str(d)
    return 'NOT FOUND'

This is a simple example where the return string can be more complicated. also you can make one of these apps for each of your query types. POST your query via HTTP and get your results from your Pythonanywhere MySQL database.

Note: I am a beginner when it comes to both Python and Database management. Any suggestions/improvements will be most appreciated. Thanks.

@SrinivasK - I don't think any of the pool configuration options will have any effect when using nullpool. In your example, I don't see much reason to use sqlalchemy if you're just using raw SQL, but perhaps you're also using the sqlalchemy ORM and you just meant it as an example.

@bkcollecton - I'm also not sure that I understand what your question is. Does SrinivasK's suggestion help? If not, what is missing?

@both - The example has a security hole, so don't use that code or similar code. The hole is that by doing simple string addition to create the query, a user of your site can give a name that includes SQL, and thus run queries on your database that you didn't intend. A better way to make call execute is like this:

connection.query("select phoneNo from TableDirectory where name=?", name)

If you'd like to learn more about this kind of vulnerability, look up sql injection

Yes, I was trying various options, and I didn't remove that import. I will do that. And yes I understand the security hole pointed out by you. I will correct my implementation. Thanks.