· 7 years ago · Oct 08, 2018, 11:14 AM
1{
2 "cells": [
3 {
4 "cell_type": "code",
5 "execution_count": 113,
6 "metadata": {},
7 "outputs": [
8 {
9 "name": "stdout",
10 "output_type": "stream",
11 "text": [
12 "Database version : Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) \n",
13 "\tAug 22 2017 17:04:49 \n",
14 "\tCopyright (C) 2017 Microsoft Corporation\n",
15 "\tExpress Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 17134: )\n",
16 " \n"
17 ]
18 }
19 ],
20 "source": [
21 "import pymssql\n",
22 " \n",
23 "# 打开数æ®åº“连接\n",
24 "db = pymssql.connect(\n",
25 " host='localhost',\n",
26 " user='test',\n",
27 " password='test',\n",
28 " database='testdb'\n",
29 ")\n",
30 "\n",
31 "# 使用 cursor() æ–¹æ³•åˆ›å»ºä¸€ä¸ªæ¸¸æ ‡å¯¹è±¡ cursor\n",
32 "cursor = db.cursor()\n",
33 " \n",
34 "# 使用 execute() 方法执行 SQL 查询 \n",
35 "cursor.execute(\"SELECT @@VERSION AS 'SQL Server Version'\")\n",
36 " \n",
37 "# 使用 fetchone() 方法获å–å•æ¡æ•°æ®.\n",
38 "data = cursor.fetchone()\n",
39 "print (\"Database version : %s \" % data)\n",
40 " \n",
41 "# å…³é—æ•°æ®åº“连接\n",
42 "db.close()"
43 ]
44 },
45 {
46 "cell_type": "code",
47 "execution_count": 108,
48 "metadata": {},
49 "outputs": [],
50 "source": [
51 "import pymssql\n",
52 " \n",
53 "# 打开数æ®åº“连接\n",
54 "db = pymssql.connect(\n",
55 " host='localhost',\n",
56 " user='test',\n",
57 " password='test',\n",
58 " database='testdb'\n",
59 ")\n",
60 "\n",
61 "# 使用 cursor() æ–¹æ³•åˆ›å»ºä¸€ä¸ªæ¸¸æ ‡å¯¹è±¡ cursor\n",
62 "cursor = db.cursor()\n",
63 " \n",
64 "# 使用 execute() 方法执行 SQL，如果表å˜åœ¨åˆ™åˆ 除\n",
65 "cursor.execute(\"DROP TABLE IF EXISTS EMPLOYEE\")\n",
66 " \n",
67 "# 使用预处ç†è¯å¥åˆ›å»ºè¡¨\n",
68 "sql = \"\"\"CREATE TABLE EMPLOYEE (\n",
69 " FIRST_NAME CHAR(20) NOT NULL,\n",
70 " LAST_NAME CHAR(20),\n",
71 " AGE INT, \n",
72 " SEX CHAR(1),\n",
73 " INCOME FLOAT )\"\"\"\n",
74 " \n",
75 "cursor.execute(sql)\n",
76 " \n",
77 "# å…³é—æ•°æ®åº“连接\n",
78 "db.close()"
79 ]
80 },
81 {
82 "cell_type": "code",
83 "execution_count": 114,
84 "metadata": {},
85 "outputs": [],
86 "source": [
87 "import pymssql\n",
88 " \n",
89 "# 打开数æ®åº“连接\n",
90 "db = pymssql.connect(\n",
91 " host='localhost',\n",
92 " user='test',\n",
93 " password='test',\n",
94 " database='testdb'\n",
95 ")\n",
96 "\n",
97 "# 使用 cursor() æ–¹æ³•åˆ›å»ºä¸€ä¸ªæ¸¸æ ‡å¯¹è±¡ cursor\n",
98 "cursor = db.cursor()\n",
99 " \n",
100 "# SQL æ’å…¥è¯å¥\n",
101 "sql = \"\"\"INSERT INTO EMPLOYEE(FIRST_NAME,\n",
102 " LAST_NAME, AGE, SEX, INCOME)\n",
103 " VALUES ('Mac', 'Mohan', 20, 'M', 2000)\"\"\"\n",
104 "try:\n",
105 " # 执行sqlè¯å¥\n",
106 " cursor.execute(sql)\n",
107 " # æ交到数æ®åº“执行\n",
108 " db.commit()\n",
109 "except:\n",
110 " # 如果å‘生错误则回滚\n",
111 " db.rollback()\n",
112 " \n",
113 "# å…³é—æ•°æ®åº“连接\n",
114 "db.close()"
115 ]
116 },
117 {
118 "cell_type": "code",
119 "execution_count": 115,
120 "metadata": {},
121 "outputs": [
122 {
123 "name": "stdout",
124 "output_type": "stream",
125 "text": [
126 "fname=Mac,lname=Mohan,age=20,sex=M,income=2000\n"
127 ]
128 }
129 ],
130 "source": [
131 "import pymssql\n",
132 " \n",
133 "# 打开数æ®åº“连接\n",
134 "db = pymssql.connect(\n",
135 " host='localhost',\n",
136 " user='test',\n",
137 " password='test',\n",
138 " database='testdb'\n",
139 ")\n",
140 "\n",
141 "# 使用 cursor() æ–¹æ³•åˆ›å»ºä¸€ä¸ªæ¸¸æ ‡å¯¹è±¡ cursor\n",
142 "cursor = db.cursor()\n",
143 " \n",
144 "# SQL 查询è¯å¥\n",
145 "sql = \"SELECT * FROM EMPLOYEE \\\n",
146 " WHERE INCOME > '%d'\" % (1000)\n",
147 "try:\n",
148 " # 执行SQLè¯å¥\n",
149 " cursor.execute(sql)\n",
150 " # 获å–所有记录列表\n",
151 " results = cursor.fetchall()\n",
152 " for row in results:\n",
153 " fname = row[0]\n",
154 " lname = row[1]\n",
155 " age = row[2]\n",
156 " sex = row[3]\n",
157 " income = row[4]\n",
158 " # 打å°ç»“æžœ\n",
159 " print (\"fname=%s,lname=%s,age=%d,sex=%s,income=%d\" % \\\n",
160 " (fname, lname, age, sex, income ))\n",
161 " \n",
162 "except:\n",
163 " print (\"Error: unable to fetch data\")\n",
164 " \n",
165 "# å…³é—æ•°æ®åº“连接\n",
166 "db.close()"
167 ]
168 },
169 {
170 "cell_type": "code",
171 "execution_count": 116,
172 "metadata": {},
173 "outputs": [
174 {
175 "name": "stdout",
176 "output_type": "stream",
177 "text": [
178 "[('Mac', 'Mohan', 21, 'M', 2000.0)]\n"
179 ]
180 }
181 ],
182 "source": [
183 "import pymssql\n",
184 " \n",
185 "# 打开数æ®åº“连接\n",
186 "db = pymssql.connect(\n",
187 " host='localhost',\n",
188 " user='test',\n",
189 " password='test',\n",
190 " database='testdb'\n",
191 ")\n",
192 "\n",
193 "# 使用 cursor() æ–¹æ³•åˆ›å»ºä¸€ä¸ªæ¸¸æ ‡å¯¹è±¡ cursor\n",
194 "cursor = db.cursor()\n",
195 " \n",
196 "# SQL æ›´æ–°è¯å¥\n",
197 "sql = \"UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'\" % ('M')\n",
198 "try:\n",
199 " # 执行SQLè¯å¥\n",
200 " cursor.execute(sql)\n",
201 " # æ交到数æ®åº“执行\n",
202 " db.commit()\n",
203 " cursor.execute(\"SELECT * FROM EMPLOYEE\")\n",
204 " print(cursor.fetchall())\n",
205 "except:\n",
206 " # å‘生错误时回滚\n",
207 " db.rollback()\n",
208 " \n",
209 "# å…³é—æ•°æ®åº“连接\n",
210 "db.close()"
211 ]
212 },
213 {
214 "cell_type": "code",
215 "execution_count": 117,
216 "metadata": {},
217 "outputs": [],
218 "source": [
219 "import pymssql\n",
220 " \n",
221 "# 打开数æ®åº“连接\n",
222 "db = pymssql.connect(\n",
223 " host='localhost',\n",
224 " user='test',\n",
225 " password='test',\n",
226 " database='testdb'\n",
227 ")\n",
228 "\n",
229 "# 使用 cursor() æ–¹æ³•åˆ›å»ºä¸€ä¸ªæ¸¸æ ‡å¯¹è±¡ cursor\n",
230 "cursor = db.cursor()\n",
231 " \n",
232 "# SQL åˆ é™¤è¯å¥\n",
233 "sql = \"DELETE FROM EMPLOYEE WHERE AGE > '%d'\" % (20)\n",
234 "try:\n",
235 " # 执行SQLè¯å¥\n",
236 " cursor.execute(sql)\n",
237 " # æ交修改\n",
238 " db.commit()\n",
239 " print(cursor.fetchall())\n",
240 "except:\n",
241 " # å‘生错误时回滚\n",
242 " db.rollback()\n",
243 " \n",
244 "# å…³é—æ•°æ®åº“连接\n",
245 "db.close()"
246 ]
247 }
248 ],
249 "metadata": {
250 "kernelspec": {
251 "display_name": "Python 3",
252 "language": "python",
253 "name": "python3"
254 }
255 },
256 "nbformat": 4,
257 "nbformat_minor": 2
258}