Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- {
- "cells": [
- {
- "cell_type": "code",
- "execution_count": 113,
- "metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "Database version : Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) \n",
- "\tAug 22 2017 17:04:49 \n",
- "\tCopyright (C) 2017 Microsoft Corporation\n",
- "\tExpress Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 17134: )\n",
- " \n"
- ]
- }
- ],
- "source": [
- "import pymssql\n",
- " \n",
- "# 打开数据库连接\n",
- "db = pymssql.connect(\n",
- " host='localhost',\n",
- " user='test',\n",
- " password='test',\n",
- " database='testdb'\n",
- ")\n",
- "\n",
- "# 使用 cursor() 方法创建一个游标对象 cursor\n",
- "cursor = db.cursor()\n",
- " \n",
- "# 使用 execute() 方法执行 SQL 查询 \n",
- "cursor.execute(\"SELECT @@VERSION AS 'SQL Server Version'\")\n",
- " \n",
- "# 使用 fetchone() 方法获取单条数据.\n",
- "data = cursor.fetchone()\n",
- "print (\"Database version : %s \" % data)\n",
- " \n",
- "# 关闭数据库连接\n",
- "db.close()"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 108,
- "metadata": {},
- "outputs": [],
- "source": [
- "import pymssql\n",
- " \n",
- "# 打开数据库连接\n",
- "db = pymssql.connect(\n",
- " host='localhost',\n",
- " user='test',\n",
- " password='test',\n",
- " database='testdb'\n",
- ")\n",
- "\n",
- "# 使用 cursor() 方法创建一个游标对象 cursor\n",
- "cursor = db.cursor()\n",
- " \n",
- "# 使用 execute() 方法执行 SQL,如果表存在则删除\n",
- "cursor.execute(\"DROP TABLE IF EXISTS EMPLOYEE\")\n",
- " \n",
- "# 使用预处理语句创建表\n",
- "sql = \"\"\"CREATE TABLE EMPLOYEE (\n",
- " FIRST_NAME CHAR(20) NOT NULL,\n",
- " LAST_NAME CHAR(20),\n",
- " AGE INT, \n",
- " SEX CHAR(1),\n",
- " INCOME FLOAT )\"\"\"\n",
- " \n",
- "cursor.execute(sql)\n",
- " \n",
- "# 关闭数据库连接\n",
- "db.close()"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 114,
- "metadata": {},
- "outputs": [],
- "source": [
- "import pymssql\n",
- " \n",
- "# 打开数据库连接\n",
- "db = pymssql.connect(\n",
- " host='localhost',\n",
- " user='test',\n",
- " password='test',\n",
- " database='testdb'\n",
- ")\n",
- "\n",
- "# 使用 cursor() 方法创建一个游标对象 cursor\n",
- "cursor = db.cursor()\n",
- " \n",
- "# SQL 插入语句\n",
- "sql = \"\"\"INSERT INTO EMPLOYEE(FIRST_NAME,\n",
- " LAST_NAME, AGE, SEX, INCOME)\n",
- " VALUES ('Mac', 'Mohan', 20, 'M', 2000)\"\"\"\n",
- "try:\n",
- " # 执行sql语句\n",
- " cursor.execute(sql)\n",
- " # 提交到数据库执行\n",
- " db.commit()\n",
- "except:\n",
- " # 如果发生错误则回滚\n",
- " db.rollback()\n",
- " \n",
- "# 关闭数据库连接\n",
- "db.close()"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 115,
- "metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "fname=Mac,lname=Mohan,age=20,sex=M,income=2000\n"
- ]
- }
- ],
- "source": [
- "import pymssql\n",
- " \n",
- "# 打开数据库连接\n",
- "db = pymssql.connect(\n",
- " host='localhost',\n",
- " user='test',\n",
- " password='test',\n",
- " database='testdb'\n",
- ")\n",
- "\n",
- "# 使用 cursor() 方法创建一个游标对象 cursor\n",
- "cursor = db.cursor()\n",
- " \n",
- "# SQL 查询语句\n",
- "sql = \"SELECT * FROM EMPLOYEE \\\n",
- " WHERE INCOME > '%d'\" % (1000)\n",
- "try:\n",
- " # 执行SQL语句\n",
- " cursor.execute(sql)\n",
- " # 获取所有记录列表\n",
- " results = cursor.fetchall()\n",
- " for row in results:\n",
- " fname = row[0]\n",
- " lname = row[1]\n",
- " age = row[2]\n",
- " sex = row[3]\n",
- " income = row[4]\n",
- " # 打印结果\n",
- " print (\"fname=%s,lname=%s,age=%d,sex=%s,income=%d\" % \\\n",
- " (fname, lname, age, sex, income ))\n",
- " \n",
- "except:\n",
- " print (\"Error: unable to fetch data\")\n",
- " \n",
- "# 关闭数据库连接\n",
- "db.close()"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 116,
- "metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "[('Mac', 'Mohan', 21, 'M', 2000.0)]\n"
- ]
- }
- ],
- "source": [
- "import pymssql\n",
- " \n",
- "# 打开数据库连接\n",
- "db = pymssql.connect(\n",
- " host='localhost',\n",
- " user='test',\n",
- " password='test',\n",
- " database='testdb'\n",
- ")\n",
- "\n",
- "# 使用 cursor() 方法创建一个游标对象 cursor\n",
- "cursor = db.cursor()\n",
- " \n",
- "# SQL 更新语句\n",
- "sql = \"UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'\" % ('M')\n",
- "try:\n",
- " # 执行SQL语句\n",
- " cursor.execute(sql)\n",
- " # 提交到数据库执行\n",
- " db.commit()\n",
- " cursor.execute(\"SELECT * FROM EMPLOYEE\")\n",
- " print(cursor.fetchall())\n",
- "except:\n",
- " # 发生错误时回滚\n",
- " db.rollback()\n",
- " \n",
- "# 关闭数据库连接\n",
- "db.close()"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 117,
- "metadata": {},
- "outputs": [],
- "source": [
- "import pymssql\n",
- " \n",
- "# 打开数据库连接\n",
- "db = pymssql.connect(\n",
- " host='localhost',\n",
- " user='test',\n",
- " password='test',\n",
- " database='testdb'\n",
- ")\n",
- "\n",
- "# 使用 cursor() 方法创建一个游标对象 cursor\n",
- "cursor = db.cursor()\n",
- " \n",
- "# SQL 删除语句\n",
- "sql = \"DELETE FROM EMPLOYEE WHERE AGE > '%d'\" % (20)\n",
- "try:\n",
- " # 执行SQL语句\n",
- " cursor.execute(sql)\n",
- " # 提交修改\n",
- " db.commit()\n",
- " print(cursor.fetchall())\n",
- "except:\n",
- " # 发生错误时回滚\n",
- " db.rollback()\n",
- " \n",
- "# 关闭数据库连接\n",
- "db.close()"
- ]
- }
- ],
- "metadata": {
- "kernelspec": {
- "display_name": "Python 3",
- "language": "python",
- "name": "python3"
- }
- },
- "nbformat": 4,
- "nbformat_minor": 2
- }
Add Comment
Please, Sign In to add comment