diff --git a/enum.py b/enum.py new file mode 100644 index 0000000..0eaf725 --- /dev/null +++ b/enum.py @@ -0,0 +1,7 @@ +# Pythonic way to do enums: +# http://stackoverflow.com/a/1695250/195722 +def enum(*sequential, **named): + enums = dict(zip(sequential, range(len(sequential))), **named) + reverse = dict((value, key) for key, value in enums.iteritems()) + enums['val'] = reverse + return type('Enum', (), enums) \ No newline at end of file diff --git a/example.py b/example.py new file mode 100644 index 0000000..ed1c6a2 --- /dev/null +++ b/example.py @@ -0,0 +1,24 @@ +from orm import MySQLModel + +class MyModel(MySQLModel): + def __init__(self): + self.connect("127.0.0.1", 3308, "test", "PASSWORD", "test2") + +class flavor(MyModel): + pass + +class color(MyModel): + pass + +class user(MyModel): + pass + _has_one = [color()] + #_has_many_to_many = [flavor()] + +x = user() +while x.select("username").select_related("color", ["color"]).get(): + print x.username +x.username = "adamsna" +x.save() +x.color.color = "green" +x.color.save() \ No newline at end of file diff --git a/orm.py b/orm.py new file mode 100644 index 0000000..f93d66a --- /dev/null +++ b/orm.py @@ -0,0 +1,367 @@ +from enum import enum +from pluralize import pluralize +import pymysql + +SQL_OPERATION = enum("SQL_SELECT", "SQL_UPDATE", "SQL_DELETE", "SQL_INSERT", "SQL_NULL") +BOOL_OPERATION = enum("OP_AND", "OP_OR", "OP_NULL") +GROUP_OPERATION = enum("LEFT_PARAN", "RIGHT_PARAN", "NULL_PARAM") + +class Model(object): + + _has_many = [] + _has_one = [] + _related = [] + _related_field = [] + + _operation = SQL_OPERATION.SQL_NULL + + + class condition: + operation = BOOL_OPERATION.OP_NULL + next_cond = None + group = False + clause = "" + def __init__(self, op, clause, group=GROUP_OPERATION.NULL_PARAM): + self.operation = op + self.clause = clause + self.group = group + self.clause = clause + + + + """ + users + flavors + user_flavors + + SELECT {FIELDS} FROM users + INNER JOIN user_flavors ON user_flavors.user_id = users.id + INNER JOIN flavors ON user_flavors.flavor_id = flavors.id + + SELECT {FIELDS} FROM self.tblname + INNER JOIN self.tblname_model.tblname ON self.tblname_model.tblname.self.tblname_id = self.tblname.id + INNER JOIN model.tblname ON self.tblname_model.tblname.model.tblname_id = model.tblname.id + """ + _has_many_to_many = [] + + + def select(self, field, fas=None): + return self + + def select_related(self, model, fields): + return self + + def where(self, field, value=None): + return self + + def or_where(self, field, value=None): + return self + + def where_in(self, field, value): + return self + + def or_where_in(self, field, value=None): + return self + + def where_not_in(self, field, value=None): + return self + + def or_where_not_in(self, field, value=None): + return self + + def from_array(self, POST, keys): + return self + + def like(self, field, value): + return self + + def or_like(self, field, value): + return self + + def not_like(self, field, value): + return self + + def or_not_like(self, field, value): + return self + + def ilike(self, field, value): + return self + + def or_ilike(self, field, value): + return self + + def not_ilike(self, field, value): + return self + + def or_not_ilike(self, field, value): + return self + + def limit(self, limit, start=0): + return self + + def order_by(self): + return self + + def select_max(self): + return self + + def select_min(self): + return self + + def select_avg(self): + return self + + def select_sum(self): + return self + + def distinct(self): + return self + + def group_by(self): + return self + + def having(self): + return self + + def where_related(self, model, field): + return self + + def save(self, force=False): + return [] + + def delete(self): + return [] + + def get(self): + return [] + + def query(self): + """ + This method should be overwritten in child classes + """ + raise NotImplementedError("Abstract method") + +class SQLModel(Model): + """ + @type self._where: Model.condition + """ + _select = {} + _where = None + _resulting_query = "" + _select_related = {} + def select(self, field, fas=None): + if fas: + self._select[field] = fas + else: + self._select[field] = field + return self + + def select_related(self, model, fields): + if self._select_related.has_key(model): + self._select_related[model].append(fields) + else: + self._select_related[model] = fields + return self + + + def _where_abs(self, field, value, op): + if value: + if type(value) == type(0): + value = str(value) + else: + value = "'" + value + "'" + if " " in field: + cond = Model.condition(op, field + " " + value) + else: + cond = Model.condition(op, field + " = " + value) + else: + cond = Model.condition(op, field) + if self._where: + next = self._where + while next: + if not next.next_cond: + break + next = next.next_cond + next.next_cond = cond + else: + self._where = cond + + def _build_where(self): + pointer = self._where + if pointer: + self._resulting_query += "WHERE " + while pointer: + if pointer.group is not None and pointer.group != GROUP_OPERATION.NULL_PARAM: + if pointer.group == GROUP_OPERATION.LEFT_PARAN: + self._resulting_query += "( " + elif pointer.group == GROUP_OPERATION.RIGHT_PARAN: + self._resulting_query += ") " + else: + self._resulting_query += pointer.clause + " " + if pointer.next_cond: + if pointer.operation == BOOL_OPERATION.OP_AND: + self._resulting_query += "AND " + elif pointer.operation == BOOL_OPERATION.OP_OR: + self._resulting_query += "OR " + + pointer = pointer.next_cond + + + def where(self, field, value=None): + self._where_abs(field, value, BOOL_OPERATION.OP_AND) + return self + + def where_in(self, field, value): + self._where_abs(field + " IN (" + ",".join([str(v) for v in value]) + ")", None, BOOL_OPERATION.OP_AND) + return self + + def or_where(self, field, value=None): + self._where_abs(field, value, BOOL_OPERATION.OP_OR) + return self + + def save(self, force=False): + props = dir(self) + tblname = pluralize(type(self).__name__) + propdict = {} + id = None + if type(self).__name__ + "_id" in props: + id = getattr(self, type(self).__name__ + "_id") + + for prop in self._select: + attr = getattr(self, prop) + if prop[0] == '_': + continue + propdict[prop] = getattr(self, prop) + + if id is not None: + self._resulting_query = "UPDATE " + tblname + " SET " + for k,v in propdict.iteritems(): + if type(v) == type(0): + self._resulting_query += k + " = " + v + ", " + elif type(v) == type(""): + self._resulting_query += k + " = '" + v + "', " + self._resulting_query = self._resulting_query.rstrip(', ') + " " + self._build_where() + if "WHERE" in self._resulting_query: + self._resulting_query += "AND " + tblname + ".id = " + str(id) + " " + else: + self._resulting_query += "WHERE " + tblname + ".id = " + str(id) + " " + else: + self._resulting_query = "INSERT INTO " + tblname + " " + self._resulting_query += "(" + for key in propdict.keys(): + self._resulting_query += key + "," + self._resulting_query = self._resulting_query.rstrip(', ') + " " + self._resulting_query += ") VALUES (" + for val in propdict.values(): + if type(val) == type(0): + self._resulting_query += str(val) + "," + else: + self._resulting_query += "'" + val + "'," + self._resulting_query = self._resulting_query.rstrip(', ') + " " + self._resulting_query += ")" + return self._resulting_query + + + def get(self): + + self._resulting_query = "SELECT " + self._resulting_query += pluralize(type(self).__name__) + ".id AS " + type(self).__name__ + "_id, " + for tbl in self._has_one: + self._resulting_query += pluralize(type(tbl).__name__) + ".id AS " + type(tbl).__name__ + "_id, " + if self._select: + for k,v in self._select.iteritems(): + if v: + self._resulting_query += "%s AS %s, " % (k, v) + else: + self._resulting_query += k + " " + for i in self._select_related: + self._resulting_query += i + ", " + self._resulting_query = self._resulting_query.strip(", ") + " " + else: + self._resulting_query += "* " + + self._resulting_query += "FROM " + pluralize(type(self).__name__) + " " + + for tbl in self._has_many: + tblname = pluralize(type(tbl).__name__) + single = type(tbl).__name__ + self._resulting_query += "INNER JOIN %s ON %s = %s" % (tblname, + tblname + "." + type(self).__name__ + "_id", + type(self).__name__ + ".id") + " " + for tbl in self._has_one: + tblname = pluralize(type(tbl).__name__) + single = type(tbl).__name__ + self._resulting_query += "INNER JOIN %s ON %s = %s" % (tblname, + pluralize(type(self).__name__) + "." + single + "_id", + tblname + ".id") + " " + for tbl in self._has_many_to_many: + jointbl = type(self).__name__ + "_" + pluralize(type(tbl).__name__) + thissingle = type(self).__name__ + thisplural = pluralize(type(self).__name__) + tblsingle = type(tbl).__name__ + tblplural = pluralize(type(tbl).__name__) + self._resulting_query += "INNER JOIN %s ON %s = %s" % (jointbl, + jointbl + "." + thissingle + "_id", + thisplural + ".id") + " " + self._resulting_query += "INNER JOIN %s ON %s = %s" % (tblplural, + jointbl + "." + tblsingle + "_id", + tblplural + ".id") + " " + + self._build_where() + + + return self._resulting_query + +class MySQLModel(SQLModel): + def connect(self, host='127.0.0.1', port=3306, user='root', passwd='', db='mysql'): + self.conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db) + self.res = None + + + def save(self, force=False): + query = super(MySQLModel, self).save(force) + c = self.conn.cursor() + c.execute(query) + self.conn.commit() + c.close() + + def get(self): + if not self.res: + query = super(MySQLModel, self).get() + self.c = self.conn.cursor(pymysql.cursors.DictCursor) + self.c.execute(query) + self.res = self.c.fetchone() + + if not self.res: + #self._select = {} + self._where = None + self.c.close() + return None + + if self._select == {}: + if self.res: + for k,v in self.res.iteritems(): + setattr(self, k, v) + res = self.c.fetchone() + else: + for i in self._select: + if i in self.res: + setattr(self, i, self.res[i]) + setattr(self, type(self).__name__ + "_id", self.res[type(self).__name__ + "_id"]) + for k,v in self._select_related.iteritems(): + for i in self._has_one: + if type(i).__name__ == k: + i._select = {} + setattr(self, type(i).__name__, i) + for val in v: + i._select[val] = val + setattr(i, val, self.res[val]) + setattr(i, type(i).__name__ + "_id", self.res[type(i).__name__ + "_id"]) + + return True + + + +class CSVModel(Model): + pass diff --git a/pluralize.py b/pluralize.py new file mode 100644 index 0000000..a9ec297 --- /dev/null +++ b/pluralize.py @@ -0,0 +1,95 @@ +# From http://code.activestate.com/recipes/577781-pluralize-word-convert-singular-word-to-its-plural/ + +ABERRANT_PLURAL_MAP = { + 'appendix': 'appendices', + 'barracks': 'barracks', + 'cactus': 'cacti', + 'child': 'children', + 'criterion': 'criteria', + 'deer': 'deer', + 'echo': 'echoes', + 'elf': 'elves', + 'embargo': 'embargoes', + 'focus': 'foci', + 'fungus': 'fungi', + 'goose': 'geese', + 'hero': 'heroes', + 'hoof': 'hooves', + 'index': 'indices', + 'knife': 'knives', + 'leaf': 'leaves', + 'life': 'lives', + 'man': 'men', + 'mouse': 'mice', + 'nucleus': 'nuclei', + 'person': 'people', + 'phenomenon': 'phenomena', + 'potato': 'potatoes', + 'self': 'selves', + 'syllabus': 'syllabi', + 'tomato': 'tomatoes', + 'torpedo': 'torpedoes', + 'veto': 'vetoes', + 'woman': 'women', + } + +VOWELS = set('aeiou') + +def pluralize(singular): + """Return plural form of given lowercase singular word (English only). Based on + ActiveState recipe http://code.activestate.com/recipes/413172/ + + >>> pluralize('') + '' + >>> pluralize('goose') + 'geese' + >>> pluralize('dolly') + 'dollies' + >>> pluralize('genius') + 'genii' + >>> pluralize('jones') + 'joneses' + >>> pluralize('pass') + 'passes' + >>> pluralize('zero') + 'zeros' + >>> pluralize('casino') + 'casinos' + >>> pluralize('hero') + 'heroes' + >>> pluralize('church') + 'churches' + >>> pluralize('x') + 'xs' + >>> pluralize('car') + 'cars' + + """ + if not singular: + return '' + plural = ABERRANT_PLURAL_MAP.get(singular) + if plural: + return plural + root = singular + try: + if singular[-1] == 'y' and singular[-2] not in VOWELS: + root = singular[:-1] + suffix = 'ies' + elif singular[-1] == 's': + if singular[-2] in VOWELS: + if singular[-3:] == 'ius': + root = singular[:-2] + suffix = 'i' + else: + root = singular[:-1] + suffix = 'ses' + else: + suffix = 'es' + elif singular[-2:] in ('ch', 'sh'): + suffix = 'es' + else: + suffix = 's' + except IndexError: + suffix = 's' + plural = root + suffix + return plural \ No newline at end of file diff --git a/sample.sql b/sample.sql new file mode 100644 index 0000000..f64e562 --- /dev/null +++ b/sample.sql @@ -0,0 +1,61 @@ +-- -------------------------------------------------------- +-- Host: 127.0.0.1 +-- Server version: 5.5.40-0+wheezy1-log - (Debian) +-- Server OS: debian-linux-gnu +-- HeidiSQL Version: 8.0.0.4396 +-- -------------------------------------------------------- + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET NAMES utf8 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; + +-- Dumping database structure for test2 +CREATE DATABASE IF NOT EXISTS `test2` /*!40100 DEFAULT CHARACTER SET latin1 */; +USE `test2`; + + +-- Dumping structure for table test2.colors +CREATE TABLE IF NOT EXISTS `colors` ( + `id` int(10) NOT NULL AUTO_INCREMENT, + `color` varchar(50) NOT NULL DEFAULT '0', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Data exporting was unselected. + + +-- Dumping structure for table test2.flavors +CREATE TABLE IF NOT EXISTS `flavors` ( + `id` int(10) NOT NULL AUTO_INCREMENT, + `flavor` varchar(50) NOT NULL DEFAULT '0', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Data exporting was unselected. + + +-- Dumping structure for table test2.users +CREATE TABLE IF NOT EXISTS `users` ( + `id` int(10) NOT NULL AUTO_INCREMENT, + `username` varchar(50) DEFAULT '0', + `admin` varchar(50) DEFAULT '0', + `color_id` int(11) DEFAULT '0', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Data exporting was unselected. + + +-- Dumping structure for table test2.user_flavors +CREATE TABLE IF NOT EXISTS `user_flavors` ( + `id` int(10) NOT NULL AUTO_INCREMENT, + `flavor_id` int(10) NOT NULL DEFAULT '0', + `user_id` int(10) NOT NULL DEFAULT '0', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Data exporting was unselected. +/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */; +/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;