21
SQL Server does not store UTF-8 under any circumstances. You get either UTF-16 Little Endian (LE) via NVARCHAR (including NCHAR and NTEXT, but don't ever use NTEXT) and XML, or some 8-bit encoding, based on a Code Page, via VARCHAR (including CHAR and TEXT, but don't ever use TEXT).
The problem here is that your code is mistranslating that 0x82 character, ...
20
The "Cannot assign requested address" part in the error message comes from the kernel TCP stack. When encountered intermittently, this typically means that the space of available sockets is exhausted because of too much sockets in wait state (TIME_WAIT, or less probably FIN_WAIT_1 or FIN_WAIT_2)
The range of socket ports can be output by cat /proc/sys/net/...
15
You can use the EXCLUDED keyword to access the values passed to INSERT. No need to pass them twice:
insert_sql = '''
INSERT INTO {t} (id,col1, col2, col3)
VALUES (%s, %s, NULLIF(%s, 'nan'), NULLIF(%s, 'nan'))
ON CONFLICT (id)
DO UPDATE SET
(col1, col2, col3)
= (EXCLUDED.col1, EXCLUDED.col2, EXCLUDED.col3) ;
...
14
There are two typical ways to express this.
With LIKE infix search:
SELECT title FROM snippets WHERE description LIKE '%evil%';
or with position:
SELECT title FROM snippets WHERE position('evil' in description) > 0;
Note that neither are indexable by default. Search for "infix search index" for more info on that, and look into pg_trgm if you need it.
14
It's to do with Python's object model - there's always a way to get a reference to objects that could be unsafe. See the rexec module documentation and the restricted execution chapter of the docs for some info on the problems, as well as:
https://www.researchgate.net/publication/228612669_Controlling_access_to_resources_within_the_python_interpreter
http://...
12
Don't make your user transaction wait for the (hopefully!) successful completion of the Python script. Your entire transaction sits there and waits for this external process to run, try to send mail, etc. I doubt the e-mail really has to go out that instant - especially given you can't control any delays it has as it gets routed to the recipient's inbox ...
10
psycopg2 is a wrapper around libpq, written in C, to expose a Python DB-API compatible API to Python programs. It implements Python objects in C that call libpq functions. It has a thin Python module wrapper around it to load it and provide some of the interface functionality that's easier to write in pure Python.
Anything that implements the DB-API to ...
10
You can reverse the sort direction to get the minimum instead of the maximum value:
# Sort by myfield (ascending value) and return first document
collection.find_one(sort=[("myfield", 1)])["myfield"]
This example assumes that:
myfield is a numeric value (so the sort order makes sense to determine a minimum or maximum)
myfield exists in the matching ...
10
Yes, it is possible to work with multiple databases at the same time but you're looking in the wrong place. psycopg2 is just a library that simplifies accessing and manipulating data coming out of PostgreSQL but it doesn't go far beyond what you can do with psql. What you're looking to do you can solve on the database level by using Foreign Data Wrappers.
...
10
I find these multiple closings with age=0 worrying
No, this whole log looks fine and tends to show that pooling is working.
These entries with age=0 being tagged as LOG C, they concern communication between a client and pgBouncer. age=0 just means that the client used the connection for less than 1 second, which is consistent with the dates with ...
10
I performed the same steps and ran into the same problem. I found that the problem was resolved by removing the ~/.pgadmin directory (created during installation) and then re-running "python pgAdmin4.py"
10
sudo apt autoremove pgadmin4
works for ubuntu version 18.04
6
You might be able to use the service broker, though that is probably overkill, as you state.
Alternately, if you don't want to install/manage an extra service for this one need, you could use xp_cmdshell or a CLR-based trigger to make an external call when needed, to a program that starts the desired process asynchronously (so, outside the transaction that ...
5
The "geospatial feature" of dynamodb isn't really anything else then calculating a geohash from the coordinates, storing the hash in the same row, create an index on the hash, and when querying based on a location, calculate the hash of that location and compare to the indexed hashes in the table. The algorithm of geohash is really simple and with a ...
5
I cannot see any possible way for this to work given that the script is being submitted through a back-end service where there is no user-context (i.e. display, etc). SSMS is not executing anything. It is merely submitting those statements to SQL Server which handles the execution (even if SQL Server then passes it off to something else).
The only potential ...
5
I tried to fix this by submitting a patch #234 (approved but not yet committed). You'll have to edit the file ~/.local/bin/mssql-cli and replace
python -m mssqlcli.main "$@"
With this line
( command -v python3 && python3 -m mssqlcli.main "$@" ) || python -m mssqlcli.main "$@"
answered Jan 28 '19 at 17:35
Evan Carroll - Use SO instead
54.5k34 gold badges199 silver badges406 bronze badges
5
Pyodbc won't progress past info messages automatically, and RESTORE generates a lot of them. So you must process them with cursor.nextset() or else you're actually aborting the RESTORE by running cursor.close() before it actually completes.
Eg
cursor.execute(sql)
while cursor.nextset():
pass
cursor.close()
answered Aug 21 '20 at 19:26
David Browne - Microsoft
38.1k3 gold badges40 silver badges85 bronze badges
5
I am able to get identical results between python and T-SQL code with the MD5 algorithm. For example, the NO COLLUSION string hashes to 0x5CA1A58C070F24EF1D4D2900E5727F37 on both platforms.
Example T-SQL code:
SELECT HASHBYTES('MD5', 'NO COLLUSION');
Example Python code:
import hashlib
result = hashlib.md5(b'NO COLLUSION')
print(result.hexdigest())
I'm not ...
5
Joe has correctly pointed out that Python's hashlib.md5 and SQL Server's HASHBYTES('MD5', ...) functions have the same output.
As an additional clarification, the built-in hash() function in Python is not intended to be used in the same way. It's implementation is platform specific, varies depending on the type of object being used, and is (as you mentioned)...
5
my other colleague categorically stated that using CLR here would be a huge risk to security and stability
Well, your colleague is categorically wrong (unless they can offer up actual proof of such claims).
Security
Ever since SQLCLR was introduced in SQL Server 2005 people have been saying that it is "unsafe". However, I have yet to see anyone ...
4
PostgreSQL calls "schemas" what MySQL calls databases. Do not create multiple databases with the intention of them working together. That said, if you need to access an external database, the PostgreSQL FDW will help you out
PostgreSQL FDW
answered Jan 12 '17 at 5:37
Evan Carroll - Use SO instead
54.5k34 gold badges199 silver badges406 bronze badges
4
Set the Application Name
If you expect to be running many processes you need to know where they are
connecting from. PGBouncer will make this invisible to pg_stat_activity. Solve
this by carefully setting the application_name with the information you'll
need:
# Sets the application name for this connection in the form of
# application-name:user@host
prog ...
4
I did some experimentation in console trying to figure out why this was happening for me as well. In the end, I searched right into the file folder for the package looking to ensure that the pooling file was present in the mysql.connector package version I had.
Sure enough there it was.
In the end, I simply changed my import statement. I had been using
...
4
Just in case someone finds this question when searching the net
I logged an advisory case with MS as I needed an answer on this. They have come back to me with the following method that works:
Open a command prompt (as administrator) and navigate to the python directory (and inside the scripts directory)
cd c:\Program Files\Microsoft SQL Server\MSSQL14....
4
I have some theories as to why the performance is so bad.
Lets see.
The first is that I simply have too many rows.
Probably not. There is no limit on the number of rows in a (SQL) DBMS. 80 million rows is not that much these days.
The second is that a database is not suited for this task.
Unlikely. We don't know which DBMS you use (Postgres? ...
4
Use the following parameter to set the number of maximum connections from your pymongo app.
client = MongoClient(host, port, maxPoolSize=10)
Here is documentation with more details.
3
A few patterns are evident in your data:
The primary activity is updates.
Updates come for several seconds after query/inserts.
The criteria fields in finds is indexed.
Even so, the 'index miss' rate is very high.
Background flush is very low (as it should be with SSD) so you're not getting I/O contention.
Lock-average is extremely high.
Based on this, I'm ...
3
I addressed a similar issue in my blog Storing X.509 Digital Certificates (And Other Messy Things) and some earlier comments. (It's too long to cut & paste here.) Many of the points made here are much easier if you can create a user-defined function that extracts the fields you need to cache.
Addressing one other point above - it is possible to write a ...
3
create materialized view foo as
select distinct machine_code, date(datetime) from thing;
create unique index on foo(machine_code,date);
Beware that the conversion of datetime to date will occur in the timezone of whoever last created or refreshed the MV. You might want to use this instead:
create materialized view foo as
select distinct ...
3
Welcome to DBA Stack Exchange!
My problem is that I don't know the 'speed' of SQL when it deals with
stats and maths, compared to other language. I know that basic
functions (correlation, R^2, ...) are already implemented in SQL, but
I am using far more 'advanced' (I mean 'complex' here..) functions
(even if I have not represented it here).
As a ...
Only top voted, non community-wiki answers of a minimum length are eligible