sqlite_connection.md

# Python SQLITE Connection

继承自前一节的 SqliteConnector，增加执行sql接口，请先完成【Python SQLITE Connector】

```python
# -*- coding: UTF-8 -*-
import logging
from error_code import ErrorCode
from sqlite_connector import SqliteConnector
logger = logging.Logger(__name__)

class SqliteConnection(SqliteConnector):
    def __init__(self, db_file) -> None:
        super().__init__(db_file)

    def execute(self, sql, arg=None):
        # TODO(You): 请在此实现查询代码


if __name__ == '__main__':
    kv = SqliteConnection("/tmp/test.db")

    ret = kv.open()
    assert ret['err'] == ErrorCode.SUCCESS

    sql = '''create table if not exists key_value (
        _key varchar(32) primary key not null,
        value text not null
    );'''
    ret = kv.execute(sql)
    assert ret['err'] == ErrorCode.SUCCESS
    ret = kv.close()
    assert ret['err'] == ErrorCode.SUCCESS
```

请选出下列能**正确**实现这一功能的选项。

## template

```python
import logging
from error_code import ErrorCode
from sqlite_connector import SqliteConnector
logger = logging.Logger(__name__)


class SqliteConnection(SqliteConnector):
    def __init__(self, db_file) -> None:
        super().__init__(db_file)

    def execute(self, sql, arg=None):
        results = []
        try:
            cursor = self.conn.execute(sql, arg or tuple())
            if cursor is not None:
                results = cursor.fetchall()
            self.conn.commit()
            return {'err': ErrorCode.SUCCESS, 'results': results}
        except Exception as e:
            logger.error(
                f'execute sql exception, sql:{sql}, arg:{arg}, exception:{str(e)}')
            self.conn.rollback()
            return {'err': ErrorCode.DB_QUERY_EXCEPT, 'results': results}


if __name__ == '__main__':
    kv = SqliteConnection("/tmp/test.db")

    ret = kv.open()
    assert ret['err'] == ErrorCode.SUCCESS

    sql = '''create table if not exists key_value (
        _key varchar(32) primary key not null,
        value text not null
    );'''
    ret = kv.execute(sql)
    assert ret['err'] == ErrorCode.SUCCESS
    ret = kv.close()
    assert ret['err'] == ErrorCode.SUCCESS
```

## 答案

```python
class SqliteConnection(SqliteConnector):
    ...
    def execute(self, sql, arg=None):
        results = []
        try:
            cursor = self.conn.execute(sql, arg or tuple())
            if cursor is not None:
                results = cursor.fetchall()
            self.conn.commit()
            return {'err': ErrorCode.SUCCESS, 'results': results}
        except Exception as e:
            logger.error(
                f'execute sql exception, sql:{sql}, arg:{arg}, exception:{str(e)}')
            self.conn.rollback()
            return {'err': ErrorCode.DB_QUERY_EXCEPT, 'results': results}
```

## 选项

### A

```python
class SqliteConnection(SqliteConnector):
    ...
    def execute(self, sql, arg=None):
        results = []
        cursor = self.conn.execute(sql, arg or tuple())
        if cursor is not None:
            results = cursor.fetchall()
        self.conn.commit()
        return {'err': ErrorCode.SUCCESS, 'results': results}
       
```

### B

```python
class SqliteConnection(SqliteConnector):
    ...
    def execute(self, sql, arg=None):
        results = []
        try:
            cursor = self.conn.execute(sql, arg or tuple())
            results = cursor.fetchall()
            self.conn.commit()
            return {'err': ErrorCode.SUCCESS, 'results': results}
        except Exception as e:
            logger.error(
                f'execute sql exception, sql:{sql}, arg:{arg}, exception:{str(e)}')
            self.conn.rollback()
            return {'err': ErrorCode.DB_QUERY_EXCEPT, 'results': results}
```

### C

```python
class SqliteConnection(SqliteConnector):
    ...
    def execute(self, sql, arg=None):
        results = []
        try:
            cursor = self.conn.execute(sql, arg or tuple())
            if cursor is not None:
                results = cursor.fetchall()
            self.conn.commit()
            return {'err': ErrorCode.SUCCESS, 'results': results}
        except Exception as e:
            logger.error(
                f'execute sql exception, sql:{sql}, arg:{arg}, exception:{str(e)}')
            return {'err': ErrorCode.DB_QUERY_EXCEPT, 'results': results}
```

### D

```python
class SqliteConnection(SqliteConnector):
    ...
    def execute(self, sql, arg=None):
        results = []
        try:
            cursor = self.conn.execute(sql, arg or tuple())
            if cursor is not None:
                results = cursor.fetchall()
            return {'err': ErrorCode.SUCCESS, 'results': results}
        except Exception as e:
            logger.error(
                f'execute sql exception, sql:{sql}, arg:{arg}, exception:{str(e)}')
            self.conn.rollback()
            return {'err': ErrorCode.DB_QUERY_EXCEPT, 'results': results}
```