Thursday, September 16, 2010

MySQL Database scheme

I've decided to work with utf-8 encoding instead of cp1251.
First, set it:

SET names 'utf8';
DROP TABLE IF EXISTS weights;

DROP TABLE IF EXISTS cues;
DROP TABLE IF EXISTS reacts;

 CREATE TABLE cues
       (
         id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         data VARCHAR(50) collate  utf8_general_ci
       )

CREATE TABLE reacts
       (
         id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         data VARCHAR(100) collate utf8_general_ci
       )

CREATE TABLE weights
       (
         cue_id INT NOT NULL,
         react_id INT NOT NULL,
     weight DOUBLE NOT NULL,
     FOREIGN KEY (cue_id) REFERENCES cues(id),
     FOREIGN KEY (react_id) REFERENCES reacts(id),
     PRIMARY KEY(cue_id, react_id)
       )


Python:
# -*- coding: utf-8 -*- 
import MySQLdb


##connection:
conn = MySQLdb.connect (host = "localhost",
                        user = "user1",
                         passwd = "pass1",
                         db = "db_name")

cursor = conn.cursor ()
cursor.execute ("SET names 'utf8'")
....
cursor.execute ("SELECT id FROM cues WHERE data="+"\""+cue+"\"")   
    cue_id=cursor.fetchone ()[0]

..
cursor.close()
conn.close()

No comments:

Post a Comment