# Exercise 1: Database Connectors

## Prerequisites
+ You had completed exercise [TiDB Architecture Basics Exercise 1b](https://eng.edu.pingcap.com/unit/view/id:2467)
+ Your TiDB service listens at port 4000.
+ The Python environment is already configured on the operating system.
+ [git](https://git-scm.com/) is already installed on the operating system.

## Section 1: Preparation
1. Clone example scripts repository to the practice node (e.g: Your local macOS, Linux or VM in the cloud).
```
$ git clone https://github.com/pingcap/tidb-course-201-lab
```

2. Go to working directory: `tidb-course-201-lab/scripts`.
```
$ cd tidb-course-201-lab/scripts
```

3. If you have TiDB Cloud Developer Tier and you prefer to do the exercise on a Cloud node like Amazon EC2, please jump to *Section 5: Try it out on TiDB Cloud*.

## Section 2: Making connection to TiDB
1. Go through the sample code - [DemoPythonConnection.py](https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/DemoPythonConnection.py). Please note the `conn = mysql.connector.connect` in the code, it will show you how to connect to TiDB.

2. Run demo script, and note the output.
```
$ ./09-demo-python-connection-01-show.sh
```

## Section 3: Executing SQL update
1. Go through and compare the different sample code, note the `query_insert = "INSERT INTO t1 (pk, name) VALUES (%s, %s)"` and `cursor.execute(query_insert, (1, "ABC"))` in [DemoPythonExecuteUpdatePositionalPlaceholders.py](https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/DemoPythonExecuteUpdatePositionalPlaceholders.py) and note the `query_insert = "INSERT INTO t1 (pk, name) VALUES (%(pk)s, %(name)s)"` and `cursor.execute(query_insert, {"pk": 2, "name": "DEF"})` in [DemoPythonExecuteUpdateNamedPlaceholders.py](https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/DemoPythonExecuteUpdateNamedPlaceholders.py). They are two ways to execute SQL update.

2. Run two sample scripts to insert data into the TiDB.
```
$ ./09-demo-python-execute-update-positional-placeholders-01-show.sh 
```
```
$ ./09-demo-python-execute-update-named-placeholders-01-show.sh
```

## Section 4: Executing SQL query
1. Go through the sample code and note line `query = "SELECT pk, name FROM t1 WHERE pk between %s and %s"` - [DemoPythonExecuteQuery.py](https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/DemoPythonExecuteQuery.py).

2. Run the demo script to query the data inserted by session 2.
```
$ ./09-demo-python-execute-query-01-show.sh
```

3. Go through the sample code - [DemoPythonExecuteQueryFetchone.py](https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/DemoPythonExecuteQueryFetchone.py), and note that we use `row = cursor.fetchone()` to avoid this automatic conversion between SQL and Python data types when fetching rows (to improve performance or to handle the conversion yourself). 

4. Run the other demo script and note that the result is different from step 2. Try to understand the function of `fetchone()`.
```
$ ./09-demo-python-execute-query-01-fetchone-show.sh
```

## Section 5: Try it out on TiDB Cloud
1. Until now, our exercises are targeting local TiDB Playground cluster. If you completed the [TiDB Architecture Basics Exercise 1a](https://eng.edu.pingcap.com/unit/view/id:2466), you can change the target database to TiDB Cloud Developer Tier. 
	+ Note: Each subsequent step requires you to do it on a practice node in the same Cloud Provider Region (e.g: Amazon EC2 instance) as your TiDB Cloud Developer Tier. Otherwise, the response latency may suffer. 

2. For making connection to TiDB, locate python connect URL `"database="test"`, `host="127.0.0.1"`, `port=4000`, `user="root"`, `password=""` in [DemoPythonConnection.py](https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/DemoPythonConnection.py) under the `scripts` folder on your practice node, change the `hostname`, `port`, `username` and `password` to corresponding values provided by your TiDB Cloud Developer Tier. And then, run the connection test script.
```
$ ./09-demo-python-connection-01-show.sh
```

3. For making connection to TiDB, locate python connect URL `"database="test"`, `host="127.0.0.1"`, `port=4000`, `user="root"`, `password=""` in [DemoPythonExecuteUpdatePositionalPlaceholders.py](https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/DemoPythonExecuteUpdatePositionalPlaceholders.py) and [DemoPythonExecuteUpdateNamedPlaceholders.py](https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/DemoPythonExecuteUpdateNamedPlaceholders.py) under the `scripts` folder on your practice node, change the `hostname`, `port`, `username` and `password` to corresponding values provided by your TiDB Cloud Developer Tier. And then, run the connection test script.
```
$ ./09-demo-python-execute-update-positional-placeholders-01-show.sh 
```
```
$ ./09-demo-python-execute-update-named-placeholders-01-show.sh
```

4. For making connection to TiDB, locate python connect URL `"database="test"`, `host="127.0.0.1"`, `port=4000`, `user="root"`, `password=""` in [DemoPythonExecuteQuery.py](https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/DemoPythonExecuteQuery.py) and [DemoPythonExecuteQueryFetchone.py](https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/DemoPythonExecuteQueryFetchone.py) under the `scripts` folder on your practice node, change the `hostname`, `port`, `username` and `password` to corresponding values provided by your TiDB Cloud Developer Tier. And then, run the connection test script.
```
$ ./09-demo-python-execute-query-01-show.sh
```
```
$ ./09-demo-python-execute-query-01-fetchone-show.sh
```

## Making connection sample output 
```
$ ./09-demo-python-connection-01-show.sh
Connected to 127.0.0.1:4000 as root (Connection ID: 415)
The server is running 5.7.25-TiDB-v6.2.0

```

## Executing SQL update sample output
```
$ ./09-demo-python-execute-update-positional-placeholders-01-show.sh
1 row inserted into table test.t1
```
```
$ ./09-demo-python-execute-update-named-placeholders-01-show.sh
1 row inserted into table test.t1
```

## Executing SQL query sample output
```
$ ./09-demo-python-execute-query-01-show.sh
PK of ABC is 1
PK of DEF is 2
```
```
$ ./09-demo-python-execute-query-01-fetchone-show.sh
1 ABC
2 DEF
```