Use of SQLite in Python & Use of ORM & how to initialize DB through code

1. How to SQLite in Python

The following example uses SQLite data by concatenating SQL statements.

import sqlite3;del main():    dbpath="db\\test.db";    try:        conn=sqlite3.connect(self.dbpath);    except:        pass;    #  read sqlite3    cur=self.conn.cursor();    sql='Select user,pwd,sex,address,birth,comment from t_user';    try:        cur.execute(sql);    except:        pass;    res=cur.fetchone();    res=cur.fetchall();    '''    for line in res:    key1=res[0];    key2=res[1];    '''    cur.close();    self.conn.close();        #update insert sqlite3     try:        conn=sqlite3.connect(self.dbpath);    except:        pass;                    sql=("update t_user set address='"+str(address)+",birth='"+datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")         "' where user="+str(user))    try:        self.conn.execute(sql);        self.conn.commit();    except:        self.conn.close();        self.conn=sqlite3.connect(self.dbpath);    cur.close();    self.conn.close();

2. Use of sqlalchemy

Orm is an object relational model, which performs database operations. If you use concatenated SQL statements, it is very low, error-prone, and debugging is troublesome. Next we will introduce a better operation method, which is implemented through a python module called sqlalchemy. Specific URL: http://www.rmunn.com/sqlalchemy-tutorial/tutorial.html

Given a URL: http://docs.sqlalchemy.org/en/rel_0_7/core/tutorial.html

The following is an example on the URL above. You can run it after saving it as a file:

From sqlalchemy import * DB = create_engine ('sqlite: // tutorial. db') dB. echo = false # Try changing this to true and see what happens # metadata = boundmetadata (db) Metadata = metadata (db) Users = TABLE ('users', metadata, column ('user _ id', integer, primary_key = true), column ('name', string (40), column ('age', integer ), column ('Password', string),) users. create () I = users.insert(polici.exe cute (name = 'Mary ', age = 30, password='secret'polici.exe cute ({'name': 'john', 'age': 42 }, {'name': 'Susan ', 'age': 57}, {'name': 'cars', 'age': 33}) # If the table already exists, you can obtain the table object # users = TABLE ('users', metadata, autoload = true) S = users. select () rs = s.exe cute () Row = Rs. fetchone () print 'id: ', row [0] print 'name:', row ['name'] print 'Age: ', row. ageprint 'password: ', row [users. c. password] For row in RS: Print row. name, 'is ', row. age, 'ears old'

Through the above code, I believe everyone understands how the basic sqlalchemy is used.

3. How to initialize the database in the project

In a project, the best way is to create and check the database in the form of code at the beginning of the project. If the database does not exist, create the database. If the database lacks tables, create the table. Below is a code modified by a cool man. I commented on the Code:

From sqlalchemy import * global_table_list = ('t_ table1', (column ('C _ id', integer, sequence ('t_ table1_id_seq '), primary_key = true ), column ('C _ name', string (40), column ('C _ keyword', text), column ('C _ time', timestamp ), column ('C _ status', integer), column ('C _ comment', string (20),), ('t_ table2 ', (column ('C _ id', integer, sequence ('t_ table2_id_seq '), primary_key = true), column ('C _ type', integer ), column ('C _ sex', Boolean), column ('C _ province ', integer), column ('C _ region', integer ), column ('C _ Platform', string (40), column ('C _ url', string (100),), ('t_ table3 ', (column ('C _ rule_id ', integer), column ('C _ keyword', string (40), column ('C _ timeline ', string (20),),) def init_db (): DB = create_engine ('sqlite: // test. db') dB. echo = false # Try changing this to true and see what happens metadata = metadata (db) for (t_name, t_columns) in global_table_list: Try: cur_table = TABLE (t_name, metadata, autoload = true) doesn t: # The following sentence is equivalent to: cur_table = TABLE (t_name, metadata, t_columns) # apply indicates that the function is called. Here Table () is called () # The call form of apply is as follows: Apply (func_name, argS). The second parameter ARGs of apply is in the form of a list # therefore, Here ARGs is a list of parameters, which must be (arg1, arg2 ,...) call cur_table = Apply (table, (t_name, metadata) + t_columns) cur_table.create () If _ name _ = '_ main _': init_db ()

The following is a brief description: first, store the table name and structure in the list. The create_engine method can be used to determine whether a database exists. When a database file exists, the database is called, when the database does not exist, the database file is automatically created. Then, the list is traversed cyclically to check whether the table exists. If the table does not exist, it is created.

Here, we use a system function apply in Python, which can call the system function through apply. Apply (func, argS ). For example, fun_abc (str1, str2, str3 ). If you call apply, it is applied (fun_abc, (str1, str2, str3), that is, argS is a list :( str1, str2, str3 ).

Let me provide another example of my initial writing, which is very low-level:

From sqlalchemy import * def init_db (): DB = create_engine ('sqlite: // test. db') dB. echo = false # Try changing this to true and see what happens metadata = metadata (db) # detection table t_table1 flag_table1 = true try: Table1 = TABLE ('t_ table1', metadata, autoload = true) doesn t: flag_table1 = false if not flag_table1: # create t_table1 table Table1 = TABLE ('t_table1', metadata, column ('C _ id', integer, sequence ('t_ table1_id_seq '), primary_key = true), column ('C _ name', string (40), column ('C _ keyword', text ), column ('C _ time', timestamp), column ('C _ status', integer), column ('C _ comment ', string (20),) table1.create () # detection table t_table2 flag_table2 = true try: Table2 = TABLE ('t_ table2', metadata, autoload = true) T: flag_table2 = false if not flag_table2: # create t_table2 table Table2 = TABLE ('t_ table2', metadata, column ('C _ id', integer, sequence ('t_ table2_id_seq '), primary_key = true ), column ('C _ type', integer), column ('C _ sex', Boolean), column ('C _ province ', integer ), column ('C _ region', integer), column ('C _ platform ', string (40), column ('C _ url', string (100 )),) table2.create () # detection table t_table3 flag_table3 = true try: table3 = TABLE ('t_ table3 ', metadata, autoload = true) Partition T: flag_table3 = false if not flag_table3: # create t_table3 table table3 = TABLE ('t_table3', metadata, column ('C _ rule_id ', integer), column ('C _ keyword ', string (40), column ('C _ timeline ', string (20),) table3.create () If _ name _ =' _ main __': init_db ()

Through these two sections of code, I want to say: good code, we must combine code with the same structure to eliminate redundant code.