[PD] Developing a python-mysqql-database-soundfile-player was: accessing mysql database

[Luigi Rensinghoff]

Thanks Frank in the meantime i found a workaround ;-) How was make-art ?
Since my python now works ;-) what about the python-puredatabase- 
project ;-)

Am 12.04.2007 um 18:34 schrieb Frank Barknecht:

> Hallo,
> Luigi Rensinghoff hat gesagt: // Luigi Rensinghoff wrote:
>
>> Yes i can acces the database from python, entering some lines of  
>> python:
>>
>> db = MySQLdb.connect("localhost", "root", "gigiroot", "sfx_database")
>> sql = """SELECT * FROM soundfile where description like '%birds%"""
>> cursor.execute(sql)
>> data = cursor.fetchall()
>> print data.
>>
>> but from PD it does not work
>
> Could you elaborate "does not work" a bit? Some debugging info or so?

What still does not work, but its not a problem since i "hardcoded"  
the connection setup to the server.
But sending the message

connect localhost root user pwd database(

gives me

could not log into MySQL Server.

defining the variables within the script does work.

mysql_host = "localhost"
mysql_user = "root"
mysql_pwd = "user"
mysql_db = "pwd"

The python-code is (Thanks to Georg Holzmann):
------------------------------------------------------------------------ 
---
# methods

def connect(host_="", user_="", db_name_="", pwd_=""):
	""" connect to MySQL server """
	global db
	global cursor
	try:
		db = MySQLdb.connect(host=str(host_), user=str(user_), passwd=str 
(pwd_), db=str(db_name_))
		cursor = db.cursor()
		print "PD mysql: logged into MySQL server !"
		return 1
	except:
		print "PD mysql: could not log into MySQL server !"
		return 0
------------------------------------------------------------------------ 
------------------------------------------------------------------------
Any Idea why ?

But actually for me it  doesnt matter, because i dont want to change  
to another database, so thats fine.

But some other questions came up, in the process, maybe someone can  
help me with this.

1) Is it in any way better to use "pyext" ? as far as i understand,  
you just need a different syntax to produce the same result ?
     Frank, why you use sqlite ? Is that better than mysql ?

2) I am not so familiar with mysql and also not lists in puredata, so  
what i am wondering is:
python gives out a list, and its not indexed. So i store the python- 
output list in a "lister" object.
If i want to adress "one" single line out of the python-result list,  
how do i do that ?
My workaround is "list-dripslow", but that way i always have to send  
a "bang" which acts like a "next".
There should be a direct way...."route" is not good for that since i  
never know how many results i get.

3) This is actually "mysql"-syntax and sort off OFF-Topic, but maybe  
someone can help me anyway.
I want to submit more complex queries. They should be like:
For example:
Give out the results, that contain rain, thunder, and heavy in the  
first filed. They should also contain collection1 and collection2 in  
the second field, but all results that contain "train" in the first  
filed and contain collection3 or collection 4 should be suppressed.

Is that clear ?

So i am sending this sort of message to "py mysql.py":

"SELECT description FROM sfx_files where description
like '%\$1%' and description like '%\$2%' and description like '%\$3%'
and description not like '%\$4%' and description not like '%\$5%' and
description not like '%\$6%' and description not like '%\$7%' and  
description
not like '%\$8%' and (path like '%\$9%' and path like '%\$10%') and
path not like '%\$11%' and path not like '%\$12%' and path not like
'%\$13%';"

That looks pretty complicated and i am actually not sure if it  
produces the right result.
I am also not sure how i can send "wildcards" that way.....

So i guess there is a more elegant way to put that into the python  
script or how to construct the query for MySQL, but for that i know  
too little of python and MySQL

4) I send the result to a datastructure, so i can visualize them. I  
am still searching for a way to automatically create/delete "lines"  
in the pd-data-window. By the way: is it possible to use another font  
in "drawsymbol" ???

5) Is there an alternative to "symbol" for the "text-input" ???

I attached the whole stuff, its still really rough and "spaghetti"- 
ish. But if anyone wants to have a look at it.

I could need some help, as you might see i am trying to do something  
complex with limited knowledge. But its fun ;-) and i already have  
something that works (more or less)

Best wishes to everyone who actually read this little "dveloper"- 
blog ;-)

Bye Luigi

>
> Attached is a very stripped down example how to access a db from
> pyext. I use sqlite as a db-backend in the code, the file is hardcoded
> to "/tmp/pypd.db". Change as wanted.
>
> You probably want to initialize the db with commands like this:
>
> BEGIN TRANSACTION;
> create table soundfile (description text);
> INSERT INTO soundfile VALUES('abc');
> INSERT INTO soundfile VALUES('mybirds');
> INSERT INTO soundfile VALUES('songbirds');
> INSERT INTO soundfile VALUES('birdsong');
> COMMIT;
>
> Beware: the code isn't very effective. For example normally you should
> not slurp in the whole result set in one go etc.
>
> Ciao
> -- 
>  Frank Barknecht                 _ ______footils.org_ __goto10.org__
>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: simple_sfx_player.zip
Type: application/zip
Size: 194463 bytes
Desc: not available
URL: <http://lists.puredata.info/pipermail/pd-list/attachments/20070414/265e8bd6/attachment.zip>
-------------- next part --------------