PDA

View Full Version : maya mel/python database interaction


tomViolet
10-15-2010, 08:47 AM
Hello all,

I undestand the answer to this might be beyone the scope of this forum but I thought it might be a good place to start. I have a set of pipeline tools-written in mel, that I now want to interact with a mysql database. Being a scripter and not a programmer I would like to keep things simple and with the minimum amount of changes to my existing tools.

-Is anyone aware of any command plugins that allow mel to interact with a mysql database?
-If I do go with the standard route and use a python mysql connector (MySQLdb), are there any platform independant modules that do not need compiling?
-If I do use python to perform a mysql query, how can I use the data returned in mel. For simplicity, say we have a python function that returns an array. Can i copy the array into mel? If so, how?

thanx in advance

NaughtyNathan
10-15-2010, 10:26 AM
I'm sure there will be a "built-in" sql module in python, as it has pretty much everything else.. :)
you can call python directly from MEL using the python command. you just capture it as you would a normal command (e.g. eval) and if python returns any kind of list it should return as a string array. I'm not sure exactly what happens when python returns some complex structures, but if this turns out to be an issue you could aways write a very small new python function that takes the complex data and returns the stuff you want as a simple list..

string $list[] = `python "import sqlBlah;sqlBlah.doSomething(param)"`;This will undoubtably get messy though due to the horror of passing MEL variables into the python string and the amount of python you may have to actually execute to do what you need to do...

:nathaN

red_oddity
10-15-2010, 05:13 PM
we use the MySQLdb sitepackage and a function like


def hos_db_query( sql='' ):
db_data = ''
try:
db = MySQLdb.connect("server", "user", "pass", "database")
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
#sys.exit (1)
cursor = db.cursor()
try:
cursor.execute( sql )
db.commit()
db_data = cursor.fetchall()
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit (1)

return db_data #because we use fetchall, this function always returns a list of lists, deal with it.


to retreive some database table data just call the function like so:


my_data = hos_db_query( 'SELECT mystuff FROM mytable WHERE my_id=1' )


precompiled MySQLdb site packages can be downloaded from here : http://www.codegood.com/archives/4

Also, just switch completely to Python or PyMel, that way you have access to a lot of really handy stuff (like hashlib and insane amounts of formatting tools )
cheers,
Sven

NateH
10-16-2010, 04:58 AM
-Is anyone aware of any command plugins that allow mel to interact with a mysql database?
Not any publicly available Maya plug-ins that I am aware of.


-If I do go with the standard route and use a python mysql connector (MySQLdb), are there any platform independant modules that do not need compiling?
sqlite3 is already included with python, no need to use any external packages (Unless they provide some extra functionality you need). It is natively included in python, so you wont need to worry about platform at all.

http://docs.python.org/library/sqlite3.html

Basic example
conn = sqlite3.connect('/tmp/example')
c = conn.cursor()

# Create table
c.execute('''create table stocks
(date text, trans text, symbol text,
qty real, price real)''')

# Insert a row of data
c.execute("""insert into stocks
values ('2006-01-05','BUY','RHAT',100,35.14)""")

# Save (commit) the changes
conn.commit()
# We can also close the cursor if we are done with it
c.close()

-If I do use python to perform a mysql query, how can I use the data returned in mel. For simplicity, say we have a python function that returns an array. Can i copy the array into mel? If so, how?
You have two main options here: Simply call your code using the mel command "python();" and capture the return results. Or, convert your python code for working with the sql database into a python scripted plugin and define an MPxCommand to interface with it.

red_oddity
10-16-2010, 07:51 PM
Yes, off course, if you don't access data on a sql server it is indeed much easier to use sqlite like Nate showed you (especially handy when the data is project specific and you want it included in your maya project directory, that way it gets included when you backup your maya project directory as well.)

We use MySQLdb simply because the data needs to be accessible from outside the company as well (through our client server web site)

tomViolet
11-05-2010, 12:49 PM
Thank you all for your replies, sqlite is definetly the best option for this and works great.

CGTalk Moderation
11-05-2010, 12:49 PM
This thread has been automatically closed as it remained inactive for 12 months. If you wish to continue the discussion, please create a new thread in the appropriate forum.