Python sql query to list

Correct way to fill a list from a SQL query using pyodbc

Is this the correct way to get a list from a SQL query in Python 2.7? Using a loop just seems somehow spurious. Is there a neater better way?

import numpy as np import pyodbc as SQL from datetime import datetime con = SQL.connect('Driver=;Server=MyServer; Database=MyDB; UID=MyUser; PWD=MyPassword') cursor = con.cursor() #Function to convert the unicode dates returned by SQL Server into Python datetime objects ConvertToDate = lambda s:datetime.strptime(s,"%Y-%m-%d") #Parameters Code = 'GBPZAR' date_query = ''' SELECT DISTINCT TradeDate FROM MTM WHERE Code = ? and TradeDate > '2009-04-08' ORDER BY TradeDate ''' #Get a list of dates from SQL cursor.execute(date_query, [Code]) rows = cursor.fetchall() Dates = [None]*len(rows) #Initialize array r = 0 for row in rows: Dates[r] = ConvertToDate(row[0]) r += 1 

Edit: What about when I want to put a query into a structured array? At the moment I do something like this:

#Initialize the structured array AllData = np.zeros(num_rows, dtype=[('TradeDate', datetime), ('Expiry', datetime), ('Moneyness', float), ('Volatility', float)]) #Iterate through the record set using the cursor and populate the structure array r = 0 for row in cursor.execute(single_date_and_expiry_query, [TradeDate, Code, Expiry]): AllData[r] = (ConvertToDate(row[0]), ConvertToDate(row[1])) + row[2:] #Convert th0e date columns and concatenate the numeric columns r += 1 

Источник

Convert A SQL Query Result To A List In Python | MySQL

When dealing with database queries we use the for-loop to access each record and deal with it one at a time. In some cases it’s better to deal with the database results in a variable so we do not need to query again. The Python List object allows us to store a SQL query result easily as it allows for duplicates and permits changes too.

This post is a follow-up to the “Write a List to CSV file” article. In this case we are querying the database and loading the results into some Python objects like a List, Tuple and Dictionary.

Note: We’re going to skip the virtual environment.. (MySQL Ex. Here) ..but it is recommended to use a virtual environment for all your projects.

Python Environment Connect to MySQL

You should be familiar with creating and connecting to a MySQL database. Here is some quick sample code to install in a new Python environment.

1. activate the environment

.\myenv\Scripts\activate (windows) source /myenv/bin/activate (linux)

2. install the mysql driver

pip install mysql-connector-python

Use the following example to connect to a localhost MySQL database.

import mysql.connector conn = mysql.connector.connect( host="localhost", database="somedbname", user="myuser", password="goodpasswd" )

What is a Python List []

The List is similar to an Array in other languages and it is indexed starting 0. The List can contain strings, numbers and objects too, here are some simple string examples.

string = [] ## New empty List string1 = ['a', 'b', 'c', 'd', 'e', 'f', 'g'] string2 = ['Brazil', 'Panama', 'Canada', 'India', 'Decentraland'] number = [2,3.6,7,-23,99] ## can contain decimal and negative numbers together = string1[0:2] + string2[2:5] + number[2:4] ## concatenate lists print(string1[3]) ## get just 1 value print(string2[4]) print(string1[2:5]) ## Range: get values from index 2 -> 5 print(number[1:5]) print(together) Values from all 3 in 1 list string1[:] = [] ## clear the list

Append to List []

newlist = ['Houston'] newlist.append('Decentraland') ## append just 1 string newlist.append(string2[2]) ## append the 3rd value from another list newlist.append(number) ## append the entire number list print(newlist)

MySQL Table and Query

For this sample I created a table called “myusers” which includes the users first name, last name and the city they work in. In this example we are using a cursor and then querying this table for the (db tales com) first record and then all of them. Keep in mind that Python is returning a Tuple () for one record but then a List with many Tuples that contains our data, we are also querying 3 fields from the database.

cursor = conn.cursor() cursor.execute("SELECT fname, lname, city FROM myusers") onerecord = cursor.fetchone() results = cursor.fetchall() print(onerecord) ## returns as a Tuple print(results) ## returns as a List of Tuples

Lets say we are only interested in a List of the Last Names and we want it to be stored in a single List. We can create an empty List and iterate through the Tuple () that is returned by the cursor.

cursor = conn.cursor() cursor.execute("SELECT lname FROM myusers") lastnames = [] for row in cursor: for field in row: lastnames.append(field) print(lastnames)

Keep in mind that we are only SELECT’ing one field in the Query. If there is more than 1 field, such as the First and Last Names, you will end up with one List containing all of the fields.

MySQL to Python List [] & Dictionary <>

Another option to consider is using the ‘Dictionary’ cursor setting to return a Dictionary <> with the keys and then we end up with just the values from the query. Then we can iterate through them using the field values from our query.

cursor = conn.cursor(dictionary=True) ## available in v2 cursor.execute("SELECT fname, lname, city FROM myusers") for row in cursor: print(row['fname'] + ' ' + row['lname'] + ' from ' + row['city'])

Lastly, the cursor can return the records as key’d Dictionary’s all encompassed within one List. Each record is it’s own Dictionary with the key named after the database field name.

cursor = conn.cursor(dictionary=True) cursor.execute("SELECT fname, lname, city FROM myusers") print(cursor.fetchall())

Источник

Convert SQL Result to List in Python (Example)

Hi! This tutorial will show you how to turn SQL results into a list in the Python programming language.

First, though, here is an overview:

Import sqlite3 Module

In this tutorial, we will demonstrate how to use the sqlite3 Python module to connect to a SQLite database, which is a lightweight disk-based database that does not require being hosted on a separate remote server.

Please note that this tutorial will not walk you through the steps of creating a database in SQLite; it will rather only demonstrate the steps for fetching the data from the database, and transforming that data into a Python list. It is also assumed that you already have some knowledge of SQL and how to query a relational database.

The sqlite3 module is a built-in module in Python. Therefore, to import the module, run the line of code below in your preferred Python IDE as follows:

Connect to Database

The first step is to connect to an existing SQLite database. To do so, you can run your code like this:

conn = sqlite3.connect("demo.db")

Where “demo.db” is the name of your SQLite database. Once connected, you can then proceed to the next step.

Create a Cursor Object

The cursor object is what will enable us to execute SQL queries.

Execute a Statement

Having created the cursor object, we will now execute a statement that will enable us to select all the columns in the table inside our database:

cur.execute("SELECT * FROM my_table")

It is assumed that you already have a table inside your database, from which you can either select individual columns by naming them separated by commas or parsing all columns by the asterisk * just as demonstrated above.

Fetch all Rows as List of Tuples

The next step is to fetch all the rows as a list of tuples via fetchall():

Источник

Formatting SQL rows to a list in python

I would like to edit these lists to exclude the first two words and replace the «\r\n» characters with commas. I’ve tried this to get rid of the ‘FreqBand,Frequency’:

for row in result: row_to_list = list(row) i = 0 for each in row_to_list: row_to_list[i].replace('FreqBand', '') i += 1 print(row_to_list) 

but the output of this seems to get rid of half the first list and doesn’t edit any of the others. Any help on this would be appreciated.

Which database you’re querying against? It’s funny because execute should not return any value. result = [list(row) for row in cursor] should work though

Why is your query returning the entire row as a single string, instead of separate elements for each column?

@Barmar The query is «select cast(MeasurementFile as varchar(max)) from Measurements M», so it’s returning one column that is a single string

That just raises the question of why you’re storing this as a single string in the database, rather than splitting it into separate columns.

2 Answers 2

You need to assign the result of replace() back to the list element. And use range() to get a sequence of numbers instead of incrementing i yourself.

for row in result: row_to_list = list(row) for i in range(len(row_to_list)): row_to_list[i] = row_to_list[i].replace('FreqBand,Frequency,', '').replace('\r\n', ',') print(row_to_list) 

First of all, the list below has a size of 1 rather than 8-10 that I assumed when I first saw the question. So initially, please check if that is something you are aware of.

['FreqBand,Frequency,0, 5, 10\r\n1,0.006,16.56,25.15,30.96\r\n'] 

In this way, when you iterate over this list with for each in row_to_list: , all you will get is the string that has no difference from the string you would get with row_to_list[0] .

Secondly, you might want to double check what you are trying to accomplish with the counter i. In the case of manipulating first elements of each list you name as row_to_list , all you need to do is to access by index then reassign the variable.

for row in result: row_to_list = list(row) row_to_list[0] = row_to_list[0].replace('FreqBand,Frequency,', '') row_to_list[-1] = row_to_list[-1].replace('\r\n', ',') 

Источник