{ "cells": [ { "cell_type": "code", "execution_count": 70, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "('_temporary_and_external_tables',)\n", "('default',)\n", "('stock',)\n", "('system',)\n" ] } ], "source": [ "from clickhouse_driver import Client\n", "#client = Client(host='localhost',password=\"click123!\",settings={\"use_numpy\":True})\n", "client = Client(host='localhost',password=\"click123!\")\n", "databases = client.execute(\"SHOW DATABASES\")\n", "for db in databases:\n", " print(db)\n" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "client.execute(\"create database if not exists stock\")" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "client.execute(\"use stock\")" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[('stock_cn',)]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "client.execute(\"show tables\")" ] }, { "cell_type": "code", "execution_count": 50, "metadata": { "scrolled": false }, "outputs": [ { "data": { "text/plain": [ "[]" ] }, "execution_count": 50, "metadata": {}, "output_type": "execute_result" } ], "source": [ "client.execute(\"DROP TABLE if exists stock_cn\")\n", "client.execute(\"\"\"CREATE TABLE if not exists stock_cn (\n", " date Date,\n", " code String,\n", " open Float32,\n", " high Float32,\n", " low Float32,\n", " close Float32,\n", " preclose Float32,\n", " volume UInt32,\n", " amount Float32,\n", " adjustflag UInt32,\n", " turn Float32,\n", " tradestatus UInt32,\n", " pctChg Float32,\n", " isST UInt32\n", " ) ENGINE=MergeTree()\n", " PARTITION by toYYYYMM(date)\n", " ORDER by date\"\"\")" ] }, { "cell_type": "code", "execution_count": 51, "metadata": { "scrolled": false }, "outputs": [ { "data": { "text/plain": [ "[]" ] }, "execution_count": 51, "metadata": {}, "output_type": "execute_result" } ], "source": [ "client.execute(\"select * from stock_cn\")" ] }, { "cell_type": "code", "execution_count": 52, "metadata": { "scrolled": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "date\n", "code\n", "open\n", "high\n", "low\n", "close\n", "preclose\n", "volume\n", "amount\n", "adjustflag\n", "turn\n", "tradestatus\n", "pctChg\n", "isST\n" ] } ], "source": [ "import json\n", "describ = client.execute(\"describe stock_cn\")\n", "for t in describ:\n", " print(t[0])" ] }, { "cell_type": "code", "execution_count": 55, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[(datetime.date(2013, 1, 4), 'sz.000717', 2.3299999237060547, 2.369999885559082, 2.309999942779541, 2.3299999237060547, 2.299999952316284, 17835423, 41668272.0, 3, 1.5811150074005127, 1, 1.3042999505996704, 0), (datetime.date(2013, 1, 7), 'sz.000717', 2.319999933242798, 2.3299999237060547, 2.2799999713897705, 2.299999952316284, 2.3299999237060547, 9814285, 22620062.0, 3, 0.8700389862060547, 1, -1.287600040435791, 0), (datetime.date(2013, 1, 8), 'sz.000717', 2.299999952316284, 2.309999942779541, 2.2799999713897705, 2.2899999618530273, 2.299999952316284, 8411073, 19301242.0, 3, 0.7456439733505249, 1, -0.43479999899864197, 0), (datetime.date(2013, 1, 9), 'sz.000717', 2.2899999618530273, 2.309999942779541, 2.259999990463257, 2.2799999713897705, 2.2899999618530273, 9934514, 22695886.0, 3, 0.8806970119476318, 1, -0.4366999864578247, 0), (datetime.date(2013, 1, 10), 'sz.000717', 2.2899999618530273, 2.359999895095825, 2.2799999713897705, 2.309999942779541, 2.2799999713897705, 22257232, 51674572.0, 3, 1.9731099605560303, 1, 1.3157999515533447, 0)]\n" ] } ], "source": [ "result = client.execute(\"select * from stock_cn where code=%(code)s limit 5\", {\"code\":\"sz.000717\"})\n", "print(result)" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n" ] } ], "source": [ "print(type(result))" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n" ] } ], "source": [ "print(type(result[0]))" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2910\n" ] } ], "source": [ "print(len(result))" ] }, { "cell_type": "code", "execution_count": 79, "metadata": {}, "outputs": [], "source": [ "result = client.query_dataframe(\"select * from stock_cn where code=%(code)s order by date\", {\"code\":\"sh.600050\"})" ] }, { "cell_type": "code", "execution_count": 78, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
datecodeopenhighlowclosepreclosevolumeamountadjustflagturntradestatuspctChgisST
02006-01-04sh.6000502.802.892.782.862.80121344910346683264.031.86684512.1428550
12006-01-05sh.6000502.862.922.852.902.86110247291319527360.031.69611211.3986080
22006-01-06sh.6000502.902.942.872.912.9096796753282116896.031.48918110.3448270
32006-01-09sh.6000502.912.932.882.912.9183990367244001568.031.29215910.0000000
42006-01-10sh.6000502.912.912.852.892.9140008102115096856.030.6155091-0.6872840
.............................................
36382020-12-21sh.6000504.684.694.624.644.6979328390368504352.030.2598001-1.0661000
36392020-12-22sh.6000504.624.644.534.544.64113513761520809376.030.3717001-2.1552000
36402020-12-23sh.6000504.534.554.494.514.5493335737421419616.030.3056001-0.6608000
36412020-12-24sh.6000504.504.514.444.454.5176268133340610144.030.2498001-1.3304000
36422020-12-25sh.6000504.454.494.434.494.4544819735200330960.030.14680010.8989000
\n", "

3643 rows × 14 columns

\n", "
" ], "text/plain": [ " date code open high low close preclose volume \\\n", "0 2006-01-04 sh.600050 2.80 2.89 2.78 2.86 2.80 121344910 \n", "1 2006-01-05 sh.600050 2.86 2.92 2.85 2.90 2.86 110247291 \n", "2 2006-01-06 sh.600050 2.90 2.94 2.87 2.91 2.90 96796753 \n", "3 2006-01-09 sh.600050 2.91 2.93 2.88 2.91 2.91 83990367 \n", "4 2006-01-10 sh.600050 2.91 2.91 2.85 2.89 2.91 40008102 \n", "... ... ... ... ... ... ... ... ... \n", "3638 2020-12-21 sh.600050 4.68 4.69 4.62 4.64 4.69 79328390 \n", "3639 2020-12-22 sh.600050 4.62 4.64 4.53 4.54 4.64 113513761 \n", "3640 2020-12-23 sh.600050 4.53 4.55 4.49 4.51 4.54 93335737 \n", "3641 2020-12-24 sh.600050 4.50 4.51 4.44 4.45 4.51 76268133 \n", "3642 2020-12-25 sh.600050 4.45 4.49 4.43 4.49 4.45 44819735 \n", "\n", " amount adjustflag turn tradestatus pctChg isST \n", "0 346683264.0 3 1.866845 1 2.142855 0 \n", "1 319527360.0 3 1.696112 1 1.398608 0 \n", "2 282116896.0 3 1.489181 1 0.344827 0 \n", "3 244001568.0 3 1.292159 1 0.000000 0 \n", "4 115096856.0 3 0.615509 1 -0.687284 0 \n", "... ... ... ... ... ... ... \n", "3638 368504352.0 3 0.259800 1 -1.066100 0 \n", "3639 520809376.0 3 0.371700 1 -2.155200 0 \n", "3640 421419616.0 3 0.305600 1 -0.660800 0 \n", "3641 340610144.0 3 0.249800 1 -1.330400 0 \n", "3642 200330960.0 3 0.146800 1 0.898900 0 \n", "\n", "[3643 rows x 14 columns]" ] }, "execution_count": 78, "metadata": {}, "output_type": "execute_result" } ], "source": [ "result" ] }, { "cell_type": "code", "execution_count": 66, "metadata": { "scrolled": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
datecodeopenhigh
02006-01-04sz.0007172.392.41
12006-01-05sz.0007172.402.48
22006-01-06sz.0007172.472.49
32006-01-09sz.0007172.472.53
42006-01-10sz.0007172.532.53
...............
33112020-12-21sz.0007174.644.67
33122020-12-22sz.0007174.644.66
33132020-12-23sz.0007174.354.42
33142020-12-24sz.0007174.354.36
33152020-12-25sz.0007174.224.45
\n", "

3643 rows × 4 columns

\n", "
" ], "text/plain": [ " date code open high\n", "0 2006-01-04 sz.000717 2.39 2.41\n", "1 2006-01-05 sz.000717 2.40 2.48\n", "2 2006-01-06 sz.000717 2.47 2.49\n", "3 2006-01-09 sz.000717 2.47 2.53\n", "4 2006-01-10 sz.000717 2.53 2.53\n", "... ... ... ... ...\n", "3311 2020-12-21 sz.000717 4.64 4.67\n", "3312 2020-12-22 sz.000717 4.64 4.66\n", "3313 2020-12-23 sz.000717 4.35 4.42\n", "3314 2020-12-24 sz.000717 4.35 4.36\n", "3315 2020-12-25 sz.000717 4.22 4.45\n", "\n", "[3643 rows x 4 columns]" ] }, "execution_count": 66, "metadata": {}, "output_type": "execute_result" } ], "source": [ "result = client.query_dataframe(\"select date,code,open,high from stock_cn where code=%(code)s\", {\"code\":\"sz.000717\"})\n", "result = result.sort_values(by='date')\n", "result" ] }, { "cell_type": "code", "execution_count": 65, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
datecodeopenhigh
642006-01-04sz.0007172.392.41
652006-01-05sz.0007172.402.48
662006-01-06sz.0007172.472.49
672006-01-09sz.0007172.472.53
682006-01-10sz.0007172.532.53
\n", "
" ], "text/plain": [ " date code open high\n", "64 2006-01-04 sz.000717 2.39 2.41\n", "65 2006-01-05 sz.000717 2.40 2.48\n", "66 2006-01-06 sz.000717 2.47 2.49\n", "67 2006-01-09 sz.000717 2.47 2.53\n", "68 2006-01-10 sz.000717 2.53 2.53" ] }, "execution_count": 65, "metadata": {}, "output_type": "execute_result" } ], "source": [ "result.head()" ] }, { "cell_type": "code", "execution_count": 80, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "RangeIndex: 3643 entries, 0 to 3642\n", "Data columns (total 14 columns):\n", " # Column Non-Null Count Dtype \n", "--- ------ -------------- ----- \n", " 0 date 3643 non-null object \n", " 1 code 3643 non-null object \n", " 2 open 3643 non-null float64\n", " 3 high 3643 non-null float64\n", " 4 low 3643 non-null float64\n", " 5 close 3643 non-null float64\n", " 6 preclose 3643 non-null float64\n", " 7 volume 3643 non-null int64 \n", " 8 amount 3643 non-null float64\n", " 9 adjustflag 3643 non-null int64 \n", " 10 turn 3643 non-null float64\n", " 11 tradestatus 3643 non-null int64 \n", " 12 pctChg 3643 non-null float64\n", " 13 isST 3643 non-null int64 \n", "dtypes: float64(8), int64(4), object(2)\n", "memory usage: 398.6+ KB\n" ] } ], "source": [ "result.info()" ] }, { "cell_type": "code", "execution_count": 81, "metadata": {}, "outputs": [], "source": [ "result['sma_5_open'] = result['open'].rolling(5).mean()" ] }, { "cell_type": "code", "execution_count": 84, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 NaN\n", "1 NaN\n", "2 NaN\n", "3 NaN\n", "4 2.876\n", " ... \n", "3638 4.652\n", "3639 4.644\n", "3640 4.620\n", "3641 4.596\n", "3642 4.556\n", "Name: sma_5_open, Length: 3643, dtype: float64" ] }, "execution_count": 84, "metadata": {}, "output_type": "execute_result" } ], "source": [ "result['sma_5_open']" ] }, { "cell_type": "code", "execution_count": 85, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "4 2.876\n", "5 2.894\n", "6 2.888\n", "7 2.878\n", "8 2.864\n", " ... \n", "3638 4.652\n", "3639 4.644\n", "3640 4.620\n", "3641 4.596\n", "3642 4.556\n", "Name: sma_5_open, Length: 3639, dtype: float64" ] }, "execution_count": 85, "metadata": {}, "output_type": "execute_result" } ], "source": [ "result['sma_5_open'].dropna()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.1" } }, "nbformat": 4, "nbformat_minor": 4 }