MatrixOne从入门到实战04——MatrixOne的连接和建表( 二 ) _生活百科

执行结果

连接数据库...输入sql语句后并执行...id: 1名字: weder性别: manid: 2名字: tom性别: manid: 3名字: wederTom性别: man执行成功！

python代码

环境要求
- Python – one of the following:
  - python.org/" rel="external nofollow noreferrer">CPython : 3.6 and newer
  - PyPy : Latest 3.x version
安装PIP
python3 -m pip install PyMySQL
准备测试数据
CREATE DATABASE test;USEtest;CREATE TABLE `user` (`id` int(11) ,`user_name` varchar(255) ,`sex` varchar(255));insert into user(id,user_name,sex) values('1', 'weder', 'man'), ('2', 'tom', 'man'), ('3', 'wederTom', 'man');select * from user;+------+-----------+------+| id| user_name | sex|+------+-----------+------+|1 | weder| man||2 | tom| man||3 | wederTom| man|+------+-----------+------+
具备一款代码编辑工具 (pycharm) 或者直接在Linux 上编辑python文件
编辑代码
import pymysql.cursors# Connect to the databaseconnection = pymysql.connect(host='127.0.0.1',user='dump',password='111',database='test',cursorclass=pymysql.cursors.DictCursor)with connection:with connection.cursor() as cursor:# Create a new recordsql = "INSERT INTO user (id,user_name,sex) VALUES (%s, %s, %s)"cursor.execute(sql, ('4', 'Jerry', 'man'))# connection is not autocommit by default. So you must commit to save# your changes.connection.commit()with connection.cursor() as cursor:# Read a single recordsql = "SELECT id,user_name,sex FROM user WHERE id=%s"cursor.execute(sql, ('4',))result = cursor.fetchone()print(result)
执行结果
{'id': 4, 'user_name': 'Jerry', 'sex': 'man'}

建表目前MatrixOne没有特殊的建表语法，建表时，只需要按照下列语法进行即可
CREATE TABLE [IF NOT EXISTS] [db.]table_name(name1 type1,name2 type2,...)

示例
创建普通表
CREATE TABLE test(a int, b varchar(10));清空普通表
目前还不支持truncate语法删除普通表
drop table test;创建带有主键的表(注意：MatrixOne 表名和列名不区分大小写，大写的表名和列名都会转为小写)
CREATE TABLE Persons (ID int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Age int,PRIMARY KEY (ID));查看表：
MySQL [ssb]> desc persons;+-----------+---------+------+------+---------+---------+| Field| Type| Null | Key| Default | Comment |+-----------+---------+------+------+---------+---------+| id| INT| NO| PRI| NULL||| lastname| VARCHAR | NO|| NULL||| firstname | VARCHAR | YES|| NULL||| age| INT| YES|| NULL||+-----------+---------+------+------+---------+---------+4 rows in set (0.00 sec)主键表目前支持多个字段作为主键，如下面的建表语句：
MySQL [ssb]> CREATE TABLE Students (ID int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Age int,PRIMARY KEY (ID,LastName));Query OK, 0 rows affected (0.01 sec)查看表：
+-----------------------------+---------+------+------+---------+---------+| Field| Type| Null | Key| Default | Comment |+-----------------------------+---------+------+------+---------+---------+| id| INT| NO|| NULL||| lastname| VARCHAR | NO|| NULL||| firstname| VARCHAR | YES|| NULL||| age| INT| YES|| NULL||| __mo_cpkey_002id008lastname | VARCHAR | NO| PRI| NULL||+-----------------------------+---------+------+------+---------+---------+5 rows in set (0.03 sec)这里会发现有一个隐藏字段是id和lastname结合的一个varchar 类型的字段，用来当做主键。