Scribd
Upload
31 views3 pages

Python SQLite Database Tutorial

The document discusses using the Records library for interacting with Python databases. It provides code examples for connecting to an SQLite database, creating tables, running queries, inserting records, and exporting data. Records provides a simple and unified API for common database operations like SQL queries and transactions. It works with many database types and aims to make database access intuitive with features like automatic connection management and parameterized queries.

Uploaded by

Zheng Jun
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
31 views3 pages

Python SQLite Database Tutorial

The document discusses using the Records library for interacting with Python databases. It provides code examples for connecting to an SQLite database, creating tables, running queries, inserting records, and exporting data. Records provides a simple and unified API for common database operations like SQL queries and transactions. It works with many database types and aims to make database access intuitive with features like automatic connection management and parameterized queries.

Uploaded by

Zheng Jun
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

---

title: python-database
date: 2020-09-16 15:41:56
tags: Python
categories: Language
---

## sqlite api

[sqlite]([Link]

```python
[Link] = [Link]('[Link]')
[Link] = [Link]()
sql = """
create table if not exists t_measurements(f_id INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
f_solutionId INTEGER NOT NULL,
f_name CHAR(300) NOT NULL , f_measureType CHAR(100) NOT NULL , f_points CHAR(500)
NOT NULL );
"""
[Link](sql)
sql = """
create table if not exists t_solution(f_id INTEGER PRIMARY KEY AUTOINCREMENT NOT
NULL,
f_name CHAR(300) NOT NULL , f_createDT CHAR(100) NOT NULL );
"""
[Link](sql)
[Link]()
```
<!-- more -->
### delete

```python
def delete_task(conn, id):
"""
Delete a task by task id
:param conn: Connection to the SQLite database
:param id: id of the task
:return:
"""
sql = 'DELETE FROM tasks WHERE id=?'
cur = [Link]()
[Link](sql, (id,))
[Link]()

def delete_all_tasks(conn):
"""
Delete all rows in the tasks table
:param conn: Connection to the SQLite database
:return:
"""
sql = 'DELETE FROM tasks'
cur = [Link]()
[Link](sql)
[Link]()
```

### 插入
```python
sql = 'insert into t_solution(f_id , f_name , f_createDT) values (?,?,?)'
[Link](sql, (None, new_name, dt))
[Link]()
[Link]
```

## records

用了一下 不好用，例子很少，常用的功能找起来费劲

据说是 Requests 作者的作品，值得试用一下，通用而优雅的数据库访问库

Records 的特点：

- 基于 SQLAlchemy 和 Tablib 开发。


- 方法非常简单且统一。
- 支持缓存查询的数据.
- 自动实现上下文管理器，不需要关注数据连接状态。
- 支持数据库事务，保持数据的原子性和一致性。
- 支持安全的参数化查询，以安全的形式进行动态参数传递，防止 sql 语句不规范导致的安全问题；
- 支持主流数据库，如 RedShift, Postgres, MySQL, SQLite, Oracle, 和 MS-SQL

```python
pip install records
```

Records 是基于 SQLAlchemy 实现的，所以数据库链接方式参考 SQLAlchemy

<img alt="20200916_154524.png" src="python-database/20200916_154524.png"


width="500" height="">

Records 操作数据的 步骤非常的简单，如下：

- 连接数据库，返回 DB 数据库对象。
- 使用 query 方法执行 SQL 语句。

### sqlite

#### connect

```python
[Link] = [Link]('sqlite:///[Link]')
```

#### create table

```python
sql = """create table if not exists t_measurements(f_id INTEGER PRIMARY KEY
AUTOINCREMENT NOT NULL,
f_name CHAR(300) NOT NULL , f_measureType CHAR(100) NOT NULL , f_points CHAR(500)
NOT NULL );
"""
[Link](sql)
```
#### select

```python
sql = 'select * from t_solution;'
for row in [Link](sql):
self.solution_dict[row[1]] = {'id': row[0], 'name': row[1], 'createDT':
row[2]}
[Link]('end', *map(unicode, (row[1], row[2])))
```

#### 统计行数

```python
sql = 'select COUNT(*) from t_config;'
[Link](sql)
cur_result = [Link]()
if not cur_result or 0 == cur_result[0]:
sql = 'insert into t_config(f_id,f_devIp,f_devPort) values (?,?,?)'
[Link](sql, (None, '', ''))
[Link]()
```

#### insert

```python
sql = 'insert into t_solution(f_id , f_name , f_createDT) values (?,?,?)'
[Link](sql, (None, new_name, dt))
[Link]()
```

#### 导出

```python
print([Link]('json'))
print([Link]('csv'))
```

You might also like