# Exercise 2: Prepared Statements and Batch Insert

## 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 3.9 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 4: Try it out on TiDB Cloud*.

## Section 2: Prepared Statement and Getting the LAST_INSERT_ID

1. Go through the sample code note line `cursor = conn.cursor(prepared=True)` and `print("Generated PK is", cursor.lastrowid)` - [DemoPythonExecuteQueryFetchone.py](https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/DemoPythonExecuteQueryFetchone.py). If you create `cursor` with parameter `prepared=False` (the default value), you are using the client side prepared statement only. The `cursor.lastrowid` to get the auto increment ID of the recently inserted row.

2. Run demo script.
```
$ ./10-demo-python-prepared-statement-01-lastrowid-show.sh
```

## Section 3: Batch insert
1. Go through the sample code - [demo-batch-insert.py](https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/demo-batch-insert.py). Please note the `def _non_batch_style_insert(cursor, row_count):` and `def _batch_style_insert(cursor, row_count):` in the code, so that to understand the two different inserts cause different elapsed times.

2. Run demo script and check the result. Compare the different elapsed time in the output，batch inserting will save a lot of time.
```
$ ./10-demo-python-batch-insert-01-show.sh local
```

## Section 4: 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 [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.
```
$ ./10-demo-python-prepared-statement-01-lastrowid-show.sh
```

3. Use parameter `cloud` to run the demo against TiDB Cloud. Before running the script, make sure the TiDB Cloud cluster has been started and then set environment variables for TiDB Cloud:
```
$ export TIDB_CLOUD_HOST=<hostname> 
$ export TIDB_CLOUD_USERNAME=<username> 
$ export TIDB_CLOUD_PASSWORD=<password> 
$ export TIDB_CLOUD_PORT=<port> 
```
And then, run the script.
```
$ ./10-demo-python-batch-insert-01-show.sh cloud
```

## Prepared Statement and Getting the LAST_INSERT_ID sample output （Your ID may be different from this exercise）
```
$ ./10-demo-python-prepared-statement-01-lastrowid-show.sh
Generated PK is 5764607523034234890
```

## Batch insert sample output 
```
$ ./10-demo-python-batch-insert-01-show.sh local
Connected to TiDB: root@127.0.0.1:4000
Batch Inserting 10000 rows in 125.9365234375 (ms).
Total rows in t1_batchtest table: 10000.
Non-Batch Inserting 10000 rows in 2950.89892578125 (ms).
Total rows in t1_batchtest table: 10000.
```