pycharmers.sdk.base module¶

Base class for Python-Charmers’ SDK

class pycharmers.sdk.base.PycharmersSDK(env_path: str = '/Users/iwasakishuto/.pycharmers/.env', api_name: str = '', verbose: bool = True, **kwargs)[source]¶

Bases: abc.ABC

Abstract Class for Python-Charmers API

Parameters

env_path (str) – path/to/.env (default= DOTENV_PATH)
service_name (str) – The service name.
verbose (bool) – Whether to print message or not. (default= False)
**kwargs (dict) – Required Keywords.

print¶

Print function based on verbose

Type: function

service_name¶

The service name

Type: str

required_env_varnames¶

The required env varnames.

Type: list

kwargs¶

Keyword arguments.

Type: dict

keyname2envname(keyname)[source]¶

Convert keyname to environment varname.

Args: keyname (str) : Keyname for method.

Examples

>>> from pycharmers.sdk._base import PycharmersSDK
>>> sdk = PycharmersSDK()
>>> sdk.keyname2envname("id")
'TRANSLATION_GUMMY_GATEWAY_USELESS_NAME'
>>> sdk.keyname2envname("hoge")
'TRANSLATION_GUMMY_GATEWAY_USELESS_HOGE'

get_val(keyname, **kwargs)[source]¶

Get the value from gatewaykwargs or an environment variable.

Parameters

keyname (str) – Keyname for each method.
**kwargs (dict) – Given kwargs.

Examples

>>> from pycharmers.sdk._base import PycharmersSDK
>>> sdk = PycharmersSDK()
>>> print(sdk.get_val("hoge"))
None
>>> print(sdk.get_val("username"))
USERNAME_IN_ENVFILE
>>> print(gateway.get_val("username", username=":)"))
:)

class pycharmers.sdk.base.PycharmersSQL(api_name: str = 'SQL', verbose: bool = False, **kwargs)[source]¶

Bases: pycharmers.sdk.base.PycharmersSDK

Wrapper class for Sqlite.

Parameters

database (Optional[str], optional) – database to use. Defaults to None.
verbose (bool, optional) – Whether to print message or not Defaults to False.

static format_data(data: Any, type: str) → str[source]¶

Format python data to match sql data format.

Parameters

data (Any) – Python any data.
type (str) – SQL data type. (ex. "int(11)", "varchar(100)")

Returns

Correctly formatted python data.

Return type

str

Examples

>>> import datetime
>>> from pycharmers.sdk import PycharmersSQL
>>> PycharmersSQL.format_data(data=1, type="int(8)")
    '1'
>>> PycharmersSQL.format_data(data=1, type="varchar(10)")
    '"1"'
>>> PycharmersSQL.format_data(data=datetime.datetime(1998,7,3), type="datetime")
    '"1998-07-03 00:00:00"'

abstract connect(func: Callable[[Any], Optional[Union[pandas.core.frame.DataFrame, Tuple[tuple]]]], **kwargs) → Optional[Union[pandas.core.frame.DataFrame, Tuple[tuple]]][source]¶

Create a connection to the database, and close it after excuting func .

Parameters: func (Callable[[Any], Union[pd.DataFrame, Tuple[tuple], None]) – The function you want to execute. Receive cursor as the first argument.
Returns: Return value from func
Return type: Union[pd.DataFrame, Tuple[tuple], None]

execute(query: str, columns: List[str] = [], verbose: bool = False) → Optional[Union[pandas.core.frame.DataFrame, Tuple[tuple]]][source]¶

Execute a SQL query.

Parameters

query (str) – Query to execute on server.
columns (List[str], optional) – If this value is fiven, Return value will be as a pd.DataFrame format. Defaults to [].
verbose (bool, optional) – Whether to display the query or not. Defaults to False.

Returns

Return value of query.

Return type

Union[pd.DataFrame, Tuple[tuple], None]

Examples

>>> from pycharmers.sdk import PycharmersSQL
>>> sql = PycharmersSQL()
>>> sql.execute("show databases;")
    (('information_schema',),
     ('mysql',),
     ('performance_schema',),
     ('remody',),
     ('sys',))
>>> # Receive as a DataFrame.
>>> df = sql.execute("show databases;", columns=["name"])
>>> print(df.to_markdown())
    |    | name               |
    |---:|:-------------------|
    |  0 | information_schema |
    |  1 | mysql              |
    |  2 | performance_schema |
    |  3 | remody             |
    |  4 | sys                |

create(table: str, verbose: bool = False, column_info={}) → None[source]¶

Create a Table named table .

Parameters

table (str) – The name of table.
verbose (bool, optional) – Whether to display the query or not. Defaults to False.
columninfo (dict) – Key value style column information.

Examples

>>> from pycharmers.sdk import PycharmersSQL
>>> sql = PycharmersSQL()
>>> sql.create(table="pycharmers_user", column_info={
...     "id"      : 'int NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT "ID"',
...     "username": 'VARCHAR(100) NOT NULL COMMENT "ユーザー名"',
...     "created" : 'datetime DEFAULT NULL COMMENT "登録日"' ,
>>> })

drop(table: str, verbose: bool = False) → None[source]¶

Drop the table .

Parameters

table (str) – The name of table.
verbose (bool, optional) – Whether to display the query or not. Defaults to False.

Examples

>>> from pycharmers.sdk import PycharmersSQL
>>> sql = PycharmersSQL()
>>> sql.drop(table="pycharmers_user")

describe(table: str, verbose: bool = False) → pandas.core.frame.DataFrame[source]¶

Get table structure.

Parameters

table (str) – The name of table.
verbose (bool, optional) – Whether to display the query or not. Defaults to False.

Returns

Table structure.

Return type

pd.DataFrame

Examples

>>> from pycharmers.sdk import PycharmersSQL
>>> sql = PycharmersSQL()
>>> df = sql.describe(table="pycharmers_user")
>>> print(df.to_markdown())
|    | Field    | Type         | Null   | Key   | Default   | Extra          |
|---:|:---------|:-------------|:-------|:------|:----------|:---------------|
|  0 | id       | int(11)      | NO     | PRI   |           | auto_increment |
|  1 | username | varchar(100) | NO     |       |           |                |
|  2 | created  | datetime     | YES    |       |           |                |

insert(table: str, data: List[list], columns: list = [], col_type='input_field', verbose: bool = False) → int[source]¶

Insert a record to specified table

Parameters

table (str) – The name of the table.
data (List[list]) – Data to be inserted.
columns (list, optional) – Column names to be inserted values. Defaults to [].
col_type (str, optional) – If columns has no data, use get_colnames to decide which columns to be inseted. Defaults to "input_field".
verbose (bool, optional) – Whether to display the query or not. Defaults to False.

Returns

The number of rows in table

Return type

int

Examples

>>> import datetime
>>> from pycharmers.sdk import PycharmersSQL
>>> sql = PycharmersSQL()
>>> sql.insert(table="pycharmers_user", data=[
...     ["iwasaki", "now()"],
...     ["shuto", datetime.datetime(1998,7,3)]
>>> ], columns=["username", "created"])

update(table: str, old_column: str, old_value: Any, new_columns: List[str], new_values: List[Any], verbose: bool = False) → None[source]¶

Update records.

Parameters

table (str) – The name of table.
old_column (str) – [description]
old_value (Any) –
new_columns (List[str]) – [description]
new_values (List[Any]) –
verbose (bool, optional) – Whether to display the query or not. Defaults to False.

Examples

>>> from pycharmers.sdk import PycharmersSQL
>>> sql = PycharmersSQL()
>>> print(sql.selectAll(table="pycharmers_user").to_markdown())
    |    |   id | username   | created             |
    |---:|-----:|:-----------|:--------------------|
    |  0 |    1 | iwasaki    | 2021-05-22 07:23:10 |
    |  1 |    2 | shuto      | 1998-07-03 00:00:00 |
>>> sql.update(table="pycharmers_user", old_column="username", old_value="iwasaki", new_column="created", new_value="now()")
>>> print(sql.selectAll(table="pycharmers_user").to_markdown())
    |    |   id | username   | created             |
    |---:|-----:|:-----------|:--------------------|
    |  0 |    1 | iwasaki    | 2021-05-22 07:28:09 |
    |  1 |    2 | shuto      | 1998-07-03 00:00:00 |

delete(table: str, column: str, value: Any, verbose: bool = False) → None[source]¶

Delete the records whose column is value from the table named table .

Parameters

table (str) – The name of table.
column (str) –
value (Any) –
verbose (bool, optional) – Whether to display the query or not. Defaults to False.

Examples

>>> from pycharmers.sdk import PycharmersSQL
>>> sql = PycharmersSQL()
>>> print(sql.select(table="pycharmers_user").to_markdown())
    |    |   id | username   | created             |
    |---:|-----:|:-----------|:--------------------|
    |  0 |    1 | iwasaki    | 2021-05-22 07:23:10 |
    |  1 |    2 | shuto      | 1998-07-03 00:00:00 |
>>> sql.delete(table="pycharmers_user", column="username", value="shuto")
>>> print(sql.select(table="pycharmers_user").to_markdown())
    |    |   id | username   | created             |
    |---:|-----:|:-----------|:--------------------|
    |  0 |    1 | iwasaki    | 2021-05-22 07:23:10 |

merge(table1info: Dict[str, List[str]], table2info: Dict[str, List[str]], method: str = 'inner', verbose: bool = False) → pandas.core.frame.DataFrame[source]¶

Select values from table1 and table2 using method JOIN

Parameters

table1info (Dict[str,List[str]]) – Table1 information ( key : table name, value : selected columns (0th column is used for merging.))
table2info (Dict[str,List[str]]) – Table2 information ( key : table name, value : selected columns (0th column is used for merging.))
method (str, optional) – How to merge two tables. Defaults to "inner".
verbose (bool, optional) – Whether to display the query or not. Defaults to False.

Returns

Merged table records.

Return type

pd.DataFrame

Examples

>>> from pycharmers.sdk import PycharmersSQL
>>> sql.merge(
...     table1info={"user": ["id", "name", "job_id"]},
...     table2info={"jobs": ["id", "job_name"]},
...     method="inner", verbose=True
>>> )
>>> SELECT user.name, user.job_id, jobs.job_name FROM user INNER JOIN jobs ON user.id = jobs.id;

get_colnames(table: str, col_type: str = 'all') → list[source]¶

Get column names in the specified table .

Parameters

table (str) – The name of table.
col_type (str, optional) – What types columns to extract. Defaults to "all".

Returns

Extracted Columns.

Return type

list

Examples

>>> from pycharmers.sdk import PycharmersSQL
>>> sql = PycharmersSQL()
>>> sql.get_colnames(table="pycharmers_user")
>>> ['id', 'username', 'created']

select(table: str, columns: List[str] = [], where: Dict[str, Any] = {}, verbose: bool = False) → pandas.core.frame.DataFrame[source]¶

Get selected records.

Parameters

table (str) – The name of table.
columns (List[str], optional) – Selected Columns. If you don’t specify any columns, extract all columns. Defaults to [].
where (Dict[str,Any], optional) – Specify the condition of the column to be extracted. { colname : value }
verbose (bool, optional) – Whether to display the query or not. Defaults to False.

Returns

Selected Records.

Return type

pd.DataFrame

Examples

>>> from pycharmers.sdk import PycharmersSQL
>>> sql = PycharmersSQL()
>>> df = sql.select(table="pycharmers_user")
>>> print(df.to_markdown())
    |    |   id | username   | created             |
    |---:|-----:|:-----------|:--------------------|
    |  0 |    1 | iwasaki    | 2021-05-22 07:23:10 |
    |  1 |    2 | shuto      | 1998-07-03 00:00:00 |
>>> df = sql.select(table="pycharmers_user", columns=["id", "username"])
>>> print(df.to_markdown())
    |    |   id | username   |
    |---:|-----:|:-----------|
    |  0 |    1 | iwasaki    |
    |  1 |    2 | shuto      |

count_rows(table: str, verbose: bool = False) → int[source]¶

Get the number of rows in the table .

Parameters

table (str) – The name of table.
verbose (bool, optional) – Whether to display the query or not. Defaults to False.

Returns

The number of rows in the specified table .

Return type

int

Examples

>>> from pycharmers.sdk import PycharmersSQL
>>> sql = PycharmersSQL()
>>> sql.count_rows(table="pycharmers_user")
    2

show_tables(verbose: bool = False) → pandas.core.frame.DataFrame[source]¶

Show all tables.

Parameters: verbose (bool, optional) – Whether to display the query or not. Defaults to False.
Returns: Information for all Tables.
Return type: pd.DataFrame

>>> from pycharmers.sdk import PycharmersSQL
>>> sql = PycharmersSQL()
>>> df = sql.show_tables()
>>> print(df.to_markdown())
    |    | Name            | Engine   |   Version | Row_format   |   Rows |   Avg_row_length |   Data_length |   Max_data_length |   Index_length |   Data_free |   Auto_increment | Create_time         | Update_time         | Check_time   | Collation       | Checksum   | Create_options   | Comment   |
    |---:|:----------------|:---------|----------:|:-------------|-------:|-----------------:|--------------:|------------------:|---------------:|------------:|-----------------:|:--------------------|:--------------------|:-------------|:----------------|:-----------|:-----------------|:----------|
    |  0 | pycharmers_user | InnoDB   |        10 | Dynamic      |      0 |                0 |         16384 |                 0 |              0 |           0 |                1 | 2021-05-22 07:06:17 | NaT                 |              | utf8_general_ci |            |                  |           |

explain(table: str, verbose: bool = False) → pandas.core.frame.DataFrame[source]¶

Explain records

Parameters

table (str) – The name of table.
verbose (bool, optional) – Whether to display the query or not. Defaults to False.

Returns

Explanation for records.

Return type

pd.DataFrame

Examples

>>> from pycharmers.sdk import PycharmersSQL
>>> sql = PycharmersSQL()
>>> df = sql.explain(table="pycharmers_user")
>>> print(df.to_markdown())
|    |   id | select_type   | table           | partition   | type   | possible_keys   | key   | key_len   | ref   |   rows |   filtered | Extra   |
|---:|-----:|:--------------|:----------------|:------------|:-------|:----------------|:------|:----------|:------|-------:|-----------:|:--------|
|  0 |    1 | SIMPLE        | pycharmers_user |             | ALL    |                 |       |           |       |      2 |        100 |         |

pycharmers.sdk.base module¶

Other contents

Social link

Table of Contents

Previous topic

Next topic

This Page