Seafile 批量导入账号名称及电话号码

前言

seafile 可以使用 CSV 导入用户名密码来新建用户,但是只能导入用户名和密码,名称则无法导入,但是用户名只能用邮箱这点不是很方便,故此需要找到一个批量导入账号名称的办法.

数据库结构

seafile 的账号和密码均存储在 mysql 的 ccnet-db 数据库内,但账号的详细信息例如名称、电话、部门这些信息是存储在 seahub-db 数据库里面.

MariaDB [(none)]> use seahub-db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [seahub-db]> show tables;
+-------------------------------------------+
| Tables_in_seahub-db                       |
+-------------------------------------------+
| api2_token                                |
| api2_tokenv2                              |
| avatar_avatar                             |
| avatar_groupavatar                        |
| base_clientlogintoken                     |
| base_commandslastcheck                    |
| base_devicetoken                          |
| base_filecomment                          |
| base_filediscuss                          |
| base_groupenabledmodule                   |
| base_innerpubmsg                          |
| base_innerpubmsgreply                     |
| base_userenabledmodule                    |
| base_userlastlogin                        |
| base_userstarredfiles                     |
| captcha_captchastore                      |
| constance_config                          |
| contacts_contact                          |
| django_content_type                       |
| django_migrations                         |
| django_session                            |
| group_groupmessage                        |
| group_messageattachment                   |
| group_messagereply                        |
| group_publicgroup                         |
| institutions_institution                  |
| institutions_institutionadmin             |
| invitations_invitation                    |
| message_usermessage                       |
| message_usermsgattachment                 |
| message_usermsglastcheck                  |
| notifications_notification                |
| notifications_usernotification            |
| options_useroptions                       |
| organizations_orgmemberquota              |
| post_office_attachment                    |
| post_office_attachment_emails             |
| post_office_email                         |
| post_office_emailtemplate                 |
| post_office_log                           |
| profile_detailedprofile                   |
| profile_profile                           |
| registration_registrationprofile          |
| share_anonymousshare                      |
| share_fileshare                           |
| share_orgfileshare                        |
| share_privatefiledirshare                 |
| share_uploadlinkshare                     |
| sysadmin_extra_userloginlog               |
| termsandconditions_termsandconditions     |
| termsandconditions_usertermsandconditions |
| two_factor_phonedevice                    |
| two_factor_staticdevice                   |
| two_factor_statictoken                    |
| two_factor_totpdevice                     |
| wiki_groupwiki                            |
| wiki_personalwiki                         |
+-------------------------------------------+
57 rows in set (0.00 sec)

其中profile_profile是存放个人资料的表,而profile_detailedprofile是存放电话号码的表格,他们均以一个字段user(用户名邮箱)来做标识达成一一对应.

MariaDB [(none)]> use seahub-db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [seahub-db]> desc profile_profile;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| user          | varchar(254) | NO   | UNI | NULL    |                |
| nickname      | varchar(64)  | NO   |     | NULL    |                |
| intro         | longtext     | NO   |     | NULL    |                |
| lang_code     | longtext     | YES  |     | NULL    |                |
| login_id      | varchar(225) | YES  | UNI | NULL    |                |
| contact_email | varchar(225) | YES  | MUL | NULL    |                |
| institution   | varchar(225) | YES  | MUL | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
MariaDB [seahub-db]> desc profile_detailedprofile;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| user       | varchar(255) | NO   | MUL | NULL    |                |
| department | varchar(512) | NO   |     | NULL    |                |
| telephone  | varchar(100) | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

那么我们只需要将我们的信息导入到这两个表就可以了

导入数据

比如现在我们需要导入用户名:zhangsan@domain.com;名称:张三;电话:12345678.
名称是在profile_profile里面字段是nickname,而电话是在profile_detailedprofile里面,字段是telephone.

# 名称导入
insert into profile_profile (user,nickname) values ('zhangsan@domain.com','张三')
# 电话号码导入
insert into profile_detailedprofile (user,telephone) values ('zhangsan@domain.com','12345678')

如果多条数据导入可以这样写

# 名称导入
insert into profile_profile (user,nickname) values ('zhangsan@domain.com','张三'),('lisi@domain.com','李四')
# 电话号码导入
insert into profile_detailedprofile (user,telephone) values ('zhangsan@domain.com','12345678'),('lisi@domain.com','87654321')

导入完成后打开用户页面就能见到用户名称和电话号码,其他字段的属性导入也是差不多

参考文献

mysql批量插入(insert)与批量更新(update)的例子

修改记录

2016年11月7日:经 Seafile 中文论坛兄弟(skipto)提醒,数据库 id 字段是自动添加,无需导入,把 id 导入字段删除.