sqlstar 3.1.4

for now, there is only mysql backend...

connection

import sqlstar

# driver://user:passwd@host:port/dbname
mysql = sqlstar.Database('mysql://root:***@localhost/tmp')
mysql.connect()

Query

QUERY = '''
    SELECT *
    FROM Girls
    WHERE AGE BETWEEN 20 AND 24
        AND BOYFRIEND IS NULL
    ORDER BY WHITE, RICH, BEAUTY DESC;
'''

Fetch data, and format result into Dataframe

df = mysql.fetch_df(QUERY)

Fetch all the rows

data = mysql.fetch_all(QUERY)

Fetch several rows

data = mysql.fetch_many(QUERY, 3)

Execute

mysql.execute("""
    CREATE TABLE `users` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `email` varchar(255) COLLATE utf8_bin NOT NULL,
        `password` varchar(255) COLLATE utf8_bin NOT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
    AUTO_INCREMENT=1 ;
    """)

Insert

Insert many records

mysql.insert_many(table, data, cols)

Insert Dataframe type of data

mysql.insert_df(table, df)

Export

Export result to csv

mysql.export_csv(query, fname, sep)

Export result to excel

mysql.export_excel(query, fname)

Create table

mysql.create_table(
    "users",
    comments={
        "name": "姓名",
        "height": "身高",
        "weight": "体重"
    },
    dtypes={
        "varchar(30)": [
            "name",
            "occupation",
        ],
        "float": ["height", "weight"],
        "int": ["age"],
    },
)

if you have data, you can make it more simple, just like this

mysql.create_table("users", df)

if you only want to specify some of them

mysql.create_table(
    table='news_spider',
    df=df,
    comments={
        "create_time": "插入时间",
        "title": "标题",
        "content": "正文",
        "author": "作者",
        "publish_time": "发布时间",
        "read_num": "阅读量",
    },
    # if type is not given, sqlstar will automatically inference
    dtypes={
        "datetime": ["create_time", "publish_time"],
        "longtext": ["content"],
        "varchar(100)": ["title", "author"],
        "decimal(10, 3)": ["read_num"]
    })

You don't need to fill in everything, and you just need to fill in comment or data type that you want to specify, then sqlstar will do the rest for you.

Rename table

mysql.rename_table(table, name)

Rename column

mysql.rename_column(table, column, name, dtype)

Add new column

mysql.add_column(table, column, dtype, comment, after)

Add comment for table

mysql.add_table_comment(table, comment)

Change column's attribute

mysql.change_column_attribute(table, column, dtype, notnull, comment)

Set primary key

mysql.add_primary_key(table, primary_key)

Truncate table's data, but keep the table structure

mysql.truncate_table(table)

Drop table

mysql.drop_table(table)

Drop column

mysql.drop_column(table, column)