def delete(self): 'Deletes record from database' query = 'DELETE FROM %s WHERE %s = %s' % ( self.Meta.table_safe, self.Meta.pk, self.db.conn.placeholder) values = [getattr(self, self.Meta.pk)] Query.raw_sql(query, values, self.db) return True
def table_exists(db, table_name): """ Given an AutORM model, check to see if its table exists. """ try: s_sql = "SELECT * FROM %s LIMIT 1;" % table_name Query.raw_sql(s_sql, db=db) except Exception: return False # if no exception, the table exists and we are done return True
def table_exists(self): """ Given an AutORM model, check to see if its table exists. """ try: s_sql = "SELECT * FROM %s LIMIT 1;" % self.rclass.Meta.table_safe Query.raw_sql(s_sql, db=self.rclass.db) except Exception: return False # if no exception, the table exists and we are done return True
def __get__(self, instance, owner): super(OneToMany, self)._set_up(instance, owner) if not instance: return self.model if not self.field: self.field = '%s_id' % instance.Meta.table conditions = {self.field: getattr(instance, instance.Meta.pk)} return Query(model=self.model, conditions=conditions)
def __get__(self, instance, owner): super(ForeignKey, self)._set_up(instance, owner) if not instance: return self.model if not self.field: self.field = '%s_id' % self.model.Meta.table conditions = {self.model.Meta.pk: getattr(instance, self.field)} return Query(model=self.model, conditions=conditions)[0]
def _update(self): 'Uses SQL UPDATE to update record' query = 'UPDATE %s SET ' % self.Meta.table_safe query += ', '.join(['%s = %s' % (escape(f.name), self.db.conn.placeholder) for f in self._changed]) query += ' WHERE %s = %s ' % (escape(self.Meta.pk), self.db.conn.placeholder) values = [f.to_db(getattr(self, f.name)) for f in self._changed] values.append(self._get_pk()) cursor = Query.raw_sql(query, values, self.db)
def create_table(db, s_create_sql): """ Create a table for an AutORM class. """ Query.begin(db=db) Query.raw_sqlscript(s_create_sql, db=db) Query.commit(db=db)
def cursor(self): c = Query.get_cursor(self.rclass.db) def row_factory(q, row): o = self.rclass() for col,v in zip(q.description, row): if col[0] in o._fields: f = o._fields[o._fields.index(col[0])] setattr(o, f.name, f.to_python(v)) else: setattr(o, col[0], v) return o c.row_factory = row_factory return c
def _update(self): 'Uses SQL UPDATE to update record' query = 'UPDATE %s SET ' % self.Meta.table_safe query += ', '.join([ '%s = %s' % (escape(f.name), self.db.conn.placeholder) for f in self._changed ]) query += ' WHERE %s = %s ' % (escape( self.Meta.pk), self.db.conn.placeholder) values = [f.to_db(getattr(self, f.name)) for f in self._changed] values.append(self._get_pk()) cursor = Query.raw_sql(query, values, self.db)
def cursor(self): c = Query.get_cursor(self.rclass.db) def row_factory(q, row): o = self.rclass() for col, v in zip(q.description, row): if col[0] in o._fields: f = o._fields[o._fields.index(col[0])] setattr(o, f.name, f.to_python(v)) else: setattr(o, col[0], v) return o c.row_factory = row_factory return c
def _new_save(self): 'Uses SQL INSERT to create new record' # if pk field is set, we want to insert it too # if pk field is None, we want to auto-create it from lastrowid include_pk = True if self._get_pk() is not None else False if not hasattr(self.__class__, "_insert_stmt_cache_%s" % include_pk): fields = [ escape(f.name) for f in self._fields if f.name != self.Meta.pk or include_pk ] placeholders = [] for f in self._fields: if f.name == self.Meta.pk and not include_pk: continue if f.sql_type == 'GEOMETRY': placeholders.append("GeomFromText(%s, %d)" % (self.db.conn.placeholder, f.srid)) else: placeholders.append(self.db.conn.placeholder) query = 'INSERT INTO %s (%s) VALUES (%s)' % ( self.Meta.table_safe, ', '.join(fields), ', '.join(placeholders)) setattr(self.__class__, "_insert_stmt_cache_%s" % include_pk, query) else: query = getattr(self.__class__, "_insert_stmt_cache_%s" % include_pk) values = [ f.to_db(getattr(self, f.name, None)) for f in self._fields if f.name != self.Meta.pk or include_pk ] cursor = Query.raw_sql(query, values, self.db) if self._get_pk() is None: self._set_pk(cursor.lastrowid) return True
def _new_save(self): 'Uses SQL INSERT to create new record' # if pk field is set, we want to insert it too # if pk field is None, we want to auto-create it from lastrowid include_pk = True if self._get_pk() is not None else False if not hasattr(self.__class__, "_insert_stmt_cache_%s" % include_pk): fields=[ escape(f.name) for f in self._fields if f.name != self.Meta.pk or include_pk ] placeholders = [] for f in self._fields: if f.name == self.Meta.pk and not include_pk: continue if f.sql_type == 'GEOMETRY': placeholders.append("GeomFromText(%s, %d)" % (self.db.conn.placeholder, f.srid)) else: placeholders.append(self.db.conn.placeholder) query = 'INSERT INTO %s (%s) VALUES (%s)' % ( self.Meta.table_safe, ', '.join(fields), ', '.join(placeholders) ) setattr(self.__class__, "_insert_stmt_cache_%s" % include_pk, query) else: query = getattr(self.__class__, "_insert_stmt_cache_%s" % include_pk) values = [f.to_db(getattr(self, f.name, None)) for f in self._fields if f.name != self.Meta.pk or include_pk] cursor = Query.raw_sql(query, values, self.db) if self._get_pk() is None: self._set_pk(cursor.lastrowid) return True
def create_table(self): """ Create a table for an AutORM class. """ if hasattr(self.rclass.Meta, 'create_sql'): s_create_sql = self.rclass.Meta.create_sql else: fields = [] deferred = [] for f in self.rclass._fields: if f.creation_deferred: deferred.append(f.define(self.rclass.Meta.table)) else: fields.append(f.define()) s_create_sql = """CREATE TABLE %s (%s); %s;""" % (self.rclass.Meta.table_safe, ", ".join(fields), ";".join(deferred)) Query.begin(db=self.rclass.db) Query.raw_sqlscript(s_create_sql, db=self.rclass.db) Query.commit(db=self.rclass.db)
def create_table(self): """ Create a table for an AutORM class. """ if hasattr(self.rclass.Meta, 'create_sql'): s_create_sql = self.rclass.Meta.create_sql else: fields = [] deferred = [] for f in self.rclass._fields: if f.creation_deferred: deferred.append(f.define(self.rclass.Meta.table)) else: fields.append(f.define()) s_create_sql = """CREATE TABLE %s (%s); %s;""" % ( self.rclass.Meta.table_safe, ", ".join(fields), ";".join(deferred)) Query.begin(db=self.rclass.db) Query.raw_sqlscript(s_create_sql, db=self.rclass.db) Query.commit(db=self.rclass.db)
CREATE TABLE author ( id INTEGER PRIMARY KEY AUTOINCREMENT, first_name VARCHAR(40) NOT NULL, last_name VARCHAR(40) NOT NULL, bio TEXT ); CREATE TABLE books ( id INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR(255), author_id INT(11), json_data TEXT, FOREIGN KEY (author_id) REFERENCES author(id) ); """ #autorm_db.conn.connect('sqlite3', ':memory:') Query.raw_sqlscript(sqlite_create) class Author(Model): books = OneToMany('Book') class Meta: defaults = {'bio': 'No bio available'} validations = {'first_name': validators.Length(), 'last_name': (validators.Length(), lambda obj,x: x != 'BadGuy!')} fields = [IdField('id'), Field('first_name'), Field('last_name'), TextField('bio')] class Book(Model): author = ForeignKey(Author)
JSONField('some_json_data') ] autorm_db.conn.connect('sqlite3', ':memory:') # let's create a table for the model above: Author.objects.create_table() # This model's fields are derived via introspecting the database. So, you need # a connection and a table. Query.raw_sqlscript(""" DROP TABLE IF EXISTS books; CREATE TABLE books ( id INTEGER PRIMARY KEY autoincrement, title VARCHAR(255), other_json_data TEXT, author_id INT(11), FOREIGN KEY (author_id) REFERENCES author(id) ); """) class Book(Model): author = ForeignKey(Author) class Meta: table = 'books' # inspect the database to get field names, # use the default field type (no-op) for all the columns, except this one: field_overrides = [JSONField('other_json_data')]
def __new__(cls, name, bases, attrs): if name == 'Model': return super(ModelBase, cls).__new__(cls, name, bases, attrs) new_class = type.__new__(cls, name, bases, attrs) if not getattr(new_class, 'Meta', None): new_class.Meta = Empty if not getattr(new_class.Meta, 'table', None): new_class.Meta.table = name.lower() new_class.Meta.table_safe = escape(new_class.Meta.table) if not getattr(new_class.Meta, 'objects', None): setattr(new_class, 'objects', BaseManager()) else: setattr(new_class, 'objects', new_class.Meta.objects) new_class.objects.rclass = new_class # See cursor.description # http://www.python.org/dev/peps/pep-0249/ if not hasattr(new_class, "db"): new_class.db = autorm_db #db = new_class.db defaults = {} if not getattr(new_class.Meta, 'fields', None): q = Query.raw_sql('SELECT * FROM %s LIMIT 1' % new_class.Meta.table_safe, db=new_class.db) new_class._fields = [Field(f[0]) for f in q.description] for field in getattr(new_class.Meta, 'field_overrides', []): if field.name not in new_class._fields: raise Exception("No db column named %s in %s" % (field.name, new_class.table)) new_class._fields[new_class._fields.index(field.name)] = field new_class.Meta.fields = new_class._fields else: new_class._fields = getattr(new_class.Meta, 'fields') field_validations = getattr(new_class.Meta, 'validations', {}) field_map = {} for f in new_class._fields: field_map[f.name] = f validation = f.validators() if not validation: continue if f.name in field_validations: if type(field_validations[f.name]) == tuple: field_validations[f.name] = list(field_validations[f.name]) elif type(field_validations[f.name]) != list: field_validations[f.name] = [field_validations[f.name]] else: field_validations[f.name].extend(validation) else: field_validations[f.name] = validation # cache a map of the fields new_class.Meta.field_map = field_map # Create function to loop over iterable validations if len(field_validations): new_class.Meta.validations = field_validations for k, v in field_validations.iteritems(): if isinstance(v, (list, tuple)): new_class.Meta.validations[k] = ValidatorChain(*v) # TODO: fix, look at the fields # Assume id is the default if not getattr(new_class.Meta, 'pk', None): new_class.Meta.pk = 'id' cache.add(new_class) return new_class
def testmodel(self): # Create tables ### MYSQL ### # # DROP TABLE IF EXISTS author; # CREATE TABLE author ( # id INT(11) NOT NULL auto_increment, # first_name VARCHAR(40) NOT NULL, # last_name VARCHAR(40) NOT NULL, # bio TEXT, # PRIMARY KEY (id) # ); # DROP TABLE IF EXISTS books; # CREATE TABLE books ( # id INT(11) NOT NULL auto_increment, # title VARCHAR(255), # author_id INT(11), # FOREIGN KEY (author_id) REFERENCES author(id), # PRIMARY KEY (id) # ); ### SQLITE ### # sqlite_create = """ DROP TABLE IF EXISTS author; DROP TABLE IF EXISTS books; CREATE TABLE author ( id INTEGER PRIMARY KEY AUTOINCREMENT, first_name VARCHAR(40) NOT NULL, last_name VARCHAR(40) NOT NULL, bio TEXT, ); CREATE TABLE books ( id INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR(255), author_id INT(11), json_data TEXT, FOREIGN KEY (author_id) REFERENCES author(id) ); """ # autorm_db.conn.connect('sqlite3', ':memory:') # Query.raw_sql(sqlite_create) for table in ("author", "books"): Query.raw_sql("DELETE FROM %s" % escape(table)) # Test Creation assert Author.objects.query().count() == 0 james = Author(first_name="James", last_name="Joyce") james.save() assert Author.objects.query().count() == 1 kurt = Author(first_name="Kurt", last_name="Vonnegut") kurt.save() tom = Author(first_name="Tom", last_name="Robbins") tom.save() # print "Tom ID", tom.id Book(title="Ulysses", author_id=james.id).save() Book(title="Slaughter-House Five", author_id=kurt.id).save() Book(title="Jitterbug Perfume", author_id=tom.id).save() slww = Book(title="Still Life with Woodpecker", author_id=tom.id, json_data=["some", "data"]) slww.save() # print "JSON Data = '%s'" % Book.objects.get(slww.id).json_data self.assertEqual(Book.objects.get(slww.id).json_data[0], "some") # Test ForeignKey self.assertEqual(slww.author.first_name, "Tom") # Test OneToMany self.assertEqual(len(list(tom.books)), 2) kid = kurt.id del (james, kurt, tom, slww) # Test retrieval b = Book.objects.query(title="Ulysses")[0] a = Author.objects.get(b.author_id) self.assertEqual(a.id, b.author_id) a = Author.objects.query(id=b.id)[:] self.assert_(isinstance(a, list)) # Test update new_last_name = "Vonnegut, Jr." a = Author.objects.query(id=kid)[0] a.last_name = new_last_name a.save() a = Author.objects.get(kid) self.assertEqual(a.last_name, new_last_name) # Test count self.assertEqual(Author.objects.query().count(), 3) self.assertEqual(len(Book.objects.query()[1:4]), 3) # Test delete a.delete() self.assertEqual(Author.objects.query().count(), 2) # Test validation a = Author(first_name="", last_name="Ted") try: a.save() raise Exception("Validation not caught") except Model.ValidationError: pass # Test defaults a.first_name = "Bill and" a.save() self.assertEqual(a.bio, "No bio available") try: Author(first_name="I am a", last_name="BadGuy!").save() raise Exception("Validation not caught") except Model.ValidationError: pass
TextField('first_name', notnull=True), TextField('last_name', notnull=True), TextField('bio'), JSONField('some_json_data')] autorm_db.conn.connect('sqlite3', ':memory:') # let's create a table for the model above: Author.objects.create_table() # This model's fields are derived via introspecting the database. So, you need # a connection and a table. Query.raw_sqlscript(""" DROP TABLE IF EXISTS books; CREATE TABLE books ( id INTEGER PRIMARY KEY autoincrement, title VARCHAR(255), other_json_data TEXT, author_id INT(11), FOREIGN KEY (author_id) REFERENCES author(id) ); """) class Book(Model): author = ForeignKey(Author) class Meta: table = 'books' # inspect the database to get field names, # use the default field type (no-op) for all the columns, except this one: field_overrides = [JSONField('other_json_data')] # Now we can create, retrieve, update and delete entries in our database.
def delete(self): 'Deletes record from database' query = 'DELETE FROM %s WHERE %s = %s' % (self.Meta.table_safe, self.Meta.pk, self.db.conn.placeholder) values = [getattr(self, self.Meta.pk)] Query.raw_sql(query, values, self.db) return True
def query(self, **kwargs): 'Returns Query object' return Query(model=self.rclass, conditions=kwargs)
def testmodel(self): # Create tables ### MYSQL ### # # DROP TABLE IF EXISTS author; # CREATE TABLE author ( # id INT(11) NOT NULL auto_increment, # first_name VARCHAR(40) NOT NULL, # last_name VARCHAR(40) NOT NULL, # bio TEXT, # PRIMARY KEY (id) # ); # DROP TABLE IF EXISTS books; # CREATE TABLE books ( # id INT(11) NOT NULL auto_increment, # title VARCHAR(255), # author_id INT(11), # FOREIGN KEY (author_id) REFERENCES author(id), # PRIMARY KEY (id) # ); ### SQLITE ### # sqlite_create = """ DROP TABLE IF EXISTS author; DROP TABLE IF EXISTS books; CREATE TABLE author ( id INTEGER PRIMARY KEY AUTOINCREMENT, first_name VARCHAR(40) NOT NULL, last_name VARCHAR(40) NOT NULL, bio TEXT, ); CREATE TABLE books ( id INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR(255), author_id INT(11), json_data TEXT, FOREIGN KEY (author_id) REFERENCES author(id) ); """ #autorm_db.conn.connect('sqlite3', ':memory:') #Query.raw_sql(sqlite_create) for table in ('author', 'books'): Query.raw_sql('DELETE FROM %s' % escape(table)) # Test Creation assert Author.objects.query().count() == 0 james = Author(first_name='James', last_name='Joyce') james.save() assert Author.objects.query().count() == 1 kurt = Author(first_name='Kurt', last_name='Vonnegut') kurt.save() tom = Author(first_name='Tom', last_name='Robbins') tom.save() #print "Tom ID", tom.id Book(title='Ulysses', author_id=james.id).save() Book(title='Slaughter-House Five', author_id=kurt.id).save() Book(title='Jitterbug Perfume', author_id=tom.id).save() slww = Book(title='Still Life with Woodpecker', author_id=tom.id, json_data=['some', 'data']) slww.save() #print "JSON Data = '%s'" % Book.objects.get(slww.id).json_data self.assertEqual(Book.objects.get(slww.id).json_data[0], 'some') # Test ForeignKey self.assertEqual(slww.author.first_name, 'Tom') # Test OneToMany self.assertEqual(len(list(tom.books)), 2) kid = kurt.id del (james, kurt, tom, slww) # Test retrieval b = Book.objects.query(title='Ulysses')[0] a = Author.objects.get(b.author_id) self.assertEqual(a.id, b.author_id) a = Author.objects.query(id=b.id)[:] self.assert_(isinstance(a, list)) # Test update new_last_name = 'Vonnegut, Jr.' a = Author.objects.query(id=kid)[0] a.last_name = new_last_name a.save() a = Author.objects.get(kid) self.assertEqual(a.last_name, new_last_name) # Test count self.assertEqual(Author.objects.query().count(), 3) self.assertEqual(len(Book.objects.query()[1:4]), 3) # Test delete a.delete() self.assertEqual(Author.objects.query().count(), 2) # Test validation a = Author(first_name='', last_name='Ted') try: a.save() raise Exception('Validation not caught') except Model.ValidationError: pass # Test defaults a.first_name = 'Bill and' a.save() self.assertEqual(a.bio, 'No bio available') try: Author(first_name='I am a', last_name='BadGuy!').save() raise Exception('Validation not caught') except Model.ValidationError: pass
CREATE TABLE author ( id INTEGER PRIMARY KEY AUTOINCREMENT, first_name VARCHAR(40) NOT NULL, last_name VARCHAR(40) NOT NULL, bio TEXT ); CREATE TABLE books ( id INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR(255), author_id INT(11), json_data TEXT, FOREIGN KEY (author_id) REFERENCES author(id) ); """ #autorm_db.conn.connect('sqlite3', ':memory:') Query.raw_sqlscript(sqlite_create) class Author(Model): books = OneToMany('Book') class Meta: defaults = {'bio': 'No bio available'} validations = { 'first_name': validators.Length(), 'last_name': (validators.Length(), lambda obj, x: x != 'BadGuy!') } fields = [ IdField('id'), Field('first_name'),