Forum Archive

Does anybody have any suggestions on downloading/installing a MySQL client for Pythonista?

Hi Eran,

It is possible to run a mysql client, but it takes a bit of work. MySQL provide a pure python connector here http://dev.mysql.com/doc/refman/5.5/en/connector-python.html

It is licenced under GPL2 so you get the source. I got it to work by first installing into a regular Python 2.7 system on a linux box. Once installed and tested on the 'donor' find the mysql directory in site packages and copy the contents over to some other place for editing.

In the copy directory move everything into the toplevel directory, delete the empty init.py files and any .pyc files. Remove the sub directories. In your new mysql directory you should have 14 or so python files. You will need to change some filenames to something temporary to avoid over-writing.

To get it to work in pythonista start editing roughly as follows. Name the core init.py file to something like mysqldb (note: exclude .py otherwise pythonista won't import). Rename all the other files by just removing the .py extension. Rename the init.py file from the locales directory to locales.

Now for each file look for the 'mysql.connector...' import statements and remove the package path, just leave the core module which will have the same name as one of the above files. In the locales file edit line 48 (ish) which is an import statement and remove the path, leave the 'client_error' bit - this is the eng locale taken care of.

The next stage is to get the whole directory into pythonista which I ended up doing by importing the files in another app and re-creating them in pythonista using the clipboard - there are probably much more efficient ways to do this !

You should now have a mysql sub-directory in your main project directory. The last step in your main program is to import sys and insert the sub directory into sys.path - I just grab the last entry in path which is the project directory and do an os.path.join.

Longwinded, but it works for me. It does take a few seconds to load and connect on my ipad2, but not so long as to be tedious. I have been accessing various Mysql 5 databases for checking data and writing data - so far has worked exactly as a regular installation, and quite quick once loaded even for fairly large queries.

Hope this helps.

Any chance you could post a link to download your modified folder?

Hi guys - I've found a github repo by Tomasso Turchi that purports to do just this. I have yet to try it since I need to figure out how to get the dozen or so files into pythonista....

but it's here:
https://github.com/tommasoturchi/mysql-connector-pythonista

https://github.com/jsbain/GitHubGet lets you download github repos

I just have to advertise mine :D.
https://github.com/Vik2015/gitrepo lets you download repos, releases and gists easily with a single interface :)

Just an update: putting the files from the repo above, without their root folder, into your site-packages folder, seems to work. Then import lowercase mysqldb.

This is a bug report regarding use of mysqldb with Pythonista.

I was pulling my hair out wondering why I couldn't create a table with a certain column be a DOUBLE, even when I inserted the exact same data into it that was inserted into another column that was also a double.

It turns out that if you're creating and deleting a table multiple times (as one might for testing purposes) while in Pythonista, and you change one of the column types, you need to hard-quit Pythonista or the previous table column types will remain in effect, ignoring the updated column types in your code. Force quitting Pythonista and then re-running the same code seems to do the trick.

I am trying to get the mysql-connector-pythonista to work without success. I did:

db=mysqldb.connect()
""" My authentication info is in the defaults in connection.py """
cursor=db.cursor()

and I get

errors.OperationalError: MySQL Connection not available

How can I get back more information about why the connection is not being established? (I have tested the parameters (userid, password, server, db) and they work fine from my Mac using mysqldb for python.

@ihf have you tried passing your authentication info as such:

db = mysqldb.connect(host= "db url here", user = "usernameHere", passwd="password here", db="nameofdatabase",port = 3306 )

Hi @Tizzy
I saw you'd posted recently on this topic and hope you don't mind me asking a question.

I am trying this myself, but keep getting an error:

ImportError: no localisation for language 'eng'

Would you know what's causing this, and have you got it working yourself?

Thanks

@chriswilson Can you post the code you have where this is happening? (You can leave the username/password/server info blank)

Also where did you get Mysqldb for pythonista? (I've been meaning to post about the different mysql options between Pythonista2 and 3)

@Tizzy
Here's my code. It's the connect line that raises the error.

import mysqldb

db = mysqldb.connect(host= "http://my_host", user = "user", passwd="password", db="my_database", port = 3306)

cursor = db.cursor()

@chriswilson Running your code strictly as is does bring up the same error. However, as soon as I entered a valid database url, the error goes away. Are you sure you're inputting the correct IP address/url?

I think so - I'm using the hostname given by my provider (in the format http://mysql.my_host.co.uk). I just used the name of the database without a path - is this ok?

@chriswilson remove the 'http://' part of the url.

So it's just 'mysql.my_host.co.uk'

You don't need a path for the database. Just a name.

@Tizzy That seems to have worked - so simple when you know. Thanks!

Now I just need to figure out how to use the mysqldb module! :)

@chriswilson Here's a couple examples of how to do a few things with some premade functions for getting a list of tables, checking if a table exists, creating a table, getting the last saved entry, and getting a dump of all the data in a table. Obviously you have to fill out the credentials with your own stuff.

Let me know if you have any questions.

try:
    import pymysql as mysqldb
except ImportError:
    import mysqldb

def dbConnect():
    #port 3306 is the default for mysql
    #returns false if it can't successfully connect
    username = "username"
    password = "password"
    dataBase = "databaseOnServer"
    porty = 3306
    try:
        conn = mysqldb.connect(host="host.something.com", user=username,passwd=password,db = dataBase,port=porty)
    except mysqldb.err.OperationalError:
        print("Can't connect. Check your hostname/user/password info.")
        conn = False

    return conn


def getLatestSavedEntry(tableName):
    #gets the latest entry, the one with largest ID (must be a table with "ID set as an auto-incrementing primary key")
    #createTable function does this. 
    conn=dbConnect()
    if conn ==False:
        print("no connection")
        return

    cursor = conn.cursor()
    try:
        cursor.execute("SELECT * FROM "+str(tableName)+" where ID = (SELECT MAX(ID) FROM "+tableName+")")
        lastEntryData=cursor.fetchone()
        if lastEntryData == None:
            lastEntryData = ["doesnt","exist...nope"]
    except:
        lastEntryData =["table","doesn't","exist","...probably"]
        print(lastEntryData)

    cursor.close()
    return lastEntryData


def createTable(tableName):
    conn = dbConnect()
    if conn ==False:
        print("no connection")
        return

    cursor=conn.cursor()
    #adjust this string with the sequel commands you'd like, columns etc.
    sequelString = "CREATE TABLE "+str(tableName)+"(ID INT(11) PRIMARY KEY AUTO_INCREMENT, uuid VARCHAR(50),request_at DOUBLE, duration INT, totalDuration INT, ratingHistoryCalculatedAverage DOUBLE,ratingHistory5 INT, ratingHistory4 INT, ratingHistory3 INT, ratingHistory2 INT, ratingHistory1 INT, Surge VARCHAR(30), fare DOUBLE, fareTakeHome DOUBLE, Distance DOUBLE)"


    try:
        print(".....trying table creation")
        cursor.execute(sequelString)
        print("created new table!")
        return "Success"
    except:
        print("table couldnt be created...")
        return "Failure to create"

    cursor.close()



def getSequelData(tableName):
    #this gets all of the data in your selected database and table, returns False if 
    conn=dbConnect()
    if conn ==False:
        print("no connection")
        return

    cursor = conn.cursor ()

    #get the vertsion of your mysql
    cursor.execute("SELECT VERSION()")
    row = cursor.fetchone()

    queryString = "SELECT * FROM "+str(tableName)
    try:
        cursor.execute(queryString)
        data=cursor.fetchall()
        print(data)

    except mysqldb.err.ProgrammingError:
        print("DOESN'T EXIST, YOU MUST CREATE THIS TABLE TO BE ABLE TO FETCH ITS CONTENTS.")
        data = False



    cursor.close()

    return data

def doesTableExist(tableNameToCheck):
    tableNameToCheck = str(tableNameToCheck)
    tableList = getTableList()
    if tableList ==False:
        print("no connection")
        return

    for table in tableList:
        #tableList is a list of tuples of unicode w/second of tuple empty
        existingTable = str(table[0])#gets to unicode string
        #print(existingTable,"???",tableNameToCheck)
        if existingTable == tableNameToCheck:
            print("table "+tableNameToCheck+" already exists. Yay!")
            userTableExists = True
            break
        else: 
            userTableExists = False

    if userTableExists:
        #print("Table exists. moving on.")
        return True
    elif not userTableExists:
        #print("Table not found. Maybe you should create it.")
        return False

def getTableList():
    conn = dbConnect()
    if conn ==False:
        print("no connection")
        return False

    cursor = conn.cursor()
    #cursor.execute("select * from information_schema.tables")
    cursor.execute("SHOW TABLES")
    tableList=cursor.fetchall()
    #print(tableList)
    cursor.close()

    return tableList



if __name__=="__main__":

    #tests

    print( getLatestSavedEntry("someTable") )
    print( createTable("someTable") )
    print( getSequelData("someTable") )
    print( getTableList() )
    print( "table exists?:",doesTableExist("someTable") )

@Tizzy it is good practice to not do statement = 'SELECT * FROM ' + table_name' or even statement = 'SELECT * FROM ' + %s' % table_name as this can cause security issues with SQL injection. Most database packages (read: modules) will have something along the lines of

statement = '''
SELECT *
FROM ?
'''
with db.connect as conn:
    result = conn.execute(statement, (table_name,))

This is a more secure way of accessing batabases. The other way is good enough for person projects, but keep that in mind or little Bobby Tables will make your life awful as a DBA.

B.

@blmacbeth are you saying to use triple quotes for security purposes?

@Tizzy The triple quotes are not important. That's a Python syntax feature which allows you to write a string literal across multiple lines. SQL doesn't care about newlines, so it makes no difference whether you put everything in one line or on multiple lines.

The important part is the question mark in the query. For example, you should write

cursor.execute("select name from ?", [tablename])

instead of

cursor.execute("select name from %s" % [tablename])

The difference is that the second variant uses standard Python string formatting (i. e. the value of tablename is just put into the string at the position of the %s), which leaves you vulnerable to injection attacks. If tablename was taken from a public web form, then you could enter mytable; drop table mytable as the table name, which would result in a query of select * from mytable; drop table mytable and delete your data.

In the first variant, we don't use the standard Python formatting. Instead we put a question mark in the query string and pass the table name as the second argument to cursor.execute, which internally escapes the string properly to avoid any code injection attacks. (We have to put tablename in square brackets to make a single-element list - if we had five question marks, we'd pass a five-element list with all the values to insert.)

Though I think this question-mark insertion only works in some cases. Now that I think about it, it might only be allowed for where clauses and such. Perhaps because letting users specify arbitrary table names is dangerous enough? Not sure...

@Tizzy sorry for not explaining myself better. @dgelessus is correct about the ? being the important part.

I checked MySQL and it has its own syntax for variable arguments that just so happens to be %s. Shame on them…

thank you @blmacbeth and @dgelessus both. Even though I'm not using these for webforms, I want them to be as secure as possible. However, I'm failing to get either of your syntax examples to work where tableName is a variable of type string. Tried on both Python 2.7 and 3.5.

cursor.execute("select * from ?", (tableName,))

TypeError: not all arguments converted during string formatting

cursor.execute("select * from ?", [tableName])

TypeError: not all arguments converted during string formatting

cursor.execute("select * from ?", tableName)

Traceback (most recent call last): File "/Users/anty/Documents/devAndGraphic/xCode7era Dev/FiveStarsV2/Script/databaseExamples.py", line 153, in <module> print( getSequelData("someTable") ) File "/Users/anty/Documents/devAndGraphic/xCode7era Dev/FiveStarsV2/Script/databaseExamples.py", line 91, in getSequelData cursor.execute("select * from ?", [tableName]) File "/Library/Python/2.7/site-packages/pymysql/cursors.py", line 156, in execute query = self.mogrify(query, args) File "/Library/Python/2.7/site-packages/pymysql/cursors.py", line 135, in mogrify query = query % self._escape_args(args, conn) TypeError: not all arguments converted during string formatting

@Tizzy - quoting @blmacbeth:

I checked MySQL and it has its own syntax for variable arguments that just so happens to be %s. Shame on them…

Even though you should not use normal %-formatting, MySQL apparently uses the same format codes. That is, you still have to use %s as the placeholder. I've only used the sqlite3 module from the standard library, and there the placeholder is ?.

@dgelessus So if we can't use the ? syntax, then is using cursor.execute("statement %s",tableName) more secure than cursor.execute("statement"+tableName) ?

What exactly the placeholder is doesn't matter, it could be ? or %s or something else depending on the module. The important part is that you do not simply insert arguments into the query string using standard python features like "select " + arg, "select %s" % arg or "select {}".format(arg). All of those are BAD.

What you need to do is pass the query string, including placeholders, as the first argument to cursor.execute (or whatever function), and as the second argument a list/tuple of arguments. When you do this, the DB module gets the unformatted query string and the arguments that you want to insert. Then it can do whatever escaping and security checks necessary to ensure that no code injection is possible.

A safe query might look like cursor.execute("select %s", (arg,)). Note that even though we have a %s in the query, we do not format it using Python's % operator. We pass the query string and the arguments to the execute function, which then does the formatting for us in a safe way.

@dgelessus thanks for the education, I really do appreciate it. I tried to tackle this before at some point unsuccessfully and went back to doing it like I always had since there's no part in my use-case where a user manually creates input of their choosing (other than authentication to a 3rd party ... so the third party would have to want to hack me..). But let's figure this out once and for all...said I.

No matter how hard I try, using all of the syntaxes suggested here, can I pass a table name through as a parameter of the execute method.

I was only able to use the suggested syntax for passing a value ...such as below.

Works:

cursor.execute("SELECT * FROM someTable WHERE ID = (%s)",("1",))

Doesn't Work:

cursor.execute("SELECT * FROM (%s) WHERE ID = '1' ",("someTable",))

Are the parenthesis around the table name valid here?

You might also try using escape_string on the database connection object.

@JonB I get the same result with or without the parentheses, both in the success and failure examples. So it looks like the parentheses aren't necessary, but they work (with the success example above)

success:

cursor.execute("SELECT * FROM someTable WHERE ID = %s", "1" )

Failure:

cursor.execute("SELECT * FROM %s WHERE ID = '1' ","someTable")

I couldn't find anything called "escape_string" in the python 2/3 docs.

http://stackoverflow.com/questions/6514274/how-do-you-escape-strings-for-sqlite-table-column-names-in-python

has some good discussion. Seems you cannot substitute table names. A few options are to write your own escaping method, use sqlalchemy (included in pythonista), or use property mapping.

Right, sqlite3 does that too, you aren't allowed to insert table names. Their logic is probably "if you take table names from an untrusted source, you're already insecure".

Thanks @dgelessus @JonB @Tizzy
This has been very helpful for an SQL noob like me!

@dgelessus @JonB @Tizzy @ccc

So I have produced a game which has a highscore table for each of three difficulty settings. I have now adapted this to use a MySQL database to store results for any user, rather than just a local highscore table.

For this I set up a limited database user account (not my admin account!) with privileges restricted to INSERT and SELECT (i.e. not DROP, TRUNCATE etc). Is it safe/acceptable to bundle the account username and password in the code so people can use this feature? Would you suggest a different way?

I have not pushed it to GitHub yet as I'm new to SQL and wanted some advice! :)
(I am aware someone could cheat and simply use the details to insert a top score if they wished, but I'm hoping for honesty!)

Thanks

Why not sqlite3? Small, lightweight, included, ...

I got the connector from Tomasso Turchi. It seems to connect ok but when I try to execute "show tables;" it gives me an error: "no localization support for language 'eng'". This seems to come from a line in file locales.py . Is there any fix for this?

Thank you!

Related to https://github.com/omz/Pythonista-Issues/issues/27

Using tommasoturchi's mysqldb connector, I'm doing this:

import mysqldb
db = mysqldb.connect(host="my.host.com", user="user", passwd="pass", db="MyDBName", port=3306)
cursor = db.cursor()

and I'm getting an error: ord() expected string of length 1, but int found all the way down in the network.py file at self._packet_number = ord(packet[3])

wassup with that?!

Thanks

@iAmMortos said:

Using tommasoturchi's mysqldb connector, I'm doing this:

import mysqldb
db = mysqldb.connect(host="my.host.com", user="user", passwd="pass", db="MyDBName", port=3306)
cursor = db.cursor()

and I'm getting an error: ord() expected string of length 1, but int found all the way down in the network.py file at self._packet_number = ord(packet[3])

wassup with that?!

Thanks

I think the port should be a string.

My Recommendation is to use MySQL connector Python

Why?

• it works with both Python 2 and 3.
• Because it is official Oracle driver for MySQL for working with Python.
• It is purely written in Python
• You can also use C extension to connect to MySQL.
• MySQL Connector/Python is implementing the MySQL Client/Server protocol completely in Python.

Follow these article to work with MySQL Connector Python
Install MySQL Connector Python
Connect to MySQL from Python

I concur with @rahuldev2910 - I also recommend MySQL Connector/Python (disclaimer: I work for Oracle and write about MySQL Connector/Python).

Latest version is 8.0.12 and also include the new X DevAPI (the mysqlx) module if you want NoSQL access to MySQL.

I wrote a blog on https://mysql.wisborg.dk/2018/08/31/mysql-connector-python-on-ios-using-pythonista-3/ how to install MySQL Connector/Python in Pythenista 3.