BGE and MYSQL database help,

Hello, who can help with connecting to an Mysql database in blender game engine 2.59?

like writing/reading data.

Interesting question. I would like to know that as well.

Yep Im very interested in this as well. Unfortunately I dont have the knowledge to share with you, but I’m sure the solution will be a fair amount of python coding. Is it possible to use external python libraries with the game engine? If so, then I’m sure there will be quite a few python <-> mysql libraries that will have an API to make things easier for you.

I think quite a few people would benefit from a fairly comprehensive answer here, or a link to a tutorial or something. This has been something I have been thinking about for a while now.

Um what is MYSQL? .

OK, I made a fairly serious attempt at this but started running into problems so I want to step back and make sure that I am doing it the right way before I sit there and painstakingly resolve the issues.

My basic idea is this:

  1. Install MySQL
  2. Set up the Database
  3. Build the Python lib MySQLdb using Python3.2
  4. Allow Blender’s sys.path to point to the MySQLdb libs
  5. Succeed.

Where I found trouble was step 3.

It seems that MySQLdb is not ready for Python3.2. When you try to run the setup.py it complains about missing “setuptools”. No big deal, install them, right?

I downloaded it from http://pypi.python.org/pypi/setuptools#files and then tried run setuptools’ setup.py, but got an error about “execfile” on setup.py line 7. I did some research and found the Python 3.0 release notes that said: “Removed execfile(). Instead of execfile(fn) use exec(open(fn).read()).”

So I thought, sure… one little change… and I edited the setup.py to use that new syntax, saying: exec(open(convert_path(‘setuptools/command/init.py’)).read(), d)

Then I ran again and it said “no module named dist”. This dist.py is under setuptools/ in the current dir, so I peppered in the following to setuptools/extension.py:
import sys
sys.path.append(‘setuptools’)

Then I ran into an error on setuptools/dist.py line 861, and changed it to:
except ValueError as e:

Then on setuptools/depends.py line 106, I had to change 65536L to 65536. Not sure if that’s equivalent but at least it compiles.

Then it failed again and I decided to see if I’m even on the right track.

Do any of you have a different direction?

-rking

You might want to do a search here and in the resource forum. As far as I remember there were a few threads dealing with database connections. Especially in the context of multi-player games.

Sorry I can’t help further

here is my sqlite3 class, its not mysql but it should be similar…
I’m still in developing that and it is a very minimalistic implementation…



import bge 
import sqlite3 
import os 
sep = os.sep 
G = bge.logic 
 
class SQL(): 
    def __init__(self, path): 
        """ initialise the db object, get Cursor, Connection """ 
        pass
    def connect(self, path):
        self.db = sqlite3.connect(path)
        self.cursor = sqlite3.Cursor(self.db)
    def createDatabase(self,path):
        self.db = sqlite3.connect(path)
    def connectDatabase(self):
        self.cursor = sqlite3.Cursor(self.db)

    def closeDatabase(self):
        self.db.close()
    def createTable(self, tablename, rows):
        try:
            self.cursor.execute("CREATE TABLE "+tablename+" "+rows)
        except sqlite3.OperationalError:
            print ("Error: table already exist, cannot create table!")
    def record(self,tablename, rows, id_):
        for i in rows:
            qstring = "UPDATE "+str(tablename)+" SET "+i+ " = '"+str(rows[i])+"' "#WHERE id = "+str(id_)
            print (qstring)
            self.cursor.execute(str(qstring))
        self.db.commit()
        #self.db.close()
    def get_fromSQL(self, tablename, colums):
        d = {}
        for i in colums:
            v = self.cursor.execute("SELECT "+i+" FROM "+tablename).fetchone()
            d[i] = v
        self.db.close()
        return d
    def sql_value(self,db, table, column):
        print ("OKOKOK", db, table, column)
        """
        conn = self.connect(db)
        """
        v = self.cursor.execute("SELECT "+column+" FROM "+table).fetchone()
        return v[0]

    def get_tables(self):
        """ get all tables as a list from the database 'self.tables' """

        t = self.db.execute("""
        SELECT name FROM sqlite_master
        WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
        UNION ALL
        SELECT name FROM sqlite_temp_master
        WHERE type IN ('table','view')
        ORDER BY 1
        """).fetchall()
        print (t)
        return [i[0] for i in t]
    def get_rows(self, table):
        r = self.cursor.execute("PRAGMA table_info(" +table+ ")")
        return [i[1] for i in r.fetchall()]
    def get_db_as_dict(self, path):
        self.db = sqlite3.connect(path)
        self.cursor = sqlite3.Cursor(self.db)
        tables = self.get_tables()
        dict_ ={}

        for i in range(0,len(tables)):
            # get all columns
            r = self.get_rows(tables[i])
            dict_[tables[i]] = {}
            # append columns to dict, should look like {db:{table:{colum1:1, colum2:2,...}}}
            for ri in r:
                qstr = "select "+ ri +" from "+tables[i]
                value = self.cursor.execute(qstr).fetchall()[0][0]
                dict_[tables[i]][ri] = value
        print ("dict_", dict_)
        return dict_

    def sql_as_dict(self,db):
        path = G.expandPath(sep+sep+db)
        self.db = sqlite3.connect(path)
        self.cursor = sqlite3.Cursor(self.db)

        print ("____________________________________________________________")
        print ("sql_as_dict(db):", db)
        print ()
        print ("SQL_DB:",self.db)
        print ()
        #return ("OK")

        # get Tables
        tables = self.get_tables()
        print ("tables:", tables)
        print ()

        # create a Dict from sql
        if not hasattr(self, 'sqlDict'):
            self.sqlDict = {}

        # append Table name
        self.sqlDict[db] = {}
        for i in range(0,len(tables)):

            # get all columns
            r = self.get_rows(tables[i])
            self.sqlDict[db][tables[i]] = {}

            # append columns to dict, should look like {db:{table:{colum1:1, colum2:2,...}}}
            for ri in r:
                qstr = "select "+ ri +" from "+tables[i]
                value = self.cursor.execute(qstr).fetchall()[0][0]
                self.sqlDict[db][tables[i]][ri] = value

        #print ("sql:", self.sqlDict)
        return self.sqlDict
        self.db.close()
    def execQuery(self, Query):
        try:
            r = self.db.execute(Query)
        except:
            r = self.cursor.execute(Query)
        finally:
            pass

        return r
        self.db.close() 
#def main(cont): 
#    own = cont.owner
#    if cont.sensors[0].positive:
#        # get the path to the database
#        path = G.expandPath(sep+sep+"db2.db")
#
#        # create a database object in namespace 
#        obj = SQL(path)
#
#        # ecxecute functions
#        obj.createDatabase(path)
#        obj.connectDatabase()
#        obj.createTable("TESTTABLE", "(id INTEGER PRIMARY KEY, Player TEXT, Planet TEXT, Sector TEXT, Objects TEXT)")
#        obj.record("TESTTABLE", {'Player':'Sevi', 'Planet':'Moon','Sector':'East', 'Objects':test},1)
#        obj.closeDatabase() 
#

hey sevi this sounds like a good alternative, keep us updated please.

levon_ from #blendercoders nailed it!!

https://launchpad.net/myconnpy <-- This lib works with Python 3.2 out of the box.

If anyone tries it and can’t get the connection working let me know and I’ll look closer.

-rking

this could open the idea of an online game using blender,…

i found the solution at that time, i can write a tutorial for this when i have some spare time next week maybe…

It is very possible to use MySQL with Python 3.2. Our BGE project currently uses the MySQLdb library to store our data on an XAMPP MySQL server. Unfortunately I can’t link any code, but I hope that this will provide more faith in the concept :slight_smile:

As stated above, this is an entirely Python/MySQL adventure so don’t include “Blender” in your Google searches.

you can find the tutorial in my blog

<b>
# created by octopus4 ,, www.octopus4.blogspot.com

import os
import GameLogic as G

cont = G.getCurrentController()
own = cont.owner

######## dynamic library import
try:
    import sqlite3
except:
    from pysqlite2 import dbapi2 as sqlite3

######## dynamic path
path = "/Volumes/usbmemory/user_data.db" # this path is for macintosh, change it to windows if you need to.

######## the connection and cursor vars
conn = sqlite3.connect(path)
c = conn.cursor()

######## dynamic table name
user_table = "users"

######## if there is no table at all, create one
if not os.path.exists(path):
    c.execute("create table "+user_table+" (id INTEGER PRIMARY KEY AUTOINCREMENT, name text,gender text,age real,mass real)")
else:
    sqlite3.connect(path)

try:
    c.execute("create table " +user_table+" (id INTEGER PRIMARY KEY AUTOINCREMENT, name text,gender text,age real,mass real)")
except (sqlite3.OperationalError):
    print ("table already exists&gt;&gt;")

######## if there is no row at all, create one

c.execute("SELECT Count(*) FROM "+user_table)
rows_no = c.fetchone()[0]

if rows_no == 0:
    c.execute("insert into "+user_table+" (name, gender, age, mass) VALUES('dave','male',32,96)")

######## to check for the number of rows in a database

c.execute("SELECT Count(*) FROM "+user_table)
print("number of rows :",c.fetchone()[0])

######## fill entries into variables

c.execute("SELECT * FROM "+str(user_table))
gender = (c.fetchall()[-1][2])

c.execute("SELECT * FROM "+str(user_table))
name = (c.fetchall()[-1][1])

c.execute("SELECT * FROM "+str(user_table))
age = (c.fetchall()[-1][3])

c.execute("SELECT * FROM "+str(user_table))
mass = (c.fetchall()[-1][4])

c.execute("SELECT * FROM "+str(user_table))
the_row = (c.fetchall())

# input from user # changed from the entry into text objects
input_name = 'grandma'
input_gender = 'female'
input_age = 88
input_mass = 65

# change stuff
if cont.sensors[1].positive:
    c.execute("UPDATE " +user_table+ " SET   name = "+" ' "+   input_name+" ' "+" WHERE id = 1")
    c.execute("UPDATE " +user_table+ " SET gender = "+" ' "+ input_gender+" ' "+" WHERE id = 1")
    c.execute("UPDATE " +user_table+ " SET    age = "+str(input_age)+" WHERE id = 1")
    c.execute("UPDATE " +user_table+ " SET    mass = "+str(input_mass)+" WHERE id = 1")

# report changes
if cont.sensors[0].positive:
    print("hey "+str(name))
    print(the_row)
    own["name_"] = name
    own["gender_"] = gender
    own["age_"] = age
    own["mass_"] = mass
   
conn.commit()
c.close()
conn.close()</b>

@ Octopus4:
Your code looks good for local database-connections, but not for online-MySQL-Databases.

My current Game- Project is based completely on Database-connection, but only local ones.
So my system uses the same connection like yours to a database via the sqlite3 library.

Don’t use a MySQL connection through the internet for games. Use HTTP to talk to a webserver and let the webserver communicate to MySQL. Using a REST API and json you’ll never run in trouble with firewalls and http is also more resilient. You won’t even need any extra python modules in Blender.

Your code looks good for local database-connections, but not for online-MySQL-Databases.

thanks, what does it need to cover that?

It needs the following:

If you can manage to get Mysql work with c++, or python, then you are ready to build your own spaceship and discover new planets.
Use sqlite3 instead or some json serialisation or maybe xml files, far better than wasting your life with mysql.

If you really need an sql databse, then use postgresql wich you can download for free, and it works after install not like mysql wich always needs aditional connector. Only managed to use mysql with php and java.
Some developpers do not care of the end user and deliver libraries as it is, compile it yourself, make your own tools.

Mysql only good for the web.

there are hundred other ways to store data than sql database. You could even write your own binary files if the data is going to be stored locally.
And if you are interested in databases you can take a look at non sql databases :

Take a look at google’s bigtable http://fr.wikipedia.org/wiki/BigTable.

Mysql only good for the web.

Not true. Every solution has its place. For us, we needed a system that had better corruption resistance and could reliably handle many users at the same time. JSON serialization was failing on over-loaded client networks and causing loss of data. Perhaps OP has a perfectly valid reason for exploring the MySQL path.