python 怎么表示sqlserver null_python操作sqlserver

首先安装pymssql

pip install pymssql

下面开始操作sqlserver吧

1.创建配置文件conn.conf

[test_db_conn]

server = 192.168.152.138

user = sa

db_name = TestDB

password = sa

2.创建testconfig.py读取配置

from ConfigParser import ConfigParser

class TestConfig:

def __init__(self):

config = ConfigParser()

config.read('conn.conf')

self.server = config.get('test_db_conn', 'server')

self.user = config.get('test_db_conn', 'user')

self.password = config.get('test_db_conn', 'password')

self.db_name = config.get('test_db_conn', 'db_name')

3.创建表

CREATE TABLE users

(

id int identity(1,1) primary key,

name nvarchar(50) not null,

age int not null,

sex bit not null,

create_time datetime

)

4.添加数据

# -*- encoding:utf-8 -*-

from pymssql import connect

from datetime import datetime

from tzlocal import get_localzone

from testconfig import TestConfig

tz = get_localzone()

cfg = TestConfig()

with connect(cfg.server, cfg.user, cfg.password, cfg.db_name) as conn:

cursor = conn.cursor()

cursor.executemany('insert into users(name,age,sex,create_time) values (%s,%s,%s,%s)',

[

(u'张三', 18, True, tz.localize(datetime.now())),

(u'李四', 20, False, tz.localize(datetime.now())),

(u'王五', 21, True, tz.localize(datetime.now())),

(u'赵六', 16, False, tz.localize(datetime.now())),

(u'孙七', 13, True, tz.localize(datetime.now())),

(u'麻子八', 8, False, tz.localize(datetime.now()))

])

conn.commit()

数据如下所示

5.查询数据

with connect(cfg.server, cfg.user, cfg.password, cfg.db_name) as conn:

cursor = conn.cursor()

print 'query one data'

cursor.execute('select id,name,age,sex,create_time from users where name = %s', u'张三')

row = cursor.fetchone()

if row:

print u'id:{0:d},name:{1:s},age:{2:d},sex:{3:s},create_time:{4:s}' \

.format(row[0], row[1], row[2], (u'女', u'男')[row[3]], row[4].isoformat())

print '(fetchone)query all data'

cursor.execute('select id,name,age,sex,create_time from users')

row = cursor.fetchone()

while row:

print u'id:{0:d},name:{1:s},age:{2:d},sex:{3:s},create_time:{4:s}' \

.format(row[0], row[1], row[2], (u'女', u'男')[row[3]], row[4].isoformat())

row = cursor.fetchone()

print '(fetchall)query all data'

cursor.execute('select id,name,age,sex,create_time from users')

rows = cursor.fetchall()

for row in rows:

print u'id:{0:d},name:{1:s},age:{2:d},sex:{3:s},create_time:{4:s}' \

.format(row[0], row[1], row[2], (u'女', u'男')[row[3]], row[4].isoformat())

输出如下

query one data

id:1,name:张三,age:18,sex:男,create_time:2016-12-14T19:14:25.417000

(fetchone)query all data

id:1,name:张三,age:18,sex:男,create_time:2016-12-14T19:14:25.417000

id:2,name:李四,age:20,sex:女,create_time:2016-12-14T18:19:24.773000

id:3,name:王五,age:21,sex:男,create_time:2016-12-14T18:19:24.773000

id:4,name:赵六,age:16,sex:女,create_time:2016-12-14T18:19:24.773000

id:5,name:孙七,age:13,sex:男,create_time:2016-12-14T18:19:24.773000

id:6,name:麻子八,age:8,sex:女,create_time:2016-12-14T18:19:24.773000

(fetchall)query all data

id:1,name:张三,age:18,sex:男,create_time:2016-12-14T19:14:25.417000

id:2,name:李四,age:20,sex:女,create_time:2016-12-14T18:19:24.773000

id:3,name:王五,age:21,sex:男,create_time:2016-12-14T18:19:24.773000

id:4,name:赵六,age:16,sex:女,create_time:2016-12-14T18:19:24.773000

id:5,name:孙七,age:13,sex:男,create_time:2016-12-14T18:19:24.773000

id:6,name:麻子八,age:8,sex:女,create_time:2016-12-14T18:19:24.773000

6.更新数据

with connect(cfg.server, cfg.user, cfg.password, cfg.db_name) as conn:

cursor = conn.cursor()

cursor.execute('update users set create_time = %s where name = %s', (tz.localize(datetime.now()), u'张三'))

conn.commit()

cursor.execute('select id,name,age,sex,create_time from users where name = %s', u'张三')

row = cursor.fetchone()

if row:

print u'id:{0:d},name:{1:s},age:{2:d},sex:{3:s},create_time:{4:s}' \

.format(row[0], row[1], row[2], (u'女', u'男')[row[3]], row[4].isoformat())

输出如下

id:1,name:张三,age:18,sex:男,create_time:2016-12-14T19:25:58.627000

7.删除数据

with connect(cfg.server, cfg.user, cfg.password, cfg.db_name) as conn:

cursor = conn.cursor()

cursor.execute('delete from users where name = %s', u'张三')

conn.commit()

cursor.execute('select id,name,age,sex,create_time from users where name = %s', u'张三')

row = cursor.fetchone()

if row:

print u'id:{0:d},name:{1:s},age:{2:d},sex:{3:s},create_time:{4:s}' \

.format(row[0], row[1], row[2], (u'女', u'男')[row[3]], row[4].isoformat())

else:

print u'数据已经被删除'

输出如下

数据已经被删除