An illiterate Python entry diary: the next day, connect to mssql and python code writing experience

In actual work, whether it is an app, web, desktop program, or any other type of application, without database support, it is difficult to expand the application, increase wider and deeper digging, so let’s also first No matter what other tutorials say, it is serious to connect to the database first. After all, we are not a pure newcomer, just don’t understand Python.

It's Baidu, um, it's very simple, https://www.cnblogs.com/baiyangcao/p/pymssql_basic.html all kinds of articles are very detailed. Actually test it

import ioimport syssys.stdout = io.TextIOWrapper(sys.stdout.buffer,encoding='utf-8')print("Content-Type: text/html\n")print('<meta charset="UTF-8" />') import pymssqlconn = pymssql.connect('我是服务器君','我就是账号君','我就是密码君','我是数据库君')cursor = conn.cursor();dt = cursor.execute('select * from 我是用户表君').fetchall();print(dt);  Traceback (most recent call last): File "D:\****\site\py\2.py", line 7, in import pymssql ModuleNotFoundError: No module named 'pymssql'

Ok. . . . It actually prompts that there is no pymssql package. Under Baidu, it's just pip install pymssql. It's very simple. As a result, I entered the python session window and hit this command. . . Report an error?

Oh, pip is an executable file in scripts in the python folder. . . That's okay

Traceback (most recent call last): File "D:\****\site\py\2.py", line 10, in dt = cursor.execute('select * from 我是用户表君').fetchall(); AttributeError: 'NoneType' object has no attribute 'fetchall'

Report an error again? ? ? Thank you Du Niang for accompanying me. Look carefully at other people's code. . .

Fetchall is the method of cursor. . . You cannot directly follow fetchall after execute. . . Ok. . .

cursor = conn.cursor();cursor.execute('select * from 我是用户表君')dt = cursor.fetchall()print(dt)

Oh, a lot of things are printed out, problems. . . Why are there garbled characters? Check it on the Internet. . . . All kinds of encoding and decoding of individual data. . . . amount. . . . Leave them alone and see what your database is like

SELECT COLLATIONPROPERTY('chinese_prc_ci_as','codepage')  ------------------------------------------936 (1 行受影响)

I just noticed that my database turned out to be 936, which is gbk encoding? ? Okay, regardless of the garbled problem, what about my field name?

cursor = conn.cursor();cursor.execute('select * from 我是用户表君')dt = cursor.fetchall()print('dt is list:',isinstance(dt,list))for row in dt:	print('row is tuple:',isinstance(row,tuple))	for val in row:			if isinstance(val,str):				print(val.encode('latin-1').decode('gbk'))			else:				print(val)

Hmm, using type(dt), what is printed out is an empty string, nothing is displayed. I don't know why for the time being. First use isinstance to overdo it. After all, I just started learning. It is normal to have defects.

In the process of writing the above code, there are several very novel and unaccustomed experiences

1. Code indentation problem. This problem is okay. I have developed the habit of indentation. This requirement is a big problem for those who write code freely before. After all, looking at their code before, it will kill you. Obsessive-compulsive like me

2. The flow control statement, the way of canceling the braces in python, is expressed as the same code segment with indentation, the problem comes, the flow control statement must be ended with a colon, hmmmmm, not very used

3. For the time being, I don’t know if there is any code segment name definition and goto, write it down and verify later

4. The semicolon at the end of the statement is optional and does not affect the execution of the code. This is the same as js, unlike c#, which makes it difficult to compile without a semicolon.

5. Python can continue to use the. Command to operate, but I don’t know what scopes are available, such as custom types and methods, which will be verified later

6. At least some commands are not supported during execution. Command stacking, for example, cursor.execute and cursor.fetchall are of the same level. This is not very friendly to old customers. I don’t understand why it is designed like this. Shouldn’t it return after execution? Is it a list? Even if the list is not returned, the cursor should not return to the cursor itself to support the command overlay after executing the command?

7. I can't find the field name due to different database reading methods? dt is list, row is tuple, and c#, this type is List<Tuple>, what about the field name? Actually need to use cursor.description to get it again?

Think about it first, and output the results according to your own habits

import ioimport syssys.stdout = io.TextIOWrapper(sys.stdout.buffer,encoding='utf-8')print("Content-Type: text/html\n")print('<meta charset="UTF-8" />')print('<pre>')import pymssqlconn = pymssql.connect('我是服务器君','我就是账号君','我就是密码君','我是数据库君')cursor = conn.cursor();dt = cursor.execute('select * from 我是用户表君').fetchall();col = cursor.description#print(col)dt = cursor.fetchall()datatable=[]for row in dt:	idx=0;	datatable.append({})	for val in row:			d_val = val;			if isinstance(val,str):				d_val=val.encode('latin-1').decode('gbk')			datatable[-1][col[idx][0]]=d_val			idx+=1conn.close()print(datatable)

Well, this looks much more comfortable, it's still a standard json format, but the ++ operation is actually cancelled, and only += can be used.