1

I would like to collect all entries in a database and write them into a .csv-file.

To do so, i tried to come up with a while-loop like this:


def create_csv():
     query = QSqlQuery(db=db)
     query.prepare("SELECT * FROM database_name")
     query.exec()
     rec = query.record()
            
     print(query.isSelect())
     nameCol = rec.indexOf("name")

          while query.next():
          print(query.value(nameCol))

This gives me all entries in the column "name" as expected. But since the database has got a lot more columns, i was wondering if there is an easier way to get all the columns (and the headers) at once?

All at once would make the creation of the csv a lot easier.

Nullschall
  • 11
  • 6

2 Answers2

0

You can do this with a simple SQL command:

select * from passtable4 LOCAL INTO OUTFILE 'C:\test.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';

You might run into this error:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Solutions are provided here and here

If your database has different types of datatypes(like date, BLOB etc), you can find a much more comprehensive guide here

Theorist
  • 159
  • 1
  • 8
  • Thank you for your reply @Mayank. I´m pretty sure this would work with MySql. But since I´m using a MS Acces Database with the QODBC Driver i would need a little different syntax. I had this problem already with filtering the db. [see here](https://stackoverflow.com/questions/67695981/how-can-i-filter-an-ms-access-databse-using-qsqltablemodel-and-qlineedit) – Nullschall Jun 10 '21 at 12:40
0

It took a while but I think I found something that worked for me:

  • I started with building the name for the file and generated 2 Lists
  • executed the query that gives me all values of the table
  • started a for-loop to get the Headers.
  • started a while loop that generates me the temporary list-element "row"
  • added the temporary list-elemt to the Value-List so it becomes a List of Lists
  • builded a Pandas-dataframe and used the to_csv as desired.

I´m sure there are "smarter" or faster ways to get this done. But at least it works for me.

 def create_csv(self):
    
    today = datetime.today()
    now = today.strftime("%y%m%d_%H%M_%S_")
    
    filename = f"{jetzt}COS.csv"
    
    

    Column_Header=[]
    Article_Value=[]
    
    query = QSqlQuery(db=db)
    query.prepare(r"SELECT * FROM database_name")
    query.exec()
    
    rec = query.record()
    
    for id in range(rec.count()):
        
        Column_Header.append(rec.fieldName(id))
        
    indexes = range(rec.count())
        
    while query.next():
        
        row = [query.value(index) for index in indexes]
        
        
        Article_Value.append(row)
        
    df = pd.DataFrame(Article_Value, columns=Column_Header, index=None) 

    print(df)
    
    df.to_csv(filename,sep=';',index=False)
Nullschall
  • 11
  • 6