# CustomPyMySQL

**Repository Path**: the_machine/custom-py-mysql

## Basic Information

- **Project Name**: CustomPyMySQL
- **Description**: No description available
- **Primary Language**: Python
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2024-05-22
- **Last Updated**: 2024-12-10

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

## <center>custom-py-mysql</center>
-------------------------------------------

### Table of contents
- [Installation](#installation)
- [Usage](#usage)
    - [Quick start](#quick-start)
    - [Create table](#create-table)
    - [Insert](#insert)
    - [Update](#update)
    - [Delete](#delete)
    - [Select](#selete)


### Installation
```shell
pip install git+https://gitee.com/the_machine/custom-py-mysql.git
```

Install mysqlclient
```shell
 sudo apt-get install python3-dev default-libmysqlclient-dev build-essential pkg-config # Debian /

 sudo yum install python3-devel mysql-devel pkgconfig # Red Hat / CentOS

 pip install mysqlclient
```

### Usage

#### Quick start

Store MySQL credentials in `auth.yaml`:
```yaml
database: user
host: '192.168.32.1'
# host: '127.0.0.1'
password: '0000'
port: '3306'
user: '1234'
```


Select user `name` and `age` from table `user_info`
```python
# Create connection from yaml
from custom_pymysql import mysql_connection
db = mysql_connection.from_yaml('auth.yaml')

from custom_pymysql.query import queries as qs

db.execute(
    qs.Select("age", "name").from_("personal_info")
    .where(
        qs.ge(qs.DBColumn('age'), qs.DBValue(22))
    )
).data_w_cols
```

##### Create table
```python
from custom_pymysql.components.column import DBColumn
from custom_pymysql.query.queries import CreateTable
from custom_pymysql.components import data_type


col_name = (DBColumn('name')
    .set_data_type(data_type.TYPE_VARCHAR)
    .set_comment('姓名')
    .set_size(20)
    .set_nullable(False)
)

col_age = (
    DBColumn('age')
    .set_data_type(data_type.TYPE_INT)
    .set_comment('年龄')
    .set_nullable(False)
)

col_dob = (
    DBColumn('dob')
    .set_data_type(data_type.TYPE_DATE)
    .set_comment('出生日期')
    .set_nullable()
)

col_id = (
    DBColumn('pid')
    .set_data_type(data_type.TYPE_CHAR)
    .set_size(5)
    .set_nullable(False)
    .set_comment('身份标识')
)

q = (
    CreateTable('personal_info')
    .set_columns(col_name, col_age, col_dob, col_id)
    .set_comment("This is an example")
    .set_primary_key(col_id)
)

print(q)
```

##### Selete
```python
from custom_pymysql.query.queries import Select
from custom_pymysql.components.column import DBColumn
from custom_pymysql.components.value import DBValue
from custom_pymysql.query import operator

s = (
    Select("name", DBColumn("age"))
    .from_("personal_info")
    .where(
        operator.gt(DBColumn('age'), DBValue(14)),
        operator.contains(DBColumn('name'), '张'),
        operator.eq(DBColumn('pid'), DBValue('12345'))
        )
    )

print(s)
```

##### Insert
```python
from custom_pymysql.queries import InsertInto
from datetime import datetime
from custom_pymysql.components.data_process import DEFAULT_DATETIME_FORMAT
from custom_pymysql.components.column import DBColumn

data = [
    {'name': '张三', 'age': 20, 'pid': '12345'},
    {'name': '李四', 'age': 22, 'pid': '12346'},
    {'name': '王五', 'pid': '12347'},
    {'name': '赵六', 'register_time': datetime.strptime("2022-01-01 10:10:10", DEFAULT_DATETIME_FORMAT)}
]

q = (
    InsertInto('personal_info')
    .values(data)
    .to_query()
)

print(q)

cols = ['name', DBColumn('age')]
data = [
    ['张三', 33],
    ['李四', 44]
]
q = (
    InsertInto('personal_info')
    .columns(*cols)
    .values(data)
    .to_query()
)

print(q)
```

##### Update
```python
from custom_pymysql.query.queries import Update
from custom_pymysql.components.column import DBColumn
from custom_pymysql.components.value import DBValue
from custom_pymysql.query import operator

s = (
    Update("personal_info")
    .set(name='张三三', age=22, dob=None)
    .where(
        operator.gt(DBColumn('age'), DBValue(14)),
        operator.contains(DBColumn('name'), '张'),
        operator.eq(DBColumn('pid'), DBValue('12345'))
    )
    )

print(s)
```

##### Delete
```python
from custom_pymysql.components.column import DBColumn
from custom_pymysql.components.value import DBValue
from custom_pymysql.query.queries import DeleteFrom
from custom_pymysql.query import operator


q = (
    DeleteFrom("company")
    .where(
        operator.eq(DBColumn('name'), DBValue('test company')),
        operator.gt(DBColumn('create_time'), DBValue('2000-01-01'))
        )
    )
print(q)
```