import pyodbc
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};', autocommit=True)
cursor = cnxn.cursor()
cursor.execute("create database afc")
cursor.execute("create database hil")
cursor.close()
cnxn.close()
hil_sql_1 = """
create table auth_group
(
id int identity
primary key,
name nvarchar(150) not null
constraint auth_group_name_a6ea08ec_uniq
unique
);
create table auth_user
(
id int identity
primary key,
password nvarchar(128) not null,
last_login datetime2,
is_superuser bit not null,
username nvarchar(150) not null
constraint auth_user_username_6821ab7c_uniq
unique,
first_name nvarchar(30) not null,
last_name nvarchar(150) not null,
email nvarchar(254) not null,
is_staff bit not null,
is_active bit not null,
date_joined datetime2 not null
);
create table auth_user_groups
(
id int identity
primary key,
user_id int not null
constraint auth_user_groups_user_id_6a12ed8b_fk_auth_user_id
references auth_user,
group_id int not null
constraint auth_user_groups_group_id_97559544_fk_auth_group_id
references auth_group
);
create unique index auth_user_groups_user_id_group_id_94350c0c_uniq
on auth_user_groups (user_id, group_id)
where [user_id] IS NOT NULL AND [group_id] IS NOT NULL;
create index auth_user_groups_group_id_97559544
on auth_user_groups (group_id);
create index auth_user_groups_user_id_6a12ed8b
on auth_user_groups (user_id);
create table django_content_type
(
id int identity
primary key,
app_label nvarchar(100) not null,
model nvarchar(100) not null
);
create table auth_permission
(
id int identity
primary key,
name nvarchar(255) not null,
content_type_id int not null
constraint auth_permission_content_type_id_2f476e4b_fk_django_content_type_id
references django_content_type,
codename nvarchar(100) not null
);
create table auth_group_permissions
(
id int identity
primary key,
group_id int not null
constraint auth_group_permissions_group_id_b120cbf9_fk_auth_group_id
references auth_group,
permission_id int not null
constraint auth_group_permissions_permission_id_84c5c92e_fk_auth_permission_id
references auth_permission
);
create index auth_group_permissions_permission_id_84c5c92e
on auth_group_permissions (permission_id);
create index auth_group_permissions_group_id_b120cbf9
on auth_group_permissions (group_id);
create unique index auth_group_permissions_group_id_permission_id_0cd325b0_uniq
on auth_group_permissions (group_id, permission_id)
where [group_id] IS NOT NULL AND [permission_id] IS NOT NULL;
create index auth_permission_content_type_id_2f476e4b
on auth_permission (content_type_id);
create unique index auth_permission_content_type_id_codename_01ab375a_uniq
on auth_permission (content_type_id, codename)
where [content_type_id] IS NOT NULL AND [codename] IS NOT NULL;
create table auth_user_user_permissions
(
id int identity
primary key,
user_id int not null
constraint auth_user_user_permissions_user_id_a95ead1b_fk_auth_user_id
references auth_user,
permission_id int not null
constraint auth_user_user_permissions_permission_id_1fbb5f2c_fk_auth_permission_id
references auth_permission
);
create index auth_user_user_permissions_permission_id_1fbb5f2c
on auth_user_user_permissions (permission_id);
create index auth_user_user_permissions_user_id_a95ead1b
on auth_user_user_permissions (user_id);
create unique index auth_user_user_permissions_user_id_permission_id_14a6b632_uniq
on auth_user_user_permissions (user_id, permission_id)
where [user_id] IS NOT NULL AND [permission_id] IS NOT NULL;
create table django_admin_log
(
id int identity
primary key,
action_time datetime2 not null,
object_id nvarchar(max),
object_repr nvarchar(200) not null,
action_flag smallint not null
constraint django_admin_log_action_flag_a8637d59_check
check ([action_flag] >= 0),
change_message nvarchar(max) not null,
content_type_id int
constraint django_admin_log_content_type_id_c4bce8eb_fk_django_content_type_id
references django_content_type,
user_id int not null
constraint django_admin_log_user_id_c564eba6_fk_auth_user_id
references auth_user
);
create index django_admin_log_content_type_id_c4bce8eb
on django_admin_log (content_type_id);
create index django_admin_log_user_id_c564eba6
on django_admin_log (user_id);
create unique index django_content_type_app_label_model_76bd3d3b_uniq
on django_content_type (app_label, model)
where [app_label] IS NOT NULL AND [model] IS NOT NULL;
create table django_migrations
(
id int identity
primary key,
app nvarchar(255) not null,
name nvarchar(255) not null,
applied datetime2 not null
);
create table django_session
(
session_key nvarchar(40) not null
primary key,
session_data nvarchar(max) not null,
expire_date datetime2 not null
);
create index django_session_expire_date_a5c62663
on django_session (expire_date);
create table oauth2_provider_application
(
id bigint identity
primary key,
client_id nvarchar(100) not null
unique,
redirect_uris nvarchar(max) not null,
client_type nvarchar(32) not null,
authorization_grant_type nvarchar(32) not null,
client_secret nvarchar(255) not null,
name nvarchar(255) not null,
user_id int
constraint oauth2_provider_application_user_id_79829054_fk_auth_user_id
references auth_user,
skip_authorization bit not null,
created datetime2 not null,
updated datetime2 not null
);
create table oauth2_provider_accesstoken
(
id bigint identity
primary key,
token nvarchar(255) not null
unique,
expires datetime2 not null,
scope nvarchar(max) not null,
application_id bigint
constraint oauth2_provider_accesstoken_application_id_b22886e1_fk_oauth2_provider_application_id
references oauth2_provider_application,
user_id int
constraint oauth2_provider_accesstoken_user_id_6e4c9a65_fk_auth_user_id
references auth_user,
created datetime2 not null,
updated datetime2 not null,
source_refresh_token_id bigint
);
create index oauth2_provider_accesstoken_user_id_6e4c9a65
on oauth2_provider_accesstoken (user_id);
create index oauth2_provider_accesstoken_application_id_b22886e1
on oauth2_provider_accesstoken (application_id);
create unique index oauth2_provider_accesstoken_source_refresh_token_id_e66fbc72_uniq
on oauth2_provider_accesstoken (source_refresh_token_id)
where [source_refresh_token_id] IS NOT NULL;
create index oauth2_provider_application_client_secret_53133678
on oauth2_provider_application (client_secret);
create index oauth2_provider_application_user_id_79829054
on oauth2_provider_application (user_id);
create table oauth2_provider_grant
(
id bigint identity
primary key,
code nvarchar(255) not null
unique,
expires datetime2 not null,
redirect_uri nvarchar(255) not null,
scope nvarchar(max) not null,
application_id bigint not null
constraint oauth2_provider_grant_application_id_81923564_fk_oauth2_provider_application_id
references oauth2_provider_application,
user_id int not null
constraint oauth2_provider_grant_user_id_e8f62af8_fk_auth_user_id
references auth_user,
created datetime2 not null,
updated datetime2 not null,
code_challenge nvarchar(128) not null,
code_challenge_method nvarchar(10) not null
);
create index oauth2_provider_grant_user_id_e8f62af8
on oauth2_provider_grant (user_id);
create index oauth2_provider_grant_application_id_81923564
on oauth2_provider_grant (application_id);
"""
hil_sql_2 = """
create table oauth2_provider_refreshtoken
(
id bigint identity
primary key,
token nvarchar(255) not null,
access_token_id bigint
constraint oauth2_provider_refreshtoken_access_token_id_775e84e8_fk_oauth2_provider_accesstoken_id
references oauth2_provider_accesstoken,
application_id bigint not null
constraint oauth2_provider_refreshtoken_application_id_2d1c311b_fk_oauth2_provider_application_id
references oauth2_provider_application,
user_id int not null
constraint oauth2_provider_refreshtoken_user_id_da837fce_fk_auth_user_id
references auth_user,
created datetime2 not null,
updated datetime2 not null,
revoked datetime2
);
alter table oauth2_provider_accesstoken
add constraint oauth2_provider_accesstoken_source_refresh_token_id_e66fbc72_fk_oauth2_provider_refreshtoken_id
foreign key (source_refresh_token_id) references oauth2_provider_refreshtoken;
create unique index oauth2_provider_refreshtoken_token_revoked_af8a5134_uniq
on oauth2_provider_refreshtoken (token, revoked)
where [token] IS NOT NULL AND [revoked] IS NOT NULL;
create index oauth2_provider_refreshtoken_user_id_da837fce
on oauth2_provider_refreshtoken (user_id);
create unique index oauth2_provider_refreshtoken_access_token_id_775e84e8_uniq
on oauth2_provider_refreshtoken (access_token_id)
where [access_token_id] IS NOT NULL;
create index oauth2_provider_refreshtoken_application_id_2d1c311b
on oauth2_provider_refreshtoken (application_id);
create table hil_doc
(
id int identity
primary key,
metadata_version_id nvarchar(64) not null,
application_id nvarchar(64) not null,
status tinyint default 0 not null,
retry_step tinyint,
retry_times tinyint default 0 not null,
is_retry bit default 0 not null,
document_name nvarchar(255) not null,
document_scheme nvarchar(64) not null,
data_source nvarchar(64) not null,
upload_finish_time datetime,
create_time datetime not null,
start_time datetime,
end_time datetime,
duration smallint,
bs_count smallint default 0 not null,
mvi_count smallint default 0 not null,
ic_count smallint default 0 not null,
rp_count smallint default 0 not null,
bc_count smallint default 0 not null,
bl_count smallint default 0 not null,
uci_count smallint default 0 not null,
eep_count smallint default 0 not null,
dl_count smallint default 0 not null,
pp_count smallint default 0 not null,
mvc_count smallint default 0 not null,
vat_count smallint default 0 not null
);
create index hil_doc_upload_finish_time_index
on hil_doc (upload_finish_time);
create index hil_doc_create_time_index
on hil_doc (create_time);
create index hil_doc_application_id_status_index
on hil_doc (application_id, status);
create index hil_doc_start_time_end_time_index
on hil_doc (start_time, end_time);
"""
afc_sql = """
create table upload_doc_records
(
id int identity
primary key,
metadata_version_id nvarchar(64) not null,
application_id nvarchar(64) not null,
main_applicant nvarchar(16) not null,
co_applicant nvarchar(16) not null,
guarantor_1 nvarchar(16) not null,
guarantor_2 nvarchar(16) not null,
document_name nvarchar(255) not null,
document_scheme nvarchar(64) not null,
business_type nvarchar(64) not null,
data_source nvarchar(64) not null,
upload_finish_time datetime,
update_time datetime not null,
create_time datetime not null
);
create unique index upload_doc_records_metadata_version_id_uindex
on upload_doc_records (metadata_version_id);
create table keywords
(
id int identity primary key,
keyword nvarchar(64) not null,
type tinyint default 0 not null,
on_off bit default 1 not null,
update_time datetime not null,
create_time datetime not null
);
create table configs
(
id int identity primary key,
value nvarchar(255) not null,
comment nvarchar(64) not null,
);
create table priority_application
(
id int identity
primary key,
application_id nvarchar(64) not null,
on_off bit default 1 not null,
update_time datetime not null,
create_time datetime not null
);
create index priority_application_application_id_on_off_index
on priority_application (application_id, on_off);
create table gcap_records
(
id int identity primary key,
status int,
rating int,
entity nvarchar(128),
application_id nvarchar(64) not null,
application_version int,
intermediate_decision nvarchar(128),
submit_datetime datetime,
update_time datetime not null,
create_time datetime not null
);
create table afc_doc
(
id int identity
primary key,
metadata_version_id nvarchar(64) not null,
application_id nvarchar(64) not null,
status tinyint default 0 not null,
retry_step tinyint,
retry_times tinyint default 0 not null,
is_retry bit default 0 not null,
document_name nvarchar(255) not null,
document_scheme nvarchar(64) not null,
data_source nvarchar(64) not null,
upload_finish_time datetime,
create_time datetime not null,
start_time datetime,
end_time datetime,
duration smallint,
bs_count smallint default 0 not null,
mvi_count smallint default 0 not null,
ic_count smallint default 0 not null,
rp_count smallint default 0 not null,
bc_count smallint default 0 not null,
bl_count smallint default 0 not null,
uci_count smallint default 0 not null,
eep_count smallint default 0 not null,
dl_count smallint default 0 not null,
pp_count smallint default 0 not null,
mvc_count smallint default 0 not null,
vat_count smallint default 0 not null
);
create index afc_doc_upload_finish_time_index
on afc_doc (upload_finish_time);
create index afc_doc_create_time_index
on afc_doc (create_time);
create index afc_doc_application_id_status_index
on afc_doc (application_id, status);
create index afc_doc_start_time_end_time_index
on afc_doc (start_time, end_time);
"""
keywords_sql = """
INSERT INTO keywords (keyword, type, update_time, create_time) VALUES
(N'利息', 0, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'结息', 0, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'工资', 1, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'代发', 1, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'养老保险', 1, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'奖金', 1, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'理财', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'赎回', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'微信', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'支付宝', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'财付通', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'放款', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'还款', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'贷款', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'银证转账', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'银行卡户名（姓名）', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'转账/转账', 3, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'商品/线下', 3, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'转账', 3, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
(N'二维码收款', 3, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000');
"""
hil_cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};', autocommit=True)
hil_cursor = hil_cnxn.cursor()
hil_cursor.execute(hil_sql_1)
hil_cursor.execute(hil_sql_2)
hil_cursor.close()
hil_cnxn.close()
afc_cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};', autocommit=True)
afc_cursor = afc_cnxn.cursor()
afc_cursor.execute(afc_sql)
afc_cursor.execute(keywords_sql)
afc_cursor.close()
afc_cnxn.close()