Guest User

Untitled

a guest
Oct 8th, 2018
127
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 6.40 KB | None | 0 0
  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. }
Add Comment
Please, Sign In to add comment