Save all bicolor numbers to MySQL
1, function
This code mainly saves all data of dichroic sphere to mysql database, and the total type of dichroic sphere is 17721088. The 8G I7 notebook of the author has been inserted for about 10 days. The following code contains two methods: Insert_SSQ and Insert_SSQ2. The former inserts all data in turn, and the latter starts from the maximum id+1. The latter is more practical.
This method basically realizes data storage in MySQL, but the efficiency is too slow. It can be considered to improve the efficiency of data insertion through table splitting, index closing, etc., which will be improved in the future.
2, code
# -*- coding:utf-8 -*-
import pymysql
import traceback
#######mysql driver
#pip install PyMySQL
#######
########## mysql usage start
'''
//In addition to instantiating a cursor object, the db connection object can also be used for operations such as commit(), rollback(), etc.
//Execution and return data of cursor object
//As mentioned in the above example, cursor can call execute to execute a certain SQL statement, or fetch one or fetch all to get the returned data. Next, take a look at the methods of cursor in detail:
callproc(procname[,args]) Call a procname Stored procedures for
close() Cursors also have close methods. After cursors are closed, they cannot move, let alone be closed fetch
execute(query[,args]) query It is a SQL String, args Is a sequence or map, which will be query Variable assignment in. about query The variable settings in the string are explained in detail below. The value returned by this method is the number of rows affected (such as query SQL It returns how many lines have been queried, adding or deleting SQL How many lines have been added or deleted)
executemany(query[,args]) This method and execute It's similar, but it's repeated several times execute，args It is also a "sequence of the same length sequence". Each execution corresponds the items in a sequence to query Of variables. allegedly executemany In terms of efficiency execute It is much higher, so it can be used in batch insert and batch update. It should be noted that this method is a whole. If you want to perform multiple query operations on this method, you can only get the last parameter constraint SQL Result set from
fetchone/fetchall() Get a row/All line results
fetchmany([size]) size It points out how many rows of data I want to get. If the number of rows that can be returned is less than the required number of rows, the smaller one will prevail.
nextset() Discard all result sets and jump directly to the next result set, if any. Return if no more result sets None，Otherwise return True，Next fetch The operation will return data from the new result set. The so-called result set is, for example, to execute two items in a row SQL If you do not call nextset，that fetch come fetch You can only get the result content of the first statement. After calling this, you can see the result content of the next statement.
rowcount This property represents the last time execute*Method, if yes-1 It means that the last returned result has no result set and the number of rows cannot be determined.
query Variable settings in string
query You can set variables in to dynamically generate some SQL Statement, which makes the operation more flexible. query Most of the variables in are used in query operations, because there is no uniform format, there are many ways to set variables. For example, it can be used?，Format strings, numbers, and so on. The following is unified in the form of format string.
//For example, "select Sno,Sname from Student where Sno=%s"
"select * from Client where level > %d and gid = %s"
//These variables can be specified in conjunction with the args parameter of the execute * method. For the execute method, because the SQL is executed only once, all its args only need one tuple (sequence), and each element of the tuple corresponds to the variable in the SQL string one by one. For the executemany method, args is a tuple (sequence) composed of a series of tuples like the above, which is equivalent to constraining the small tuples in the large tuples into SQL execution in a cycle
'''
########## mysql usage end
class MysqlClass():
db = None
host = 'localhost'
usr = 'root'
pwd = 'YourPwd'
dbname = 'Lottery'
port = 3306
charset = 'utf8'
# {"quote-currency": "btc", "base-currency": "eos", "symbol-partition": "main", "price-precision": 8, "amount-precision": 2}
def ShowVersion(self):
db = pymysql.connect(self.host, self.usr, self.pwd, self.dbname, self.port)
# Create a cursor object cursor using the cursor() method
cursor = db.cursor()
# Executing SQL queries using the execute() method
cursor.execute("SELECT VERSION()")
# Use the fetchone() method to get a single piece of data
data = cursor.fetchone()
print("Database version : %s " % data)
# Close database connection
db.close()
def OpenDB(self):
'''
//Open mysql:
'''
self.db = pymysql.connect(host = self.host, user = self.usr, passwd = self.pwd, db = self.dbname,charset = self.charset)
#print('Open MySQL!')
def CloseDB(self):
"""
//Close sql
"""
self.db.close()
#print('Close MySQL!')
def ExcuteSQL(self,str_sql):
self.OpenDB()
try:
cursor = self.db.cursor()
cursor.execute(str_sql)
cursor.close()
self.db.commit()
except:
self.db.rollback()
traceback.print_exc()
self.CloseDB()
def GetMaxId(self):
sql_1 = "select max(id) from SSQ"
maxnum = 0
try:
cursor = self.db.cursor()
cursor.execute(sql_1)
ret1 = cursor.fetchone()
maxnum = ret1[0]#Return to tupple
cursor.close()
except :
self.db.rollback()
traceback.print_exc()
return maxnum
def Insert_SSQ(self, data_dict):
''' Insert dichroic data '''
self.OpenDB()
num = 0
#q find the number of bum s
sql_1 = "select count(id) from SSQ"
try:
cursor = self.db.cursor()
cursor.execute(sql_1)
ret1 = cursor.fetchone()
num = ret1[0]#Return to tupple
cursor.close()
except :
self.db.rollback()
traceback.print_exc()
#Exclude the same content: check whether it is the same, if it is the same, do not insert (date and title)
if(num>0):
cursor = self.db.cursor()
sql_2 = 'select * from SSQ where t1=%d and t2=%d and t3=%d and t4=%d and t5=%d and t6=%d and t7=%d '%(data_dict['t1'],data_dict['t2'],data_dict['t3'],data_dict['t4'],data_dict['t5'],data_dict['t6'],data_dict['t7'])
cursor.execute(sql_2)
ret2 = cursor.fetchall()
cursor.close()
if(len(ret2)>0):
print('This data already exists!')
return
num += 1
#insert data
sql_3 = "INSERT INTO SSQ(id,t1,t2,t3,t4,t5,t6,t7) \
VALUES (%d,%d,%d,%d,%d,%d,%d,%d)"%(num,data_dict['t1'],data_dict['t2'],data_dict['t3'],data_dict['t4'],data_dict['t5'],data_dict['t6'],data_dict['t7'])
try:
# Execute sql statement
cursor = self.db.cursor()
cursor.execute(sql_3)
cursor.close()
# Commit to database execution
self.db.commit()
except:
# Rollback on error
print(data_dict)
self.db.rollback()
traceback.print_exc()
self.CloseDB()
def Insert_SSQ2(self, data_dict):
''' Insert dichroic data
//Insert from maximum num+1
'''
sql_3 = "INSERT INTO SSQ(id,t1,t2,t3,t4,t5,t6,t7) \
VALUES (%d,%d,%d,%d,%d,%d,%d,%d)"%(data_dict['id'],data_dict['t1'],data_dict['t2'],data_dict['t3'],data_dict['t4'],data_dict['t5'],data_dict['t6'],data_dict['t7'])
try:
# Execute sql statement
cursor = self.db.cursor()
cursor.execute(sql_3)
cursor.close()
# Commit to database execution
self.db.commit()
except:
# Rollback on error
print(data_dict)
self.db.rollback()
traceback.print_exc()
def GetAllSSQ_ToSQL():
count=0
sqltmp = MysqlClass()
sqltmp.OpenDB()
maxnum = sqltmp.GetMaxId()
for i1 in range(1,29):
for i2 in range(i1+1,30):
for i3 in range(i2+1,31):
for i4 in range(i3+1,32):
for i5 in range(i4+1,33):
for i6 in range(i5+1,34):
for i7 in range(1,17):
count+=1
if(count<=maxnum):
continue
ssq_dict={'id':count,'t1':i1,'t2':i2,'t3':i3,'t4':i4,'t5':i5,'t6':i6,'t7':i7}
sqltmp.Insert_SSQ2(ssq_dict)
sqltmp.CloseDB()
print("All SSQ:",count)
if __name__ == '__main__':
print('MySqlDBHelper!\n')
temp = MysqlClass()
######1. Display version number
#temp.ShowVersion()
######1,end
######2
GetAllSSQ_ToSQL()
######2 end
3, explain
The current test environment of this code is Python 3.5 and MySQL 5.7.13