Entity Component Systems

import sqlite3

conn=sqlite3.connect(":memory:")
if not conn:
    print('oh no!')
c=conn.cursor()

c.execute('CREATE TABLE entities (entityID integer primary key, desc text)')
c.execute('CREATE TABLE components (componentID integer primary key, name text not null, desc text, tableName text not null)')
c.execute('CREATE TABLE entityComponents (entityID int, componentID int, rowID int)')
c.execute('CREATE TABLE positions (rowID integer primary key, x real,y real)')
c.execute('CREATE TABLE physics (rowID integer primary key, vx real, vy real)')


t=(None,'Position','Location of entity','positions')
c.execute('INSERT INTO components (componentID,name,desc,tableName) VALUES(?,?,?,?)',t)
positionsID=c.lastrowid

t=(None,'Physics','Velocity of entity','physics')
c.execute('INSERT INTO components (componentID, name,desc,tableName) VALUES(?,?,?,?)',t)
physicsID=c.lastrowid

# Insert Island Entity
t=(None,'Island')
c.execute('INSERT INTO entities (entityID,desc) VALUES (?,?)',t)
id=c.lastrowid

# Insert Yacht Entity
t=(None,'Yacht')
c.execute('INSERT INTO entities (entityID,desc) VALUES (?,?)',t)
id=c.lastrowid

# Insert positions component for the last entity
t=(None,-5,5)
c.execute('INSERT INTO positions (rowID,x,y) VALUES (?,?,?)',t)
rowID=c.lastrowid
t=(id,positionsID,rowID)
c.execute('INSERT INTO entityComponents (entityID,componentID,rowID) VALUES (?,?,?)',t)

# Insert pysics component for the last entity
t=(None,-1,2)
c.execute('INSERT INTO physics (rowID,vx,vy) VALUES (?,?,?)',t)
rowID=c.lastrowid
t=(id,physicsID,rowID)
c.execute('INSERT INTO entityComponents (entityID,componentID,rowID) VALUES(?,?,?)',t)

# Print out summary
print('Entities++++++++')
for row in c.execute('SELECT * FROM entities'):
    print(row)
print('Components++++++')
for row in c.execute('SELECT * FROM components'):
    print(row)
print('Positions+++++++')
for row in c.execute('SELECT * FROM positions'):
    print(row)
print('Physics+++++++++')
for row in c.execute('SELECT * FROM physics'):
    print(row)
print('Data++++++++++++')
for row in c.execute('SELECT * FROM (((entityComponents left join entities on entities.entityID=entityComponents.entityID) left join components on entityComponents.componentID=components.componentID) left join positions on entityComponents.rowID=positions.rowID)'):
    print(row)