1) python
1.py
#!/usr/bin/python import sqlite3 #open db conn = sqlite3.connect('test.db') print "Opened database successfully"; c = conn.cursor() #create table try: c.execute('''CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);''') except sqlite3.OperationalError as e: #sqlite3.OperationalError: table COMPANY already exists print "table COMPANY already exists"; print "Table created successfully"; #insert data c.execute("INSERT OR REPLACE INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \ VALUES (1, 'Paul', 32, 'California', 20000.00 )"); c.execute("INSERT OR REPLACE INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \ VALUES (2, 'Allen', 25, 'Texas', 15000.00 )"); c.execute("INSERT OR REPLACE INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \ VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )"); c.execute("INSERT OR REPLACE INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \ VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )"); print "Records created successfully"; #read db cursor = c.execute("SELECT id, name, address, salary from COMPANY") for row in cursor: print "ID = ", row[0] print "NAME = ", row[1] print "ADDRESS = ", row[2] print "SALARY = ", row[3], "\n" conn.commit() print "Operation done successfully"; conn.close()
2.py
# By Vamei import sqlite3 # test.db is a file in the working directory. conn = sqlite3.connect("test1.db") c = conn.cursor() # create tables try: c.execute('''CREATE TABLE category (id int primary key, sort int, name text)''') c.execute('''CREATE TABLE book (id int primary key, sort int, name text, price real, category int, FOREIGN KEY (category) REFERENCES category(id))''') except sqlite3.OperationalError as e: #sqlite3.OperationalError: table COMPANY already exists print "table COMPANY already exists"; #insert books = [(1, 1, 'Cook Recipe', 3.12, 1), (2, 3, 'Python Intro', 17.5, 2), (3, 2, 'OS Intro', 13.6, 2), ] # execute "INSERT" c.execute("INSERT OR REPLACE INTO category VALUES (1, 1, 'kitchen')") # using the placeholder #c.execute("INSERT OR REPLACE INTO category VALUES (?, ?, ?)", [(2, 2, 'computer')]) c.execute("INSERT OR REPLACE INTO category VALUES (?, ?, ?)", (2, 2, 'computer')) # execute multiple commands c.executemany('INSERT OR REPLACE INTO book VALUES (?, ?, ?, ?, ?)', books) # retrieve one record c.execute('SELECT name FROM category ORDER BY sort') print(c.fetchone()) print(c.fetchone()) # retrieve all records as a list c.execute('SELECT * FROM book WHERE book.category=1') print(c.fetchall()) # iterate through the records for row in c.execute('SELECT name, price FROM book ORDER BY sort'): print(row) # save the changes conn.commit() # close the connection with the database conn.close()
3、综合demo
#!/usr/bin/python import sqlite3 test = { 'dev_id': 'z1', 'ver': '0.2.1', 'time': '1970-1-1 0:0:0', 'reg_code': '13210000', 'ip': '', 'port': '', 'user_role': '3', 'run_status': '0', 'warn_status': '0', 'battery': '4.065947', '4g': '46', 'reg_remainday': '73', 'local_ip': '10.4.37.164', 'charge': '1', 'usb': '1', 'location': '1', 'cros_mode': '1', 'cros_net_type': '0', 'vrs_ip': '222.35.95.xx', 'vrs_port': '2101', 'zhd_ip': '202.96.185.yy', 'zhd_port': '9000', 'data_usage': '0', 'lon': '0.000000', 'lat': '0.000000' } def dump(data): #open db conn = sqlite3.connect('test.db') print "Opened database successfully"; c = conn.cursor() #create table try: c.execute('''CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL, NET_SIGNAL TEXT NOT NULL, LOCAL_IP TEXT NOT NULL, CHARGE TEXT NOT NULL, USB TEXT NOT NULL, LOCATION TEXT NOT NULL, CROS_NET_TYPE TEXT NOT NULL, VRS_IP TEXT NOT NULL, VRS_PORT TEXT NOT NULL, ZHD_IP TEXT NOT NULL, ZHD_PORT TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), BATTERY TEXT NOT NULL, DEV_ID TEXT NOT NULL, VER TEXT NOT NULL, REG_CODE TEXT NOT NULL, REG_RMDAY TEXT NOT NULL, IP TEXT NOT NULL, PORT TEXT NOT NULL, USER_ROLE TEXT NOT NULL, RUN_STATUS TEXT NOT NULL, WARN_STATUS TEXT NOT NULL, LON TEXT NOT NULL, LAT TEXT NOT NULL, SALARY REAL);''') except sqlite3.OperationalError as e: print "table COMPANY already exists"; print "Table created successfully"; _dev_id = data["dev_id"] _ver = data["ver"] _time = data["time"] _reg_code = data["reg_code"] _ip = data["ip"] _port = data["port"] _user_role = data["user_role"] _run_status = data["run_status"] _warn_status = data["warn_status"] _battery = data["battery"] _net_signal = data["4g"] _reg_rmday = data["reg_remainday"] _local_ip = data["local_ip"] _charge = data["charge"] _usb = data["usb"] _location = data["location"] _cros_mode = data["cros_mode"] _cros_net_type = data["cros_net_type"] _vrs_ip = data["vrs_ip"] _vrs_port = data["vrs_port"] _zhd_ip = data["zhd_ip"] _zhd_port = data["zhd_port"] _data_usage = data["data_usage"] _lon = data["lon"] _lat = data["lat"] _age = 25 _address = 'California' _salary = 20000.00 t = [5,_net_signal, _local_ip, _charge, _usb,_location, _cros_net_type, _vrs_ip, _vrs_port, _zhd_ip, _zhd_port, _age,_address, _battery, _dev_id, _ver, _reg_code, _reg_rmday, _ip, _port, _user_role, _run_status, _warn_status, _lon,_lat,_salary] #insert data c.execute("INSERT OR REPLACE INTO COMPANY (ID,NET_SIGNAL,LOCAL_IP,CHARGE,USB,LOCATION,\ CROS_NET_TYPE,VRS_IP,VRS_PORT,ZHD_IP,ZHD_PORT,AGE,ADDRESS,BATTERY,DEV_ID,VER,\ REG_CODE,REG_RMDAY,IP,PORT,USER_ROLE,RUN_STATUS,WARN_STATUS,LON,LAT,SALARY) \ VALUES (1, '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 32, 'California',\ 'battery', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9','10', 20000.00 )"); c.execute("INSERT OR REPLACE INTO COMPANY (ID,NET_SIGNAL,LOCAL_IP,CHARGE,USB,LOCATION,\ CROS_NET_TYPE,VRS_IP,VRS_PORT,ZHD_IP,ZHD_PORT,AGE,ADDRESS,BATTERY,DEV_ID,VER,\ REG_CODE,REG_RMDAY,IP,PORT,USER_ROLE,RUN_STATUS,WARN_STATUS,LON,LAT,SALARY) \ VALUES (2, '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 25, 'Texas',\ 'battery', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9','10', 15000.00 )"); c.execute("INSERT OR REPLACE INTO COMPANY (ID,NET_SIGNAL,LOCAL_IP,CHARGE,USB,LOCATION,\ CROS_NET_TYPE,VRS_IP,VRS_PORT,ZHD_IP,ZHD_PORT,AGE,ADDRESS,BATTERY,DEV_ID,VER,\ REG_CODE,REG_RMDAY,IP,PORT,USER_ROLE,RUN_STATUS,WARN_STATUS,LON,LAT,SALARY) \ VALUES (3, '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 23, 'Norway',\ 'battery', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9','10', 20000.00 )"); c.execute("INSERT OR REPLACE INTO COMPANY (ID,NET_SIGNAL,LOCAL_IP,CHARGE,USB,LOCATION,\ CROS_NET_TYPE,VRS_IP,VRS_PORT,ZHD_IP,ZHD_PORT,AGE,ADDRESS,BATTERY,DEV_ID,VER,\ REG_CODE,REG_RMDAY,IP,PORT,USER_ROLE,RUN_STATUS,WARN_STATUS,LON,LAT,SALARY) \ VALUES (4,'0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 25, 'Rich-Mond ',\ 'battery', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9','10', 65000.00 )"); c.execute('INSERT OR REPLACE INTO COMPANY VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',t) print "Records created successfully"; #read db cursor = c.execute("SELECT id,net_signal, local_ip, charge, usb,location, \ cros_net_type, vrs_ip, vrs_port, zhd_ip, zhd_port, age, \ address, battery, dev_id, ver, reg_code, reg_rmday, ip, port, \ user_role, run_status, warn_status, lon, lat, salary from COMPANY") for row in cursor: print "ID = ", row[0] print "NET_SIGNAL = ", row[1] print "LOCAL_IP = ", row[2] print "CHARGE = ", row[3] print "USB = ", row[4] print "LOCATION = ", row[5] print "CROS_NET_TYPE = ", row[6] print "VRS_IP = ", row[7] print "VRS_PORT = ", row[8] print "ZHD_IP = ", row[9] print "ZHD_PORT = ", row[10] print "AGE = ", row[11] print "ADDRESS = ", row[12] print "BATTERY = ", row[13] print"DEV_ID = ", row[14] print"VER = ", row[15] print"REG_CODE = ", row[16] print"REG_RMDAY = ", row[17] print"IP = ", row[18] print"PORT = ", row[19] print"USER_ROLE = ", row[20] print"RUN_STATUS = ", row[21] print"WARN_STATUS = ", row[22] print"LON = ", row[23] print"LAT = ", row[24] print "SALARY = ", row[25], "\n" conn.commit() print "Operation done successfully"; conn.close() dump(test)
参考
https://www.cnblogs.com/vamei/p/3794388.html
http://www.runoob.com/sqlite/sqlite-python.html
nodejs
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
