Does anybody have any suggestions on downloading/installing a MySQL client for Pythonista?
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/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:
""" My authentication info is in the defaults in connection.py """
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 )
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?
@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)
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)#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_nameas 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.
@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])
cursor.execute("select name from %s" % [tablename])
The difference is that the second variant uses standard Python string formatting (i. e. the value of
tablenameis just put into the string at the position of the
%s), which leaves you vulnerable to injection attacks. If
tablenamewas taken from a public web form, then you could enter
mytable; drop table mytableas the table name, which would result in a query of
select * from mytable; drop table mytableand 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
tablenamein 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
whereclauses and such. Perhaps because letting users specify arbitrary table names is dangerous enough? Not sure...