Categories
Uncategorized

MySQL Node Matrix – Python

This will create either a directed or an undirected matrix on a MySQL data base.

The edges are written as tuples, like this

('[from node]','[to node]','[weight]')

Where the first item in the tuple is the from node, the second item is the to node, and the third item is the weight of the edge.

Here is the code in full

# import myqsl module
from MySQLdb import connect

# determine whether it will be a directed graph or not
dir_graph = False

# these are the edges
tup_edg = (('Jose','Shereena',1),
           ('Shereena','Shevaun',4),
           ('Rikee','Darren',3),
           ('Shevaun','Wanda',2))

# get the node names to a list
set_nodes = set()

# now go through each tuple to put names in set
for tup_each in tup_edg:
    set_nodes.add(tup_each[0])
    set_nodes.add(tup_each[1])

# connect to db_matrix
db_connect = connect(host='theDBHost',
                     user='theUser',
                     passwd='complexPassword',
                     db='db_matrix')

# get cursor
db_cursor = db_connect.cursor()

# create table
db_cursor.execute('CREATE TABLE `t_matrix` (`node` VARCHAR(60))')

for str_node in list(set_nodes):
    # create column with 0 as the default
    db_cursor.execute('ALTER TABLE `t_matrix` ADD %s INT DEFAULT 0' % str_node)
    # create row
    db_cursor.execute('INSERT INTO `t_matrix`(`node`) VALUES("%s")' % str_node)

# now add the links
for tup_each in tup_edg:
    # the row is the 'from' and column is the 'to'
    db_cursor.execute('UPDATE `t_matrix` SET `%s` = %d WHERE `node`="%s"'%
                        (tup_each[1],tup_each[2],tup_each[0]))
    # if it is not a directed graph, then it will copy the same info
    if dir_graph == False:
        db_cursor.execute('UPDATE `t_matrix` SET `%s` = %d WHERE `node`="%s"'%
                            (tup_each[0],tup_each[2],tup_each[1]))

# commit all changes as close connection
db_connect.commit()
db_connect.close()

That should return something that looks like this

+----------+------+-------+----------+--------+---------+-------+
| node     | Jose | Wanda | Shereena | Darren | Shevaun | Rikee |
+----------+------+-------+----------+--------+---------+-------+
| Jose     |    0 |     0 |        1 |      0 |       0 |     0 |
| Wanda    |    0 |     0 |        0 |      0 |       2 |     0 |
| Shereena |    1 |     0 |        0 |      0 |       4 |     0 |
| Darren   |    0 |     0 |        0 |      0 |       0 |     3 |
| Shevaun  |    0 |     2 |        4 |      0 |       0 |     0 |
| Rikee    |    0 |     0 |        0 |      3 |       0 |     0 |
+----------+------+-------+----------+--------+---------+-------+

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s