Skip to main content
24 votes
Accepted

Encoding issue with SQL Server VARCHAR column retrieved in Python

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, ...
Solomon Rutzky's user avatar
17 votes
Accepted

PostgreSQL/Psycopg2 upsert syntax to update columns

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'),...
ypercubeᵀᴹ's user avatar
14 votes

How to uninstall pgAdmin4 on Ubuntu 16.04?

sudo apt autoremove pgadmin4 works for ubuntu version 18.04
Sarath Rajan's user avatar
10 votes
Accepted

AttributeError: 'NoneType' object has no attribute 'value' - pgAdmin4

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....
Dowwie's user avatar
  • 216
7 votes

MySQL database copy

Yes, what you are doing is a fine solution for a smaller database. If your databases get larger (say, 1GB or more) you may find that using mysqldump is too slow. For larger databases, use Percona ...
Bill Karwin's user avatar
  • 15.1k
6 votes
Accepted

When I run mssql-cli, I get "No module named mssqlcli"

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 ( ...
Evan Carroll's user avatar
  • 63.6k
6 votes
Accepted

SQL Server hashbytes seed

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-...
Joe Obbish's user avatar
  • 32.5k
5 votes
Accepted

Does SSMS v17 allow user input in Python scripts?

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. ...
Solomon Rutzky's user avatar
5 votes

Python script leaves the database in restoring mode

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 ...
David Browne - Microsoft's user avatar
5 votes

SQL Server hashbytes seed

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 ...
Josh Darnell's user avatar
  • 29.4k
5 votes

Is using CLR for regular expressions safer than using external scripts?

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 ...
Solomon Rutzky's user avatar
4 votes
Accepted

Connection pooling with python mysql-connector

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 ...
Nerivon's user avatar
  • 156
4 votes

How to work with multiple Postgres databases in psycopg2

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 ...
Evan Carroll's user avatar
  • 63.6k
4 votes

psycopg2 connection already closed

The most likely reason is you have a firewall or gateway which is not very patient and is dropping the connection after 5 minutes. Usually when this happens, it makes it look to the client like the ...
jjanes's user avatar
  • 40.1k
4 votes

SQL Server 2017 & External Python Modules

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 ...
Dan Gray's user avatar
4 votes
Accepted

Why are my queries taking so long?

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 ...
ypercubeᵀᴹ's user avatar
4 votes

Set or limit number of connections a single pymongo client will open

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.
dilsingi's user avatar
  • 196
4 votes
Accepted

SQL optional where clauses without exponentially large number of only slightly different queries

Given that the phrase column IN () is not valid SQL syntax, I do not believe there is a way to do this with SQL. If there is any SQL syntax to do this, it is going to be exceptionally convoluted, ...
hrunting's user avatar
  • 156
4 votes

Azure DB - Converting Base64 Encoded into Binary Found in Databricks Python

As far as I can see they are the exact same binary value (DB Fiddle). Just Python displays binary data in some weird and wonderful way with a mix of ASCII characters, escape codes, and hex codes ...
Martin Smith's user avatar
  • 84.9k
3 votes

Are lots of columns or joins better in Django

I'm not sure how to phrase this, but this type of application isn't what Django is made for. Django is made for simple CRUD based websites, with an admin interface that comes for free, but I don't ...
Tom V's user avatar
  • 15.7k
3 votes
Accepted

Should I do the maths within PostgreSQL for ACIDity?

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, ...
Troy Witthoeft's user avatar
3 votes

SQL Server backup. PowerShell vs Python

I am not familiar with Python, but for your current requirement (i.e. backup). I'd strongly recommend you to use PowerShell for two reasons: MS in its SQLPS module (since SQL Server 2012), has a ...
jyao's user avatar
  • 3,063
3 votes
Accepted

How to filter splitting a column value?

You can use string functions like INSTR(), LEFT(), RIGHT(), LENGTH() for this purpose. create table market(id int, col varchar(100), exchange varchar(100)); insert into market values (1, 'EOS/ETH', ...
McNets's user avatar
  • 23.8k
3 votes

Reading from Postgres SQL in a Jupyter Notebook

You didn't specify important keyword "FROM": df = pd.read_sql('SELECT * "xyz"', conn) so the right one is: df = pd.read_sql('SELECT * FROM "xyz"', conn)
Alexandr Volok's user avatar
3 votes

How to set a auto-increment value in SQLAlchemy?

Starting from version 1.4, SQLAlechemy has Identity column. Example copied from documentation: from sqlalchemy import Table, Column, MetaData, Integer, Identity metadata = MetaData() data = Table( ...
Karol Zlot's user avatar
3 votes
Accepted

Allow only one reader to a table

Edited: look at the last example is a better way Yes, but you must send the two sentences within a transaction and add the "for update" clause in the select sentence. Python If you send then ...
Emilio Platzer's user avatar
3 votes

how to get all the table existing in a database

There is no information_schema in SQLite as pointed out by @mustaccio. However, you can get the information you require by using this SQL: SELECT name FROM sqlite_master WHERE type ...
Vérace's user avatar
  • 30.2k
3 votes

inserting multiple values into SQL with %

The % operator takes a string on its left side, and a list of values on its right side: execute("SELECT ... WHERE B_MONTH = '%s' AND B_DAY = '%s'" % (currentMonth, currentDay)) Please note that ...
CL.'s user avatar
  • 5,243
3 votes
Accepted

how do I prevent my postgres connection from timing out? (python, psycopg2)

PostgreSQL doesn't drop idle connections. It must be something else, probably a misconfigured firewall. To defend against that, set the parameter tcp_keepalives_idle on the server to something less ...
Laurenz Albe's user avatar
  • 53.4k
3 votes
Accepted

How to add a new row to a sql server table in the first position (from python)

A table isn't ordered. There is no such thing as "first row", "next row" etc in a table. You can have ORDER BY in a SELECT statement which defines the order of the result. Without ...
Tibor Karaszi's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible