more sophisticated change for sqlalchemy. 0002

effyhuihui · effyhuihui · commit aac9f3e64dc3 · 2014-12-04T15:02:22.000-08:00
diff --git a/effy/0002/0002.py b/effy/0002/0002.py
@@ -23,9 +23,9 @@
 Step 1: 
 start a live connection with DBApi
 '''
-## activate echo option will print out actual SQL statement at each step.
+## activate echo option will print out actual SQL statement at each step, for debugging purposes
 ## to connect to postgresql, create_engine('dialect://username:password@local:port/DBname')
-db = create_engine('postgresql://username:password@localhost:5432/dbname', echo=True)
+db = create_engine('postgresql://username:password@localhost:5432/dbname', echo=False)
 
 '''
 Step 2:
@@ -63,9 +63,11 @@
 '''
 Step 3:
 define ORM 
-CAUION: STEP 3, 4, 5 CAN NOT EXCHANGE AMONGST EACH OTHER. THE SEQUENCE IS DEFINITE
+CAUION: STEP 3, 4, 6 CAN NOT EXCHANGE AMONGST EACH OTHER. THE SEQUENCE IS DEFINITE
+Here I opened session before instance creation, this is for the purpose of get the 
+max(id) in the codes table, in case there are already records in the table.
 '''
-class Codes(object):
+class Codes_Entity(object):
 	__table__ = "codes"
 	def __init__(self, id, code):
 		self.id = id
@@ -77,24 +79,35 @@ def __repr__(self):
 Step 4:
 map between object and relational table. 
 '''
-mapper(Codes, codes)
+mapper(Codes_Entity, codes)
 metadata.create_all(db)
 
 '''
 Step 5:
+start session. And query to get the max(id) in the Code table.
+'''
+sm = sessionmaker(bind=db, autocommit=False, expire_on_commit=True)
+session = scoped_session(sm)
+## the following query means:
+## select max(id) from codes;
+##.scalar() method get the actual data, otherwise it is just a sql statement
+## Note that in the func(), the Table is not the actual name of the table itself
+## in the db, rather, it is the mapped class entity!!!! <-------should be very careful
+max_id = session.query(func.max(Codes_Entity.id)).scalar()
+
+'''
+Step 6:
 Create instances of Codes class
 '''
 raw_codes = codeGen(200)
 code_list = []
 for i in range(len(raw_codes)):
-	code_list.append(Codes(i+1, raw_codes[i]))
+	code_list.append(Codes_Entity(max_id+1+i, raw_codes[i]))
 
 '''
-Step 6:
-start session. Add records and commit.
+Step 7:
+Add records and commit.
 '''
-sm = sessionmaker(bind=db, autocommit=False, expire_on_commit=True)
-session = scoped_session(sm)
 for code in code_list:
 	#add will stage all the transactions
 	session.add(code)
diff --git a/effy/0002/ActivationCodeGen.py b/effy/0002/ActivationCodeGen.py
@@ -8,7 +8,7 @@ def digit(raw):
 
 def codeGen(n):
 	codes_pool = []
-	for i in range(n-1):
+	for i in range(n):
 		code = ""
 		for i in range(10):
 			code += digit(codeSeedA)
