如何使用 Python 与 Databend 交互

准备工作

• Databend : 确保Databend 正在运行并可访问，查看 部署和管理 Databend。
• 如何创建用户
• 如何授予用户权限

创建 Databend 用户

mysql -h127.0.0.1 -uroot -P3307

创建用户

CREATE USER user1 IDENTIFIED BY 'abc123';

授予权限

Grants ALL privileges to the user user1:

GRANT ALL ON *.* TO user1;

Python

本指南将会展示如何使用 Python 连接 Databend 并执行查询。

我们将创建一个名为 books 的表并插入一行数据，然后进行查询。

使用 mysql.connector

pip install mysql-connector-python

main.py

#!/usr/bin/env python3
import mysql.connector

cnx = mysql.connector.connect(user='user1', password='abc123',
                              host='127.0.0.1',
                              port = 3307,
                              database='')

# Create database, table.
cursor = cnx.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS book_db")
cursor.execute("USE book_db")
cursor.execute("CREATE TABLE IF NOT EXISTS books(title VARCHAR, author VARCHAR, date VARCHAR)")

# Insert new book. 
add_book = ("INSERT INTO books "
               "(title, author, date) "
               "VALUES (%s, %s, %s)")
data_book = ('mybook', 'author', '2022')
cursor.execute(add_book, data_book)

# Query.
query = ("SELECT * FROM books")
cursor.execute(query)
for (title, author, date) in cursor:
  print("{} {} {}".format(title, author, date))

cursor.close()
cnx.close()

运行 python main.py ：

mybook author 2022

使用 sqlalchemy

pip install sqlalchemy

main.py

#!/usr/bin/env python3

import sqlalchemy

engine = sqlalchemy.create_engine("mysql+pymysql://user1:abc123@localhost:3307/")
conn = engine.connect()
conn.execute("CREATE DATABASE IF NOT EXISTS book_db")
conn.execute("USE book_db")
conn.execute("CREATE TABLE IF NOT EXISTS books(title VARCHAR, author VARCHAR, date VARCHAR)")
conn.execute("INSERT INTO books VALUES('mybook', 'author', '2022')")
results = conn.execute('SELECT * FROM books').fetchall()
for result in results:
    print(result)
conn.execute('drop database book_db')

运行 python main.py ：

('mybook', 'author', '2022')