Database Operation¶
seldom
supports simple operations of SQLite3 and MySQL database.
sqlite3 | MySQL |
---|---|
execute_sql() | execute_sql() |
query_sql() | query_sql() |
delete() | delete() |
insert() | insert() |
select() | select() |
update() | update() |
init_table() | init_table() |
close() | close() |
Connecting DB¶
Connect to SQLit3 database
from seldom.db_operation import SQLiteDB
db = SQLiteDB(r"D:\learnAPI\db.sqlite3")
Connect to MySQL database
- Install the PyMySQL driver
> pip install pymysql
- connect to databases
from seldom.db\_operation import MySQLDB
db = MySQLDB(host="127.0.0.1", port="3306", user="root", password="123",
database="db_name")
Operation Method¶
- execute_sql
The SQL statement was executed, but no result was returned.
db.execute_sql("INSERT INTO table_name (id, name) VALUES (1, 'tom') ")
db.execute_sql("UPDATE table_name SET name = 'jack' WHERE id=1")
db.execute_sql("DELETE FROM table_name WHERE id = 1")
- query_sql
The query SQL statement is executed and the query result is returned.
ret = db.query_sql("select * from table_name")
print(ret)
- delete
Delete table data.
db.delete(table="user", where={"id":1})
- insert
Insert a data.
data = {'id': 1, 'username': 'admin', 'password': "123"},
db.insert(table="user", data=data)
- select
Query data in the table.
result = db.select(table="user", where={"id":1, "name": "tom"})
print(result)
- update
Update table data.
db.update(table="user", data={"name":"new tom"}, where={"name": "tom"})
- init_table
Bulk inserts, clearing table data before inserting.
datas = {
'api_event': [
{'id': 1, 'name': 'Redmi PRO launch1'},
{'id': 2, 'name': 'Redmi2 PRO launch'},
{'id': 3, 'name': 'Redmi3 PRO launch'},
{'id': 4, 'name': 'Redmi4 PRO launch'},
{'id': 5, 'name': 'Redmi5 PRO launch'},
],
'api_guest': [
{'id': 1, 'real_name': 'alen'},
{'id': 2, 'real_name': 'jack'},
{'id': 3, 'real_name': 'tom'},
]
}
db.init_table(datas)
- close
Close the database connection.
db.close()