tessl/pypi-mysql-python

Python interface to MySQL databases implementing the Python Database API version 2.0 specification.

—

Pending

Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Overview

Eval results

Files

Data Type Conversion

Name: tessl/pypi-mysql-python
Author: tessl

Automatic conversion between Python and MySQL data types, including comprehensive date/time handling, binary data processing, and DB API 2.0 compliant type classification system.

Capabilities

Type Conversion Functions

Core functions for converting Python objects to MySQL-compatible representations.

def Binary(x):
    """
    Convert data to binary string representation for BLOB fields.
    
    Parameters:
    - x (str/bytes): Data to convert to binary format
    
    Returns:
    str: Binary string representation
    """

def DateFromTicks(ticks):
    """
    Convert UNIX timestamp to date object.
    
    Parameters:
    - ticks (float): UNIX timestamp (seconds since epoch)
    
    Returns:
    datetime.date: Date object
    """

def TimeFromTicks(ticks):
    """
    Convert UNIX timestamp to time object.
    
    Parameters:
    - ticks (float): UNIX timestamp (seconds since epoch)
    
    Returns:
    datetime.time: Time object
    """

def TimestampFromTicks(ticks):
    """
    Convert UNIX timestamp to datetime object.
    
    Parameters:
    - ticks (float): UNIX timestamp (seconds since epoch)
    
    Returns:
    datetime.datetime: Datetime object
    """

Date and Time Classes

Date and time type aliases and classes for MySQL compatibility.

# Type aliases from datetime module
Date = datetime.date          # Date class for DATE fields
Time = datetime.time          # Time class for TIME fields  
Timestamp = datetime.datetime # Timestamp class for DATETIME/TIMESTAMP fields
TimeDelta = datetime.timedelta # Time delta class for time intervals

DB API Type Sets

DB API 2.0 compliant type classification sets for type checking and comparison.

class DBAPISet(frozenset):
    """
    Special frozenset subclass for DB API type comparisons.
    Allows 'type in typeset' and 'typeset == type' comparisons.
    """
    
    def __eq__(self, other):
        """
        Compare with individual types or other DBAPISet instances.
        
        Parameters:
        - other: Type constant or DBAPISet to compare
        
        Returns:
        bool: True if other is member of this set or sets are equal
        """

# DB API 2.0 type sets
STRING: DBAPISet     # String field types (ENUM, STRING, VAR_STRING, etc.)
BINARY: DBAPISet     # Binary field types (BLOB, TINY_BLOB, MEDIUM_BLOB, etc.)
NUMBER: DBAPISet     # Numeric field types (DECIMAL, TINY, SHORT, LONG, etc.)
DATE: DBAPISet       # Date field types (DATE, NEWDATE)
TIME: DBAPISet       # Time field types (TIME)  
TIMESTAMP: DBAPISet  # Timestamp field types (TIMESTAMP, DATETIME)
DATETIME: DBAPISet   # Alias for TIMESTAMP
ROWID: DBAPISet      # Row ID field types (empty set for MySQL)

Advanced Date/Time Processing

Specialized functions for parsing and formatting date/time values.

def DateTime_or_None(s):
    """
    Parse datetime string or return None if invalid.
    
    Parameters:
    - s (str): Datetime string to parse
    
    Returns:
    datetime.datetime/None: Parsed datetime or None
    """

def Date_or_None(s):
    """
    Parse date string or return None if invalid.
    
    Parameters:
    - s (str): Date string to parse
    
    Returns:
    datetime.date/None: Parsed date or None
    """

def Time_or_None(s):
    """
    Parse time string or return None if invalid.
    
    Parameters:
    - s (str): Time string to parse
    
    Returns:
    datetime.time/None: Parsed time or None
    """

def TimeDelta_or_None(s):
    """
    Parse time delta string or return None if invalid.
    
    Parameters:
    - s (str): Time delta string to parse
    
    Returns:
    datetime.timedelta/None: Parsed time delta or None
    """

def mysql_timestamp_converter(s):
    """
    Convert MySQL timestamp format to datetime.
    
    Parameters:
    - s (str): MySQL timestamp string
    
    Returns:
    datetime.datetime: Converted datetime object
    """

Formatting Functions

Functions for converting Python date/time objects to MySQL string formats.

def format_TIME(v):
    """
    Format time value for MySQL TIME field.
    
    Parameters:
    - v (datetime.time): Time value to format
    
    Returns:
    str: MySQL TIME format string
    """

def format_DATE(v):
    """
    Format date value for MySQL DATE field.
    
    Parameters:
    - v (datetime.date): Date value to format
    
    Returns:
    str: MySQL DATE format string
    """

def format_TIMESTAMP(d):
    """
    Format datetime value for MySQL TIMESTAMP/DATETIME field.
    
    Parameters:
    - d (datetime.datetime): Datetime value to format
    
    Returns:
    str: MySQL TIMESTAMP format string
    """

def format_TIMEDELTA(v):
    """
    Format timedelta value for MySQL TIME field.
    
    Parameters:
    - v (datetime.timedelta): Timedelta value to format
    
    Returns:
    str: MySQL TIME format string
    """

def DateTime2literal(d, c):
    """
    Convert datetime to SQL literal string.
    
    Parameters:
    - d (datetime.datetime): Datetime value
    - c: Connection object (for conversion context)
    
    Returns:
    str: SQL literal representation
    """

def DateTimeDelta2literal(d, c):
    """
    Convert timedelta to SQL literal string.
    
    Parameters:
    - d (datetime.timedelta): Timedelta value
    - c: Connection object (for conversion context)
    
    Returns:
    str: SQL literal representation
    """

Conversion System

The converters module provides automatic type conversion between Python and MySQL.

# Main conversion mapping (from MySQLdb.converters)
conversions: dict  # Dictionary mapping MySQL field types to conversion functions

Usage Examples

Basic Type Conversion

import MySQLdb
from datetime import datetime, date, time

db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
cursor = db.cursor()

# Date/time conversion
now = datetime.now()
today = date.today()
current_time = time(14, 30, 0)

cursor.execute("""
    INSERT INTO events (name, event_date, event_time, created_at) 
    VALUES (%s, %s, %s, %s)
""", ("Meeting", today, current_time, now))

db.commit()
cursor.close()
db.close()

Binary Data Handling

import MySQLdb

db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
cursor = db.cursor()

# Handle binary data
with open("image.jpg", "rb") as f:
    image_data = f.read()

# Convert to MySQL binary format
binary_data = MySQLdb.Binary(image_data)

cursor.execute(
    "INSERT INTO images (name, data) VALUES (%s, %s)",
    ("profile.jpg", binary_data)
)

db.commit()
cursor.close()
db.close()

Using UNIX Timestamps

import MySQLdb
import time

db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
cursor = db.cursor()

# Create dates from UNIX timestamps
timestamp = time.time()
event_date = MySQLdb.DateFromTicks(timestamp)
event_time = MySQLdb.TimeFromTicks(timestamp)
event_datetime = MySQLdb.TimestampFromTicks(timestamp)

cursor.execute("""
    INSERT INTO schedule (date_only, time_only, full_datetime)
    VALUES (%s, %s, %s)
""", (event_date, event_time, event_datetime))

db.commit()
cursor.close()
db.close()

Type Checking with DB API Sets

import MySQLdb
from MySQLdb.constants import FIELD_TYPE

db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
cursor = db.cursor()

cursor.execute("SELECT id, name, email, created_at FROM users LIMIT 1")
description = cursor.description

for column in description:
    field_name = column[0]
    field_type = column[1]
    
    # Check field type using DB API sets
    if field_type in MySQLdb.STRING:
        print(f"{field_name}: String field")
    elif field_type in MySQLdb.NUMBER:
        print(f"{field_name}: Numeric field")
    elif field_type in MySQLdb.TIMESTAMP:
        print(f"{field_name}: Timestamp field")
    elif field_type == MySQLdb.TIMESTAMP:  # Alternative comparison
        print(f"{field_name}: Timestamp field (alternative check)")

cursor.close()
db.close()

Custom Type Conversion

import MySQLdb
from MySQLdb.converters import conversions
from MySQLdb.constants import FIELD_TYPE
import json

# Custom converter for JSON fields
def json_converter(data):
    if data is None:
        return None
    return json.loads(data)

# Create custom conversion dictionary
custom_conv = conversions.copy()
custom_conv[FIELD_TYPE.BLOB] = json_converter

# Use custom converter
db = MySQLdb.connect(
    host="localhost", 
    user="user", 
    passwd="pass", 
    db="test",
    conv=custom_conv
)

cursor = db.cursor()
cursor.execute("SELECT settings FROM user_preferences WHERE user_id = %s", (1,))
settings = cursor.fetchone()[0]  # Automatically converted from JSON

print(f"User settings: {settings}")
cursor.close()
db.close()

Working with Time Zones

import MySQLdb
from datetime import datetime, timezone, timedelta

db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
cursor = db.cursor()

# Create timezone-aware datetime
eastern = timezone(timedelta(hours=-5))
event_time = datetime(2023, 12, 25, 15, 30, 0, tzinfo=eastern)

# MySQL will store as UTC or local time depending on configuration
cursor.execute(
    "INSERT INTO events (name, scheduled_at) VALUES (%s, %s)",
    ("Holiday Party", event_time)
)

db.commit()
cursor.close()
db.close()

Handling NULL Values

import MySQLdb

db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")
cursor = db.cursor()

# NULL values are automatically converted to Python None
cursor.execute("SELECT name, description FROM products WHERE id = %s", (1,))
row = cursor.fetchone()

name, description = row
if description is None:
    print(f"Product '{name}' has no description")
else:
    print(f"Product '{name}': {description}")

cursor.close()
db.close()

Additional Type Converter Functions

Additional conversion functions for specialized data types and custom conversion scenarios.

String and Primitive Type Converters

def Bool2Str(s, d):
    """
    Convert boolean to MySQL string representation.
    
    Parameters:
    - s (bool): Boolean value to convert
    - d: Conversion mapping dictionary
    
    Returns:
    str: "1" for True, "0" for False
    """

def Thing2Str(s, d):
    """
    Convert any object to string via str().
    
    Parameters:
    - s: Object to convert
    - d: Conversion mapping dictionary
    
    Returns:
    str: String representation of object
    """

def Float2Str(o, d):
    """
    Convert float to high-precision string representation.
    
    Parameters:
    - o (float): Float value to convert
    - d: Conversion mapping dictionary
    
    Returns:
    str: String with 15 decimal places precision
    """

def None2NULL(o, d):
    """
    Convert Python None to MySQL NULL.
    
    Parameters:
    - o: None value
    - d: Conversion mapping dictionary
    
    Returns:
    NULL: MySQL NULL constant
    """

def Thing2Literal(o, d):
    """
    Convert object to SQL string literal with proper quoting.
    
    Parameters:
    - o: Object to convert to literal
    - d: Conversion mapping dictionary
    
    Returns:
    str: Quoted SQL literal string
    """

def Unicode2Str(s, d):
    """
    Convert Unicode string to byte string using connection encoding.
    
    Parameters:
    - s (unicode): Unicode string to convert
    - d: Conversion mapping dictionary
    
    Returns:
    str: Encoded byte string
    """

Set and Collection Converters

def Str2Set(s):
    """
    Convert comma-separated string to Python set.
    
    Parameters:
    - s (str): Comma-separated string values
    
    Returns:
    set: Set of string values
    """

def Set2Str(s, d):
    """
    Convert Python set to comma-separated MySQL SET string.
    
    Parameters:
    - s (set): Set of values to convert
    - d: Conversion mapping dictionary
    
    Returns:
    str: Quoted comma-separated string literal
    """

def quote_tuple(t, d):
    """
    Convert tuple to SQL tuple literal with proper escaping.
    
    Parameters:
    - t (tuple): Tuple to convert
    - d: Conversion mapping dictionary
    
    Returns:
    str: SQL tuple literal like "(value1, value2, ...)"
    """

Array and Binary Data Converters

def array2Str(o, d):
    """
    Convert array.array to SQL string literal.
    
    Parameters:
    - o (array.array): Array object to convert
    - d: Conversion mapping dictionary
    
    Returns:
    str: SQL string literal of array contents
    """

def char_array(s):
    """
    Create character array from string.
    
    Parameters:
    - s (str): String to convert to character array
    
    Returns:
    array.array: Character array
    """

Instance and Class Converters

def Instance2Str(o, d):
    """
    Convert class instance to string representation.
    
    Searches conversion mapping for the exact class or compatible
    base class, falling back to string conversion if no specific
    converter is found.
    
    Parameters:
    - o: Class instance to convert
    - d: Conversion mapping dictionary
    
    Returns:
    str: String representation of instance
    """

Usage Examples

from MySQLdb import converters

# Boolean conversion
result = converters.Bool2Str(True, {})
print(result)  # "1"

# Set conversion
mysql_set = "red,green,blue"
python_set = converters.Str2Set(mysql_set)
print(python_set)  # {'red', 'green', 'blue'}

# Convert back to MySQL format
mysql_format = converters.Set2Str(python_set, converters.conversions)
print(mysql_format)  # "'red,green,blue'"

# Tuple conversion for IN clauses
values = (1, 2, 3, 4)
sql_tuple = converters.quote_tuple(values, converters.conversions)
print(sql_tuple)  # "(1, 2, 3, 4)"

# Array conversion
import array
char_data = array.array('c', b'hello')
sql_string = converters.array2Str(char_data, converters.conversions)
print(sql_string)  # "'hello'"

Install with Tessl CLI